VB Script - Read Line - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

VB Script - Read Line

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-05-23 : 14:27:30

I need to read all lines in a file and based on the first digit, save that whole line to a new file, so let's say:
If first caracter = 1, then save line to C:\1.txt
If first caracter = 2, then save line to C:\2.txt
If first caracter = 3, then save line to C:\3.txt

Thanks!

---

Thanks!
Igor.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 14:37:54

Good luck on getting someone to write a VB Script for you when you haven't even shown how far you had gotten. We offer free help here. But what you are asking us to do is beyond free help. We'd be happy to guide you in the write direction, but you have to at least show that you made an attempt to do the work on your own.

If you need help with VBScript syntax, then this is what I have used:
http://www.devguru.com/Technologies/vbscript/quickref/vbscript_intro.html

Tara Kizer
aka tduggan

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-05-23 : 14:46:10

Thanks for your Reply Tara,

I understand what you're saying, unfortunatetly I'm a newbie when it comes to programming, what I've done so far was trying to manipulate the data using T-SQL, pushing the data to tables and work with LEFT and SUBSTRINGS. Thought it would be easy in VBS, just wanted something to begin with, not the whole solution. I'll do the research on the web site you provided.

---

Thanks!
Igor.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-23 : 19:42:00

Actually there's a built-in DOS command (findstr) that can do this very easily. Open a command prompt and try the following:

findstr /B "1" test.txt >c:\1.txt
findstr /B "2" test.txt >c:\2.txt
findstr /B "3" test.txt >c:\3.txt

Sure beats the hell out of doing it another way.

Run "findstr /?" to get all the parameters you can use.

<edit> Now, if you wanna get sexy with it:

for /L %a in (1,1,3) do findstr /B "%a" test.txt >%a.txt
...or...
for %a in (1 2 3) do findstr /B "%a" test.txt >%a.txt

The 1st one is a for loop that increments a number, the 2nd one is a for loop that iterates a list. The for command can also read data from files. It's extremely handy, if you'd like to learn more I have some examples here:

http://weblogs.sqlteam.com/robv/category/128.aspx

</edit>

JBelthoff
Posting Yak Master

173 Posts

Posted - 2006-05-24 : 06:08:50

You can use the File Scripting Object for this in Classic ASP which is vbscript. It has a read line feature. You would also us it to create and write to new files as well.

Checking the first charectar of a string is a simple if Left(str, 1) statement.

Look around the Asp sites for FSO.

JBelthoff
Dodge, Duck, Dip, Dive & Dodge
If a man can dodge a wrench, he can dodge a ball!

Asp Hoting Provider

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-05-24 : 14:28:15

Thanks a bunches guys, appreciate the help. This is what I've done so far, from anohter job we have: I'm Dim-ing some unecessary stuff that will be removed afterwards. The code below, is picking all the records and dumping on V1.txt... I'm getting close

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
public V1RecordFile, V2RecordFile, V9RecordFile
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 = "C:\"
Const OriginalFileDir = "C:\"

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 left((lineread),1) = "1" then get_V1Records(lineread)
if left((lineread),1) = "2" then get_V2Records(lineread)
if left((lineread),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 get_V1Records(lineread)
recordtype = mid(lineread,1,1)
record = mid(lineread,2,280)
fullrecord = recordtype & "|" & record
V1RecordFile.WriteLine(fullrecord)

end function

function get_V2Records(lineread)
recordtype = mid(lineread,1,1)
record = mid(lineread,2,280)
fullrecord = recordtype & "|" & record
V1RecordFile.WriteLine(fullrecord)

end function

function get_V9Records(lineread)
recordtype = mid(lineread,1,1)
record = mid(lineread,2,280)
fullrecord = recordtype & "|" & record
V1RecordFile.WriteLine(fullrecord)

end function

---

Thanks!
Igor.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-05-24 : 16:33:58

Hey Guys -
I got it, see my solution below. Thanks for the help!

'**********************************************************************
' 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 = "C:\"
Const OriginalFileDir = "C:\"

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

---

Thanks!
Igor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources