Converting CSV text

W

wutzke

If anyone has a minute, I'd like some help getting a start on
converting a chunk of text

I have this copied form a report in PDF


04 ARHB10-36 INSULATED BROWN EA 2.00 0.00 0.00 0.00 0.00 31.50 47.
95
Dec 0.0000 Mar '06 0.0000 Jun '05 0.0000 Sep '04 0.0000 Yr 1 avg :
0.0000
Nov '06 0.0000 Feb '06 0.0000 May '05 0.0000 Aug '04 0.0000 total :
0.0000
Oct '06 0.0000 Jan '06 0.0000 Apr '05 0.0000 Jul '04 0.0000 Mn 1 stk :
0.00
Sep '06 0.0000 Dec '05 0.0000 Mar '05 0.0000 Jun '04 0.0000 Yr 2 avg :
0.0833
Aug '06 0.0000 Nov '05 0.0000 Feb '05 0.0000 May '04 0.0000 total :
1.0000
Jul '06 0.0000 Oct '05 0.0000 Jan '05 1.0000 Apr '04 0.0000 Mn 2 stk :
0.00
Jun '06 0.0000 Sep '05 0.0000 Dec '04 2.0000 Mar '04 0.0000 Yr 3 avg :
0.2500
May '06 0.0000 Aug '05 0.0000 Nov '04 0.0000 1.0000 total : 3.0000
Apr '06 0.0000 Jul '05 0.0000 Oct '04 0.0000 0.0000 Mn 3 stk : 0.00
Month stock average 3 : 0.00

that I want converted to a CSV file like this
ARHB10-36,INSULATED BROWN,,2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Dec '06 ,Mar '06 ,Jun '05 ,Sep '04 ,Nov '06 ,Feb '06 ,May '05 ,Aug
'04 ,Oct '06 ,Jan '06 ,Apr '05 ,Jul '04 ,Sep '06 ,Dec '05 ,Mar
'05 ,Jun '04 ,Aug '06 ,Nov '05 ,Feb '05 ,May '04 ,Jul '06 ,Oct
'05 ,Jan '05 ,Apr '04 ,Jun '06 ,Sep '05 ,Dec '04 ,Mar '04 ,May
'06 ,Aug '05 ,Nov '04 ,Apr '06 ,Jul '05 ,Oct '04
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,2,0,0,0,1,0,0,0

I'm looking for the "ARHB10-36,INSULATED BROWN,2" of the 1st line
which is the Item style, description and current quanity. The rest of
the data is sales by month.

This is just on chunk of text. Each Item style is another chunk. So
next would be
ARHB10-37,INSULATED BROWN,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
 
J

Joel

Here is some code from a previous posting that coverts a textt file to CSV.
It need modification to do what you need it to do. Your problem is pretty
simple except for spliting the first few words..You main problem is taking a
two word style and put it inot one cell.

If you need assistance let me know.


Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine


If (InStr(InputLine, "TEXT") > 0) Then

If Len(OutputLine) > 0 Then
tswrite.WriteLine OutputLine
OutputLine = ""

End If
Else
If Len(OutputLine) > 0 Then
OutputLine = OutputLine + "," + InputLine
Else
OutputLine = InputLine
End If
End If

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub
 
W

wutzke

Great. Thanks for the start.
I loaded this in and set the paths. I created the longtext.txt file.
But I haven't yet gotten it to output into the longtext.csv file. The
file is created but has no content.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top