May 8, 2012 at 6:16 am
I am frequently given csv files in following format:
"Id", "Col1", "Col2", "Col3"
"1", "x1", "y1", "z1"
"2", "x2", "y2", "z2"
The column names are in the first row. Two questions:
- is there any way to automatically remove double quotes for the names of the columns? I can remove them manualy (in the Advanced module of the Flat File Source, but this is tedious and I have a lot of columns).
- how can I remove double quotes for data? I can set column delimiter to "{,}", but this leaves first and last double qoutes in my data, like this:
"1, x1, y1, z1"
"2, x2, y2, z2"
Edit - what is the best way to somehow save the settings of the current import so that I can use it in the future also?
May 8, 2012 at 6:21 am
use the text qualifier on the import screen and set it to " this should then remove the " when you look at the csv in the preview
May 8, 2012 at 6:26 am
Wow, how could I miss this one 🙂 Thanks, that's exactly what I needed.
One question remains - what is the best way to somehow save the settings of the current import so that I can use it in the future also?
May 8, 2012 at 6:29 am
will the filename be exactly the same and in the same directory every time the import needs to be run? if so one of the last screens is run immediatly and save package, just select save package and then it creates the SSIS dtsx package which you can save in SQL and just run it via SSIS in SSMS or create a job which executes it.
if the name and directory is different every time then you will need to do it manually each time or write something which ranems and moves the file to the right name and location.
February 10, 2013 at 12:55 am
Text qualifier remove " from data but not from column names. How can be " removed from the column names.?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply