Import CSV files with dynamic columns each row - Urgent

Question

Hi All:

I had a problem on importing data from CSV file.

Since the data provider did not follow the rule on the formats of CSV that created rows with two different columns, let's say:

type 1 rows: 10 columns

type 2 rows: 15 columns

rather than put 14 "," on type 1 rows, they only put 9 and make the end of Column10 nothing. So can anybody help me on importing the data like this? I am on an urgent job so if there is anyone can give me a hand that will be really appreciated.

Cheers

Johnny

Answer 1

There are multiple options....

You can use SSIS from SQL Server 2012 - it supports changing number of columns in flat files.

Important question is it really a csv file:

1. are the records of type 1 and type 2 should be loaded to the same table?

2. is there any parent-child relationship between these types.

If it is true you will probably have to write script task to parse your file.

Answer 2

Options:

1) SSIS 2012: http://blogs.msdn.com/b/mattm/archive/2011/11/17/what-s-new-in-ssis-for-sql-server-2012-rc0.aspx

2) Read everything as a single column and then split it later on in the process

3) Script Component as source: http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 3

thanks Piotr,

I just tried sql server 2012 and it looks cool thanks :)

Answer 4

thanks a lot

it really helps