June 23, 2011 at 1:18 pm
I have 2 choices for a source flat file...either a CSV or Text file (seperated by single space)
There are some fields that has a comma in it so the CSV format is causing an issue. However each field is enclosed in double quotes. Is it possible for SSIS to seperate each field base on the double quotes?
I then tried using the text file but there is no "space" as a delimiter in SSIS.
Please advise
June 23, 2011 at 2:08 pm
Like you said, there is no "space" option for text qualifier. One option you can try is, read the data of all the columns in one column. For that you can use "Ragged Right" from drop-down for "Format" option. In your data flow task, you need to add a derived column where you need to replace all the space with a proper delimiter, like "|", "_", etc.
Example: REPLACE([Column 0]," ","|")
June 23, 2011 at 2:12 pm
Ms.SSIS (6/23/2011)
Like you said, there is no "space" option for text qualifier. One option you can try is, read the data of all the columns in one column. For that you can use "Ragged Right" from drop-down for "Format" option. In your data flow task, you need to add a derived column where you need to replace all the space with a proper delimiter, like "|", "_", etc.Example: REPLACE([Column 0]," ","|")
Great idea but since some fields have a space in the data, that will not work.
June 23, 2011 at 3:13 pm
can you get the data with a delimiter that is not used in the data, like a tab delimited or pipe [|] or tilde [~] or something? for get the space delimited...you'd run into a lot of problems that way.
if not, i'd go with CSV delimited and a format file to describe the double quotes to get an import to work., or import wizard/SSIS which can also consume quote delimited files.
Lowell
June 23, 2011 at 3:22 pm
Lowell (6/23/2011)
can you get the data with a delimiter that is not used in the data, like a tab delimited or pipe [|] or tilde [~] or something? for get the space delimited...you'd run into a lot of problems that way.if not, i'd go with CSV delimited and a format file to describe the double quotes to get an import to work., or import wizard/SSIS which can also consume quote delimited files.
I have to use the vendor's reporting tool to extract the data and those are the 2 formats unfortunantly. Can you explain further on consuming the quotes because right now, its splitting up the fields with a comma in it. How do I preserve that as a single field?
June 24, 2011 at 2:53 am
Both formats will work.
1. Text with spaces: the space is not listed in the dropdown box as a delimiter, but you can type in that dropdown. Just type in a space. Although I think a space for a delimiter is a recipe for disaster.
2. CSV with commas and double quotes: select the " as text qualifier in the editor and it will work.
For future reference: try using a delimiter like &| and you will never ever have problems.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2011 at 8:07 am
Koen Verbeeck (6/24/2011)
Both formats will work.1. Text with spaces: the space is not listed in the dropdown box as a delimiter, but you can type in that dropdown. Just type in a space. Although I think a space for a delimiter is a recipe for disaster.
2. CSV with commas and double quotes: select the " as text qualifier in the editor and it will work.
For future reference: try using a delimiter like &| and you will never ever have problems.
Excellent! #2 works! Thanks!
btw, I always use pipe as a delimiter but since the vendor's tool is not capable of that I had no choice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply