Formatting text a text file

[Anthony1312002]

As you can see below I have a text file that I'm trying to import. What I want to do is rip the customer's box number from the botton line, say 96022 as shown in the text file, and put it at the beginning of each line item for that customer and so on. Below the text file is the script I'm using to read the file. How can I get this to work?

Text File
Singles: 97 20,177 90 20,267 4,281,989.21 20,177 90 20,267 4,281,989.21 17,608 2,569
Multiples: 14 603 2 605 638,054.48 966 4 970 638,054.48 785 181
Checks Only: 26 928 21 949 384,530.02 928 21 949 384,530.02 0 928
Check & List: 2 11 0 11 74,152.68 33 0 33 74,152.68 0 33
Checks Only - Suspense: 11 121 1 122 393,600.29 121 1 122 393,600.29 0 121
------ --------- ------ --------- ---------------- --------- ------ --------- ---------------- --------- ---------
96022 World Com Totals: 150 21,840 114 21,954 5,772,326.68 22,225 116 22,341 5,772,326.68 18,393 3,832


Singles: 9 1,643 11 1,654 787,235.89 1,643 11 1,654 787,235.89 1,487 156
Multiples: 3 160 1 161 117,625.88 132 1 133 117,625.88 112 20
Checks Only: 7 144 3 147 229,297.14 144 3 147 229,297.14 0 144
Check & List: 3 7 0 7 20,471.42 20 0 20 20,471.42 0 20
Check Only Suspense: 5 35 1 36 45,908.37 35 1 36 45,908.37 0 35
------ --------- ------ --------- ---------------- --------- ------ --------- ---------------- --------- ---------
96026 BTI Totals: 27 1,989 16 2,005 1,200,538.70 1,974 16 1,990 1,200,538.70 1,599 375


Import Script
<%
'Declare variables
Dim objFile
Dim objText
Dim strTextLine
Dim sql, sql2
Dim CheckDated
Dim CheckDated2
Dim MyTest

'Create FileSystem Object
Set objFile = CreateObject("Scripting.FileSystemObject")

'Create=False (we are reading the file, not creating one)
Dim MyFile, MyFileNew
MyFile = Request.QueryString("RecordDate")
Set objText=objFile.OpenTextFile(MyFile)

'Write each line until the end of the text file,
'as long as opened file (objText) is not at the end
'(the do while not statement will read one line at a time)

'...
Do while not objText.AtEndOfStream
Dim MyDate, MyCustomer, MyChecksFull, arrayText

'assign a line to strTextLine
strTextLine=objText.ReadLine
'data=Split(strTextLine,vbTab)
data=Split(strTextLine)

'------------------------------------------
arrayText = Split(strTextLine," ")

'Splits the text file up into an array - use "," if the values are comma separated

Dim iCount

' For iCount = 1 to uBound(arrayText)
'------------------------------------------
'Write the line
Response.Write strTextLine
'Write an HTML break
Response.Write "<br>"


Loop

Response.Write "Your file has successfully been imported"

'close and erase the file from memory
objText.close
Set objText=nothing
%>

 

[EBGreen]

Can you give a little more info? If this is your input:

Singles: 97 20,177 90 20,267 4,281,989.21 20,177 90 20,267 4,281,989.21 17,608 2,569
Multiples: 14 603 2 605 638,054.48 966 4 970 638,054.48 785 181
Checks Only: 26 928 21 949 384,530.02 928 21 949 384,530.02 0 928
Check & List: 2 11 0 11 74,152.68 33 0 33 74,152.68 0 33
Checks Only - Suspense: 11 121 1 122 393,600.29 121 1 122 393,600.29 0 121
------ --------- ------ --------- ---------------- --------- ------ --------- ---------------- --------- ---------
96022 World Com Totals: 150 21,840 114 21,954 5,772,326.68 22,225 116 22,341 5,772,326.68 18,393 3,832

THen what would you like the output to look like?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Anthony1312002]

What I posted is what the file looks like after it write it back to the browser. Here is what it looks like before that happens. I must warn you. It's a mess.


Formatted File

Singles: 97 20,177 90 20,267 4,281,989.21 20,177 90 20,267 4,281,989.21 17,608 2,569
Multiples: 14 603 2 605 638,054.48 966 4 970 638,054.48 785 181
Checks Only: 26 928 21 949 384,530.02 928 21 949 384,530.02 0 928
Check & List: 2 11 0 11 74,152.68 33 0 33 74,152.68 0 33
Checks Only - Suspense: 11 121 1 122 393,600.29 121 1 122 393,600.29 0 121
------ --------- ------ --------- ---------------- --------- ------ --------- ---------------- --------- ---------
96022 World Com Totals: 150 21,840 114 21,954 5,772,326.68 22,225 116 22,341 5,772,326.68 18,393 3,832


Singles: 9 1,643 11 1,654 787,235.89 1,643 11 1,654 787,235.89 1,487 156
Multiples: 3 160 1 161 117,625.88 132 1 133 117,625.88 112 20
Checks Only: 7 144 3 147 229,297.14 144 3 147 229,297.14 0 144
Check & List: 3 7 0 7 20,471.42 20 0 20 20,471.42 0 20
Check Only Suspense: 5 35 1 36 45,908.37 35 1 36 45,908.37 0 35
------ --------- ------ --------- ---------------- --------- ------ --------- ---------------- --------- ---------
96026 BTI Totals: 27 1,989 16 2,005 1,200,538.70 1,974 16 1,990 1,200,538.70 1,599 375

 

