asked
Import a file with different number of fields
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: 5,20131017,ACM10,
B_xxx@gmail.com,S,5,
xxx@gmail.com 5,20131017,ACM10,
B_xxx@yahoo.com,R,5,
xxx@yahoo.com 15,20131017,_DCR5,
xxx@gmail.com,55.228.227.1,m 15,201301017 ,_DCR5,
xxx@gmail.com,65.115.221.1,m 7,20130910,_dfpn,
xxx4@yahoo.com,S 7,20130915,_dfpn,
xxx@gmail.com,S 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.
Do you have a single csv file that has various types of records?
Could you provide a few records for example or at most the first column and what makes it identifiable?
Here's a sample of three out of the 7 different types of records that I have in one file: 5,20131017,ACM10,
B_xxx@gmail.com,S,5,
xxx@gmail.com 5,20131017,ACM10,
B_xxx@yahoo.com,R,5,
xxx@yahoo.com 15,20131017,_DCR5,
xxx@gmail.com,55.228.227.1,m 15,201301017 ,_DCR5,
xxx@gmail.com,65.115.221.1,m 7,20130910,_dfpn,
xxx4@yahoo.com,S 7,20130915,_dfpn,
xxx@gmail.com,S 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][1]), 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. [1]:
http://www.sqlservercentral.com/Forums/Topic561815-148-1.aspx
Following on from your example, as you are looking at a 1 or 2 character first field, you could use LEFT to determine the first 2 characters, and for example split these such that "5," means the first type, "15" for the second, or "7," for the third.
Hi @Liton, My point was that you look at 2 characters - so either "70" or "7," - the comma will prevent the 70 records being lumped in together with the 7 ones.
17 People are following this question.
