Load CSV file with Commas within its columns into SQL server using SSIS
Posted on 2011-03-18
I have a CSV file in the following format
Name, Tournament , Code , Cash
"a", "bit $1,000, express", "f" , $1000
"n", "Tour",hold $10", "y" , $10
so here "bit $1,000express" and "Tour",hold $10" are actually in the Tournament coloumn as you can see from my illustration. This is exactly how most of the data in my file is represented.
So when I use SSIS to try to load this CSV file into my SQL 2008 database, the middle column is truncated at the commas that occur with the text of the column into separate columns .
so here "000", and "express" will be moved to next two columns and "hold $10" will be in the next columns after its rightful column.
Does anyone know how to solve this kind of problem in SSIS .
By the way its a large CSV file with many columns .