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: 1004363 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: 246965 More actions · Answer 2

Are you using commas as your column delimiter?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246965 More actions · Answer 4

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 30920 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: 246965 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin