asked on

Reading file based on first caracter

Hello All -
I'm writing an program in VB Script (MS SQL Server 2000, DST ActiveX) that needs to open a file, read line by line and base on the first caracter save the whole line based on the first caracter, which works as a record indicator. I should have a total of 3 record types on that master file and the master file will have multiple records per record type.

Also, I have to preserve the length of the line, as I'll then load those 3 files to 3 different SQL tables afterwards.

This is how I'm thinking on how to attack on this:

If first caracter = 1, then save record to C:\1.txt
If first caracter = 2, then save record to C:\2.txt
If first caracter = 3, then save record to C:\3.txt

Thanks in advance for the help.

Clever_Bob

I can offer a few pointers.

My first observation: Why do you create the files and then upload them to SQL server? It seems like an unecessary double-handling when you could just open the textfile and write the relevent lines directly to the database. Perhaps creating these text files is another part of your requirement.

You don't exactly state where you are running into problems so I'll just offer a general solution.

1) You'll need to use the filesystem object to import the data into your script e.g.

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTF = oFSO.OpenTextFile("C:\Addusers\AddUsers.csv",ForReading,True)

2) Create a loop that goes right through your textfile

Do While oTF.AtEndOfStream <> True
'// Read a line of the file
sLine = oTF.ReadLine
'// test for the character you want
if left (sLine, 1) = "1" thhen
save record to C:\1.txt 'just pseudocode!

end if

if left (sLine, 1) = "2" thhen
save record to C:\1.txt 'just pseudocode!

end if

That should get you started!

igorblackbelt

ASKER

Bob -

First thanks for your reply, I believe the code you provided should get me started, but let me get you more info on my issue.

This master file that I'm getting should have 3 types of records, let's name them records 1, 2 and 9. Records 1, are different from 2 and 9, 2s are different from 1 and 9, 9s are different from 1 and 2. Why different ? Because they have different fields, Records 1 have CustomerInfo, 2s have OrdersInfo and 9s have ProductsInfo.

The request I got is to receive this data dump and create reports off of this info, so after speaking with the original developer of the file, he said I would need to break his master file into 3 different files, one with Records 1, another with records 2 and another with records 9 and the first digit tells me what record type that is.

And since I'm no VB guru, I thought one of E-E gurus could help me with that, I believe your code will sufice my needs, let me do some testing here and I'll post the results.

Once again, thanks for your reply.

igorblackbelt

ASKER

Bob -

I got it, see my solution below, let me know what your thoughts.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
public V1RecordFile, V2RecordFile, V9RecordFile, readoriginalfile
public RecordType

Function Main()
Dim fso, f1, ts, originalfile, s, todaysDate

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const WorkArea = "E:\Reports\voyager\today\"
Const OriginalFileDir = "E:\Reports\voyager\today\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set originalfile = fso.GetFile(OriginalFileDir & "M1.txt")

Set V1RecordFile = fso.CreateTextFile(WorkArea & "V1.txt", True)
Set V2RecordFile = fso.CreateTextFile(WorkArea & "V2.txt", True)
Set V9RecordFile = fso.CreateTextFile(WorkArea & "V9.txt", True)

Set readoriginalfile = originalfile.OpenAsTextStream(ForReading, TristateUseDefault)
Do While Not readoriginalfile.AtEndofStream

      lineread = readoriginalfile.ReadLine
      if mid(lineread,1,1) = "1" then get_V1Records(lineread)
      if mid(lineread,1,1) = "2" then get_V2Records(lineread)
      if mid(lineread,1,1) = "9" then get_V9Records(lineread)

Loop

readoriginalfile.Close

'HeaderRecordFile.Close
V1RecordFile.Close
V2RecordFile.Close
V9RecordFile.Close

      Main = DTSTaskExecResult_Success
End Function

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'the following are functions called as this script goes throught the file line by line. Each function breaks up a line and writes
'the data to a dumpfile.
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Function V1
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
function get_V1Records(lineread)

            recordtype = mid(lineread,1,1)
            record = mid(lineread, 2, 280)
            fullrecord = recordtype & record

if recordtype = "1" then
            V1RecordFile.WriteLine(fullrecord)
end if

End Function

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Function V2
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
function get_V2Records(lineread)

            recordtype = mid(lineread,1,1)
            record = mid(lineread, 2, 280)
            fullrecord = recordtype & record

if recordtype = "2" then
            V2RecordFile.WriteLine(fullrecord)
end if

End Function

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Function V9
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
function get_V9Records(lineread)

            recordtype = mid(lineread,1,1)
            record = mid(lineread, 2, 280)
            fullrecord = recordtype & record

if recordtype = "9" then
            V9RecordFile.WriteLine(fullrecord)
end if

End Function

ASKER CERTIFIED SOLUTION

Clever_Bob

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial