File parser to retrieve a txt file.

Question

question

AbhiD asked Jan 17, '12

File parser to retrieve a txt file.

Hi Guys, I have a file say sample.txt or a .csv file and this file contains a header, data and a footer. Based on the information in header i need to put the data into the database. In other words i need to extract the data based on the header ~~bytes~~ characters, remove the the footer of the file and put it in the Database. FYI, everything will be a single string of data and the calculation of header, data and footer should be done on basis of ~~bytes~~ characters ... for example ...the total ~~bytes~~ characters for header or footer component is 700. Size of data component will be variable. Which is the best approach for this? **Edit**
Sample File (from comment below)


BOF      XXXXXXX XXX LOT SELECTIONSVS3CR DATA OF  01/17/2012 
A3CR5JQ   5JQ0051201166764100120113                A1R                                              AOMS       01R                                                      20120113201201132012011320120119                    000000000020000000000000000000010521                                                                                                                                                                                                                                                                                                                                                                                  
T3CR5JQ   5JQ0051201166764100120113                A1R                                              N                      100112      20100112    000000000020000000                  000000000000008006                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
A3CR5JQ   5JQ0051381166764100120113                A2R                                              AOMS       01R                                                      20120113201201132012011320120119                    000000000020000000000000000000010521                                                                                                                                                                                                                                                                                                                                                                                  
EOF      XXXXXXX XXX LOT SELECTIONSSC3CR DATA OF  01/17/2012Total Detail Records: 03

import-data flat-file recommendation

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AbhiD commented · Jan 17, 2012 at 08:47 AM

let me correct myself .. everything will be based on the number of characters .. 700 characters for each component.

0 ·

Answer 1 · 2012-01-17T11:00:24Z

Grant Fritchey answered Jan 17, '12

I'd use SSIS. You can read read the file in, put in conditional logic, pretty much everything you'd need to make this work. It should be a lot easier than trying to do it all in TSQL too.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AbhiD commented · Jan 18, 2012 at 07:27 AM

hey Grant, but how will i break the file to separate the header, data and footer ... and eventhough if i have them separated then how should i decode the file ... i think i should develop a file parser or so .... please advice me how shd i go abt it

0 ·

Answer 2 · 2012-01-17T12:56:54Z

Pavel Pawlowski answered Jan 17, '12

I agree with @Grant Fritchey to use SSIS. However using SSIS can be also tricky, if the same file can come with different header/footer and have different internal structure as SSIS requires that the structures of the sources and destinations are constant. However with right conditional logic and separate data flows and/or packages for different file structure and a control mechanism to launch the right one for particular structure the SSIS way will be probably the easiest one to maintain.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AbhiD commented · Jan 18, 2012 at 07:29 AM

hey Pavel, can u suggest me something by which i can put the whole internal structure of file into SSIS and then decode my file based on that.....

0 ·

Pavel Pawlowski commented · Jan 18, 2012 at 12:29 PM

Based on sample.. I suggest to develop a single package per file structure and a master package which will decide what child package to execute based on the header row. For the determination a script task can be used to open the flat file, read first row to determine file type. According the file type appropriate child package for processing can be executed. **In the child package there could be in general two possibilities.** 1) Use only a flat file connection, which skipping first row and defining all the columns for particular file structure with eventual error rows redirection - the footer row should be redirected to error. 2) Use flat file connection, which will skip first header row. Then define a single column which will contain whole row. After that a script component can be used for particular custom splitting. This splitting can be handled by eg. Regular Expression or whatever method necessary to process the row correctly. When a footer row comes to the script component it will be simply ignored by the code.

0 ·

AbhiD commented · Jan 19, 2012 at 07:04 AM

thanks pavel .... i will start working on these lines ..lets c how it goes ...

0 ·

Answer 3 · 2012-01-17T15:17:52Z

Fatherjack answered Jan 17, '12

can you post any part of the file? It is possible that LogParser will be able to help you with this situation but I would need to see the header and the first few lines to confirm.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AbhiD commented · Jan 18, 2012 at 07:02 AM

hi all, this the sample file ....all the X's and rest will be constant in header n footer but every time the date will be different .... each record begins with a A or C .... BOF XXXXXXX XXX LOT SELECTIONSVS3CR DATA OF 01/17/2012 A3CR5JQ 5JQ0051201166764100120113 A1R AOMS 01R 20120113201201132012011320120119 000000000020000000000000000000010521 T3CR5JQ 5JQ0051201166764100120113 A1R N 100112 20100112 000000000020000000 000000000000008006 A3CR5JQ 5JQ0051381166764100120113 A2R AOMS 01R 20120113201201132012011320120119 000000000020000000000000000000010521 EOF XXXXXXX XXX LOT SELECTIONSSC3CR DATA OF 01/17/2012Total Detail Records: 03

0 ·

Answer 4 · 2012-01-18T11:39:07Z

TimothyAWiseman answered Jan 18, '12

It depends on how much processing needs to be done. For basic things, I concur with Grant that SSIS is the way to go. For more advanced pre-processing I personally think Python or Perl are more malleable, but SSIS can accomplish just about any prepocessing you might wish with the right conditional logic and separting out the flows. I personally find Python far more intuitive for that sort of advanced conditional processing. [Expanded slightly for clarity]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question