Import from a flatfile with rows that have different formats

[holtis]

Hello Folks,

Im trying to use Integrations services to import a file on a daily basis.

My flatfile has to different type of rows that comes from an transaction system, it looks like this:

132,1/1/2008,00,123654,text,1,123.00
132,1/1/2008,00,123652,text,1,23.00
132,1/1/2008,00,123655,text,1,3.00
123,1/1/1/2008,01,149.00
1125,1/1/2008,00,123654,text,1,123.00
1125,1/1/2008,00,123652,text,1,23.00
1125,1/1/2008,00,123655,text,1,3.00
1125,1/1/1/2008,01,149.00;Cash;EUR;01;12

After the date you can see that there is two digits number either 00 or 01. The rows also have a different lengthts.

When ever that columns contains 00 the line should be inserted to a special text file, if the columns contains 01 it should to another file.

How can I solve this in a good way?

One of the problems I have is that when I try to import the rows the flat file connections indicates(erros message) that I have partial row in the file which is true since the the rows with the columns content 01 have more fields then the other.

Thanks for you help.

holtis

[rmiao]

Possible to create separate data files?

[duketin]

Hi,

It's easy to seperate into 2 files as followed:
e:\work>find ",00," flatfile.txt

---------- FLATFILE.TXT
132,1/1/2008,00,123654,text,1,123.00
132,1/1/2008,00,123652,text,1,23.00
132,1/1/2008,00,123655,text,1,3.00
1125,1/1/2008,00,123654,text,1,123.00
1125,1/1/2008,00,123652,text,1,23.00
1125,1/1/2008,00,123655,text,1,3.00

e:\work>grep ",01," flatfile.txt
123,1/1/1/2008,01,149.00
1125,1/1/1/2008,01,149.00;Cash;EUR;01;12

See, you can redirect (>) to a file of your choice, then importing to SQL server.

Best,
duketin

[holtis]

Problem Solved thanks