[Genimuse]

Yes, but what do you want it to look like? When you say that you want the customer's box number to be at the beginning of the line "and so on," what do you mean?

Also, are there always those 7 lines in the text file, or can the number of lines change?

 

[Anthony1312002]

Thanks for being patient. What I want to do is make the file look like this.

96022 Singles: 12/13/2004 97 20,177 90 20,267 4,281,989.21 20,177 90 20,267 4,281,989.21 17,608 2,569
96022 Multiples: 12/13/2004 14 603 2 605 638,054.48 966 4 970 638,054.48 785 181
96022 Checks Only: 12/13/2004 26 928 21 949 384,530.02 928 21 949 384,530.02 0 928
96022 Check & List: 12/13/2004 2 11 0 11 74,152.68 33 0 33 74,152.68 0 33
96022 Checks Only - Suspense: 12/13/2004 11 121 1 122 393,600.29 121 1 122 393,600.29 0 121
96026 Singles: 12/13/2004 9 1,643 11 1,654 787,235.89 1,643 11 1,654 787,235.89 1,487 156
96026 Multiples: 12/13/2004 3 160 1 161 117,625.88 132 1 133 117,625.88 112 20
96026 Checks Only: 12/13/2004 7 144 3 147 229,297.14 144 3 147 229,297.14 0 144
96026 Check & List: 12/13/2004 3 7 0 7 20,471.42 20 0 20 20,471.42 0 20
96026 Check Only Suspense: 12/13/2004 5 35 1 36 45,908.37 35 1 36 45,908.37 0 35


And yes, the number of lines do change. I really would like to send you the copy of the text file that I have. I promise you would not be in any danger. I'm really having a hard time with this file. The file is only 4kb in size and there are no viruses. Could I send it to you so you can see what I'm up against?

 

[pkailas]

I didn't test this in ASP, but VB works

this gets you the number of records in your text file

Dim fso as FileSystemObject
dim ts as TextStream
dim iCounter

iCounter = 0
set fso = New FileSystemObject
set ts = fspenTextFile("Your text file")

do while not ts.AtEndOfStream
if InStr(ts.ReadLine, "Totals:") then
iCounter = iCounter + 1
end if
loop
ts.close



loop through it again and populate an array for the Box Number

redim BoxNum(iCounter)
iCounter = 0
set ts = fspenTextFile("Your text file")

do while not ts.AtEndOfStream
if InStr(ts.ReadLine, "Totals:") then
BoxNum(iCounter) = left(ts.readline, 5) 'note that you can parse the box number however you want.
iCounter = iCounter + 1
end if
loop
ts.close

Now you can use your other code and increment through the array.

 

[Anthony1312002]

pkailas, thanks for the reply. How would this look in ASP?

 

[Genimuse]

Almost identical.

 

[Genimuse]

Sorry, that wasn't supposed to be flip. It should work just like that in ASP.

 

[pkailas]

if you need more assistance, you could send me the file and I'll see if I can make it more user friendly

 

[Anthony1312002]

That's okay. Just one thought. I get a undetermined string error when I try to use Dim as when using VB Script.

Say for example when I use Dim fso as FileSystemObject, the error occurs.

Is there a way to get around that?

 

[pkailas]

Try this:

dim fso

set fso = Server.Createobject("Scripting.FileSystemObject")


instead of:

Dim fso as FileSystemObject

set fso = New FileSystemObject

 

[DreXor]

methodology here if you'd like a different way of tackling this, regardless of the sub lines before a total:

Create an array with 2 slots .. Or a ADOR Recordset
FSO open the file.
readline everything into the second slot of the array, keep redim preserve as you go.
also as you go, check for instr(readline, "totals:") if found populate first slot of array with the client/company whatever number..
also check for If replace(replace(line," ",""),"-","") = "" then dont add it (the dash line for the totals, looking for it and stripping it.)

Sample populated Array

blank,singles:etc
blank,multiples:etc
blank,checks:etc
1234,somecompany totals:etc
blank,singles:etc
blank,multiples:etc
blank,checks:etc
5678,someothercompany totals:etc

after complete, for next through the array in reverse, if populate bottom up the client/company number based off of last encountered since the desired number appears at the end of a grouping. insert the values into the first slot..

Sample populated Array

1234,singles:etc
1234,multiples:etc
1234,checks:etc
1234,somecompany totals:etc
5678,singles:etc
5678,multiples:etc
5678,checks:etc
5678,someothercompany totals:etc

then it's just a matter of one last cycle going forewards to output it concactenated to file again.



DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

oops just noticed that you didn't want the totals line, which is just a matter of when checking for the totals line and finding the company number and do one of two things :

1)
add the line in the second slot, and in the reverse cycle populating the first slot, drop those values.

2)
ignore the line like you do the dash lines, and determine the company number as before, but instead of adding a line, put the company value into the first slot of the record prior :

Sample populated Array

blank,singles:etc
blank,multiples:etc
[red]1234[/red],checks:etc
[s]1234, somecompany totals:etc[/s]
blank,singles:etc
blank,multiples:etc
[red]5678[/red],checks:etc
[s]5678, someothercompany totals:etc[/s]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!