?
Solved

Reading file based on first caracter

Posted on 2006-05-23
5
Medium Priority
?
207 Views
Last Modified: 2010-04-17
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.
0
Comment
Question by:igorblackbelt
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Clever_Bob
ID: 16747352
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!
0
 

Author Comment

by:igorblackbelt
ID: 16752549
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.
0
 

Author Comment

by:igorblackbelt
ID: 16755661
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
0
 
LVL 7

Accepted Solution

by:
Clever_Bob earned 500 total points
ID: 16766391
Looks great. You'll be a VB guru yet!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Progress
Introduction to Processes

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question