Splitting up records in a text field 1

[Syerston]

I wonder would anyone out there be able to help.

I currently import a text file into a SQL table using a small VB executable which calls a DTS package. However the organisation that sends the file has now changed its format. It now includes two different sets of records, each having a different number of fields.

If I could find a way to split the text file it would be a simple matter of running two DTS packages. The records are identified by the first field being either a J or a I.

John

 

[gaudibri]

Hi,

the most simple solution I see is to open two files for output (says JRecords.txt and IRecords.txt) and filling them while parsing the input file.

I don't know the format of your input file but here is a frame you could re-use :

[tt]
' define a text var
dim SLine as string

' creates output files
Open "JRecords.txt" For Output As #1
Open "IRecords.txt" For Output As #2
' open input file
Open FileInput For Input Access Read As #3

' parses the input file
Do while not eof(3)

' Read one line
Line Input #3, SLine
' Copy the line in the appropriate file
If left(SLine,1)="J" Then
Print #1,SLine
Else
Print #2, SLine
Endif
Loop
' Close all file
Close #1
Close #2
Close #3[/tt]

You can then import the data from the two files as you did before.
I hope it can help. Don't hesitate to post again.

 

[Glasgow]

There is a tool that used to be a gem under Unix called Grep that you can get for DOS/windows - try doing "grep for windows" search on Google. Provided that you can distinguish the different line types, this will allow you to set up a simple batch file with two grep calls redirecting output to two separate text files.

e.g.

grep "^J*" InFile >OutFile1
grep "^K*" InFile >OutFile2

would take all lines starting with J and put in one file and ditto for lines starting with K - though there are all sorts of pattern matching possibilities.


Also, have you considered using the BULK INSERT SQL statement instead of DTS?

Good luck

 

[Syerston]

gaudibri

You are an absolute STAR!!!

Many Thanks

John

 

[gaudibri]

ooh look mum, something's glittering...it's a star. Thanks.

 

[SQLBaby]

gaudibri, i am posting one question and its not same like this but like this, hope you will help me too, Please see question, TXT file to SQL TABLE

Thanks

Knowledge is a key of success.