x

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.

more ▼

asked Oct 18, 2013 at 07:21 PM in Default

avatar image

liton
910 35 41 49

Do you have a single csv file that has various types of records?

Oct 18, 2013 at 09:28 PM Shawn_Melton

Do you have 7 different csv files or 1 file with 7 layouts? If you would provide a sample it would be easier.

Oct 19, 2013 at 04:35 AM dvroman

I have a single csv file that has various types of records

Oct 21, 2013 at 07:42 PM liton

Could you provide a few records for example or at most the first column and what makes it identifiable?

Oct 21, 2013 at 07:55 PM Shawn_Melton

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.

Oct 21, 2013 at 09:07 PM liton
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Oct 20, 2013 at 06:16 PM

avatar image

Dave_Green ♦
5.5k 4 5 10

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.

Oct 22, 2013 at 07:58 AM Dave_Green ♦

The problem with left function is that I have another layout that starts with 70. So when I use the left function it combines the 70 records with 7.

Oct 22, 2013 at 03:37 PM liton

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.

Oct 22, 2013 at 04:16 PM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1219
x27
x22

asked: Oct 18, 2013 at 07:21 PM

Seen: 975 times

Last Updated: Oct 22, 2013 at 04:16 PM

Copyright 2017 Redgate Software. Privacy Policy