I have a csv file (example below) with a different column count based on the first value. There are 7 different layouts and the file doesn’t have a header. Based on the first field I can identity which layout the record belongs to.
Is there a way I can split the file into 7 different files then import it to the appropriate tables? I know there’s a conditional split feature in SSIS, but the problem I’m having is the source file has different number of fields.
Here's a sample of three out of the 7 different types of records that I have in one file:
If the first column is 5 then it will have 7 columns, if the first number is 15 then it have 6 columns, and if the first number is 15 then it will be 5 columns.
Assuming I interpret correctly that you have one file which can have any of 7 different record types on each line, I would look at treating the line as one long string field, using a conditional split to work out which of the 7 it is (for example, using SUBSTRING or LEFT as described here), then using this to output to the 7 different files.
If you treat the entire line as one long string field, you should be able to re-output it to the new files unchanged. This can then be used for 7 separate import routines, 1 each for the 7 different file types.
answered Oct 20, 2013 at 06:16 PM