Extract to CSV File

Question

Extract to CSV File

wweraw25

SSC Eights!

Points: 953
More actions
October 17, 2017 at 6:04 pm

#402606

I have data something like below for one of the column.
ADDRESS
2248|("snake") ft NE, FTE 700
When I extract this record to csv using SSIS, My column is populating as below:
ADDRESS Some other column
2248|("snake") ft NE FTE 700
I've used something like this in my ole db source
Select '"'+[Address]+'"' from dbo.table

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

wweraw25 - Tuesday, October 17, 2017 6:04 PM
I have data something like below for one of the column.
ADDRESS
2248|("snake") ft NE, FTE 700
When I extract this record to csv using SSIS, My column is populating as below:
ADDRESS Some other column
2248|("snake") ft NE FTE 700
I've used something like this in my ole db source
Select '"'+[Address]+'"' from dbo.table

I'm not really grasping what the question is here.

Ah... now I see it. I had to be in the edit mode of this post in order to see that the "FTE 700" part of the address ended up in another column. I also see that this is for SSIS, which I know nothing about and so I can't help here but, hopefully, my post will also act as a clarification and a "bump" for those that do know about SSIS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

Are you using commas as your column delimiter?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

wweraw25 SSC Eights! Points: 953 More actions · Answer 3

Phil Parkin - Wednesday, October 18, 2017 4:12 AM
Are you using commas as your column delimiter?

Yes I'm.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

Are you viewing the extracted file in a text editor, or in Excel?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

wweraw25 SSC Eights! Points: 953 More actions · Answer 5

Phil Parkin - Wednesday, October 18, 2017 7:45 AM
Are you viewing the extracted file in a text editor, or in Excel?

in excel since it includes some amount fields and have to do some aggregations stuff.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 6

The simplest option if you can do it is to simply strip out the comma from that column in the select statement. Another option is to use a different delimiter you can try tab, excel will read that pretty easily, depending on who's consuming the report you can use something more extreme like an character you can't easily type(unfortunately excel only supports single character delimiters). You can also try using a text qualifier and see if that doesn't break your data.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 7

wweraw25 - Wednesday, October 18, 2017 8:02 AM
Phil Parkin - Wednesday, October 18, 2017 7:45 AM
Are you viewing the extracted file in a text editor, or in Excel?
in excel since it includes some amount fields and have to do some aggregations stuff.

So that explain why the comma has been interpreted as a column delimiter. If you viewed the data file in a text editor, the comma would be there.

ZZartin - Wednesday, October 18, 2017 8:25 AM
The simplest option if you can do it is to simply strip out the comma from that column in the select statement. Another option is to use a different delimiter you can try tab, excel will read that pretty easily, depending on who's consuming the report you can use something more extreme like an character you can't easily type(unfortunately excel only supports single character delimiters). You can also try using a text qualifier and see if that doesn't break your data.

Great answer.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.