Manipulate CSV file

[bmquiroz]

Hi all,

Need some help formatting a CSV file using FSO? Basically I have written an Excel macro that pulls data from Excel and saves it into a CSV file but when I open the file in a text editor the formatting appears to be off.

This is what I have.
CI,20060320203300,testuser,1,,,,
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317
3,testbankcode1,,nyc,testbankaccount1,usd,50001.00,20060317
99,13,100001.00,,,,,

This is what I need.
CI,20060320203300,testuser,1
3,testbankcode,,nyc,testbankaccount,usd,50000.00,20060317,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
3,testbankcode1,,nyc,testbankaccount1,usd,50001.00,20060317,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
99,13,100001.00

How can I get FSO to remove the commas in the header and trailer records and append about 39 to the records in between?

Thanks.

 

[Sheco]

Do you want to change the operation of the Excel macro?

Or do you want to make a .VBS that fixes the output file that was already created?

 

[bmquiroz]

Either one I guess as long as it works! Have been stumped on this one for days.

Here is the Excel VBA code.

Dim rng As Range
With Worksheets(ActiveSheet.Name)
Set rng = .Range("G2", .Cells(Rows.Count,"G").End(xlUp))
End With
wkbName = Format(Now, "mmmm_dd_yyyy_hh_mm_ss") & "_" & ActiveSheet.Name
RWcount = Selection.Rows.Count
r = RWcount + 2
Set rngSave = Selection
Set wkbNew = Workbooks.Add
Set wksheet = wkbNew.ActiveSheet
With wksheet.Range("A1")
.Resize(1, 4).Value = Array("C1",[Text(Now(),"yyyymmddhhmmss")], "testuser", "00000001")
rngSave.Copy .Offset(1, 0)
.Offset(1, 8).Resize(RWcount, 39) = ""
.Cells(r, 1).Value = "99"
.Cells(r, 2).Value = RWcount
.Cells(r, 3).Formula = "=sum(" & rng.Address(1, 1, xlA1, True) & ")"
End With
Application.DisplayAlerts = False
wkbNew.SaveAs Filename:="c:\" & wkbName & ".csv", FileFormat:=xlCSV
wkbNew.Close

Thanks for your help!

 

[Jerz]

Anything unique about the last row? Is it the only one that starts with 99 in the first field?
Does this describe what you want?:
Read the file
split the first row on commas
write 4 fields with commas inbetween fields to temp file
continue reading all but the last row
write current content plus additional 39 commas to temp file
split the last row on commas
write 3 fields with commas inbetween fields to temp file
copy temp file over original (optional)

 

[bmquiroz]

Jerz thanks for the reply. No there is nothing unique about the last row (trailer record), it is static and yes the only record with 99 in the first field. The operation you described is correct! That is exactly what I am looking to do, which is why I thought that maybe using FSO ReadLine > WriteLine would do the trick, just really swamped at the moment and have no time to play around with code.

If you can get some code together, I would appreciate it.

Thanks!