How to read specific columns from flat file and store in to the another flat file

Question

Post reply

How to read specific columns from flat file and store in to the another flat file

jagadeeshb

Right there with Babe

Points: 782
More actions
December 8, 2009 at 7:31 am

#140592

My flat file should look like this
8101109292009300004.0100014363001UNIX
4404658800046382224138299257735138528975000086218910132009121320091213201212132012114.2431212200910.33148494CG11211.11220909200900340R01212009010001010120090101200911.114534WaynWayn1111200911102009abcd
44046588000468092241382992557353449618480001266172091020090813200912112009121320121000312012009000149393CG111112.21120909200900341R01122009020001010120090101200911.114534GEORGI092A,PETERGEORGI092A,PETER1111200911112009wxyz
9000010929200945447704.000
Here in example first column stars with '8' and fifth column '3' and ends
with '9' and '3' these two columns i have to check in that row, between those rows i have to store the data in seperate flat file or database, like this i have to loop each row and needs to verify the conditions like that.
Is any one worked in this if so please send me the sample code or give me your suggestions how to implement the logic for this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 1

[font="Comic Sans MS"]

The DML/file structure is unclear to me.

But as I understand - powerful text parsing tools like perl would probably be best option for you . If you must use SSIS - then you would need to use a script component ..

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

steveb. SSC-Forever Points: 46733 More actions · Answer 2

You should be able to do this in SSIS,

however your description is not clear.

Here in example first column stars with '8' and fifth column '3' and ends
with '9' and '3'

I have read this a couple of time and it dosen't make any sense to me..

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

steveb. (12/8/2009)
You should be able to do this in SSIS,
however your description is not clear.
Here in example first column stars with '8' and fifth column '3' and ends
with '9' and '3'
I have read this a couple of time and it dosen't make any sense to me..

Haha, me neither:-) I think I tried four or five times & gave up.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jagadeeshb Right there with Babe Points: 782 More actions · Answer 4

Hi,

Actually here in the flat file in first row i have to verify the first column having 8 or not and then the 5th column is having 3 or not if then i have to move to the second row and read the specific columns like 1,2,3,7,9,10,15,19,21 these columns i have to read and save in another flat file like this i have to verify each and every row till the first column of other row equal to 9.

This is the flat file comma delimited.

ex: 8,101,2,09292009,3,0,000,4.0,10001436,300,1,UNIX

4,4046580000910223,4046580000910223,1095410955,2009-09-29,2004-01-27,2009-08-12,2049-12-31,1,1.1,3,2009-08-12,C,G,1,2,1,1,2009-08-12,bbb,ggg,2008-05-30,2009-08-12

4,4046580000910231,4046580000910231,1095410956,2009-09-29,2004-01-27,2049-12-31,1,2.2,3,2.1,0,0,0,2,0,1,3,0,0,1

9,101,2,09292009,3,54477,000,4.0,10001436,300,1,UNIX

Here in the example first row first column is 8 and fourth row first column is 9 between this whatever the records are there i have to seperate the records and in that seperated records i have to read only some columns(i.e.,1,2,3,7,9,10,15,19,21) not all columns and need to save in another flat file like this the flat file contains more records.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

You are still not giving us the whole story. That, combined with the fact that your English is a bit dodgy, is making this more difficult to understand than it should be.

You have provided sample source data. Why not provide sample output data too, to illustrate your explanation?

So when column 5 = 5, start sending records to a text file until column 5 = 3 again, with column 1 just marking beginning and end of record set? Is it therefore true that you will not know in advance how many text files you will be creating? Is there a file-naming algorithm? Will there be one output record for every line between the header and footer records?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jagadeeshb Right there with Babe Points: 782 More actions · Answer 6

Hi Phil,

Thanks for your reply and actually the logic is not like that what you are assuming.

The logic is when column 1 = 8 and column 5=3 in that row, condition true comes to next row and read all records till it finds column 1=9 and column 5=3 viceversa. save those transactions in seperate flat file.

I think this one gives you clear idea. so just give me your suggestion that how to implement this by using which controls in ssis package.

Really i am new to this thats why i am asking like a fresher.

Thanks,

Jagadeesh