Utility to modify CSV text file ?

Paste this code into Notepad and save it to a folder (any folder, including the desktop) with a name of your choice and the extension ".vbs"

Drop a text file onto it and it should create a file called "Download.csv" in the same folder as the file that you dropped.

The input file should correspond to the format of your "source.txt"

The output file will (I hope) correspond to your "download.csv"

Please advise of progress.

Code: [Select]

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ReadFile  = objFSO.OpenTextFile   (wscript.arguments(0),  ForReading)
Set objFile = objFSO.GetFile(wscript.arguments(0))


wscript.echo "Ready to create file: "& VBCRLF & objFile.ParentFolder & "\Download.csv"

Set Writefile = objFSO.CreateTextFile (objFile.ParentFolder & "\Download.csv", ForWriting)
' Read input file all at once
strText = ReadFile.ReadAll
Readfile.Close
' Split input file into lines and store in array
arrFileLines = Split(strText, vbCrLf)
' Process first (headers) Line
StrHeaderLine = arrFileLines(0)
arrHeaderFields = Split(StrHeaderline, ",", -1, 1)
strNewHeaderLine=""
For k=0 To 16
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'Rem modified Header 17
strNewHeaderLine = strNewHeaderLine & " Postage and Packing" & ","
For k=18 To 27
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' New Header 28
strNewHeaderLine = strNewHeaderLine & " Escrow ID" & ","
' New Header 29
strNewHeaderLine = strNewHeaderLine & " Invoice ID" & ","
For k=28 To 32
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' Modified Header 33
strNewHeaderLine = strNewHeaderLine & " Address Line 2/District" & ","
For k=34 To 37
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(38)
Writefile.writeline strNewHeaderLine
For j = 1 To (UBound(arrFileLines)-1)
    ReadLine  = arrFileLines (j)
    arrFields = Split(Readline, Chr(34), -1, 1)
    OutputLine=""   
    ' Split data lines at quote marks
    ' Even numbered lines starting at 0 will be commas
    ' odd numbered lines will be the fields
    ' commas in fields are OK now
   
    ' Process up To & including Invoice Number
    For k=1 To 63 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    ' Add the new blank fields
    ' Escrow ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Invoice ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Add all remaining fields except last
    For k= 65 To 75 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    ' Add last field without trailing comma
    outPutLine=OutputLine & Chr(34) & arrFields(77) & Chr(34)
    Writefile.writeline outPutLine
Next
Writefile.Close
wscript.echo "Download.csv written - processing completed"


Wow it is so nearly there
But I got this error when I imported the download file:
"Import failed because the file does not have delimited first line"

So I added a comma to the end of the first (header) line in download.csv and tried importing it again and it worked !!
If you can change that you have it sorted !!
thanks

Good Job Salmon Trout. Definitely needed vbscript to handle the comma issue.

I have an old bactch file that kind of dealed with that issue. It would count the delimiters in the line before processing the line. If it had to many it would just kick that line out to an error file to be manually fixed first.

"Import failed because the file does not have delimited first line"

So I added a comma to the end of the first (header) line in download.csv and tried importing it again and it worked !!

Whoops! I thought I was "tidying up" by eliminating the final trailing comma... that I assumed was a mistake... I shall reinstate it forthwith (quick edit!):

It is now present both in the header line and the following data line(s) (as per the originals)

Hope this works... off to bed now... been watching "Sherlock" (Benedict Cumberbatch brilliant!)... will check tomorrow to see if it worked...

Code: [Select]

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ReadFile  = objFSO.OpenTextFile   (wscript.arguments(0),  ForReading)
Set objFile = objFSO.GetFile(wscript.arguments(0))


wscript.echo "Ready to create file: "& VBCRLF & objFile.ParentFolder & "\Download.csv"

Set Writefile = objFSO.CreateTextFile (objFile.ParentFolder & "\Download.csv", ForWriting)
' Read input file all at once
strText = ReadFile.ReadAll
Readfile.Close
' Split input file into lines and store in array
arrFileLines = Split(strText, vbCrLf)
' Process first (headers) Line
StrHeaderLine = arrFileLines(0)
arrHeaderFields = Split(StrHeaderline, ",", -1, 1)
strNewHeaderLine=""
For k=0 To 16
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'Rem modified Header 17
strNewHeaderLine = strNewHeaderLine & " Postage and Packing" & ","
For k=18 To 27
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' New Header 28
strNewHeaderLine = strNewHeaderLine & " Escrow ID" & ","
' New Header 29
strNewHeaderLine = strNewHeaderLine & " Invoice ID" & ","
For k=28 To 32
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' Modified Header 33
strNewHeaderLine = strNewHeaderLine & " Address Line 2/District" & ","
For k=34 To 37
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'
' AS PROMISED HEADER NOW ENDS WITH A COMMA                   
'
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(38) & ","
Writefile.writeline strNewHeaderLine
For j = 1 To (UBound(arrFileLines)-1)
    ReadLine  = arrFileLines (j)
    arrFields = Split(Readline, Chr(34), -1, 1)
    OutputLine=""   
    ' Split data lines at quote marks
    ' Even numbered lines starting at 0 will be commas
    ' odd numbered lines will be the fields
    ' commas in fields are OK now
   
    ' Process up To & including Invoice Number
    For k=1 To 63 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    ' Add the new blank fields
    ' Escrow ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Invoice ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Add all remaining fields except last
    For k= 65 To 75 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    '
    ' Add last field *** with *** trailing comma
    '
    outPutLine=OutputLine & Chr(34) & arrFields(77) & Chr(34) & ","
    Writefile.writeline outPutLine
Next
Writefile.Close
wscript.echo "Download.csv written - processing completed"



That is brilliant !!   ... it worked first time

I notice that if you click the .vbs icon without dropping a file on it, then you get a MS script error popup, as expected. Is it possible to replace that with a Warning popup instead so if she does click it, it will remind her the icon will only work when you drop a .csv file on it ?
That's a nice-to-have and not necessary so if its a lot of work then please ignore.

Many thanks for your help.

I notice that if you click the .vbs icon without dropping a file on it, then you get a MS script error popup, as expected. Is it possible to replace that with a Warning popup instead so if she does click it, it will remind her the icon will only work when you drop a .csv file on it ?

Updated version below. If you double click or in some other way start the script with no file supplied you get this:



If you drop a file (it doesn't have to have any particular name or extension but it should conform to the format of your source.txt that you posted) you get this:



Requirements for dragged file:

* It must reside on a writeable medium such as a hard drive (e.g. NOT a write-protected pen drive or CD-ROM) because the utility is hardwired to create the download.csv in the same folder.
* It must be a CSV file in format, the name can have spaces, and the extension can be anything you like (or absent).
* It must have a header line as per your source.txt with at least the same number of fields.
* It must have at least one data line, with at least the same number of fields, and they must have quotes around them.
* It doesn't matter if the data fields have commas in them.

Until you click OK in this dialog, any previous file called "download.csv" in the same folder as the dragged file can be renamed or moved out of the way. Once you click OK, the script will obliterate it and over write it with the new one.

Click OK and the processing starts.

When it is done (this should be more or less instantly) you get this



Note: if the dragged file is wrong in some way, e.g. it is a csv but has the wrong number of fields or lines, or if it is a wrong type of file altogether, (a Word document or a jpg or whatever!) then you would get a standard MS error message like this at the processing stage (It would be quite complex to make a custom message for this so you will have to rely on guiding the user to only drag the right file, unless you want to enforce a definite file name policy?)



Obviously, details in the messages such as the script name and the file paths are going to be different on your machine.

As I said above, the output file name "download.csv" is hard-coded, as is the folder where it will go, (the same one as the input file) and it will permanently obliterate and replace any file called download.csv that happens to be in there already.

All testing and writing has been done on Windows 7 so you should be OK if you upgrade your OS at some later date.

Let me know how you get on.

Code: [Select]

Const ForReading = 1
Const ForWriting = 2
MsgTitle = "CSV Transformer Utility"
If wscript.arguments.Count = 0 Then
dummy = MsgBox("There was an error:" & vbcrlf & "You need to drop a file!" , vbOKOnly + vbCritical, MsgTitle)
wscript.Quit
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ReadFile  = objFSO.OpenTextFile   (wscript.arguments(0),  ForReading)
Set objFile = objFSO.GetFile(wscript.arguments(0))
InputFileName=objFile.Path
dummy = MsgBox("Input file: " & vbcrlf & InputFileName & vbcrlf & vbcrlf & "Ready to create file: "& VBCRLF & objFile.ParentFolder & "\Download.csv" , vbInformation, MsgTitle)
Set Writefile = objFSO.CreateTextFile (objFile.ParentFolder & "\Download.csv", ForWriting)
' Read input file all at once
strText = ReadFile.ReadAll
Readfile.Close
' Split input file into lines and store in array
arrFileLines = Split(strText, vbCrLf)
' Process first (headers) Line
StrHeaderLine = arrFileLines(0)
arrHeaderFields = Split(StrHeaderline, ",", -1, 1)
strNewHeaderLine=""
For k=0 To 16
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'Rem modified Header 17
strNewHeaderLine = strNewHeaderLine & " Postage and Packing" & ","
For k=18 To 27
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' New Header 28
strNewHeaderLine = strNewHeaderLine & " Escrow ID" & ","
' New Header 29
strNewHeaderLine = strNewHeaderLine & " Invoice ID" & ","
For k=28 To 32
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
' Modified Header 33
strNewHeaderLine = strNewHeaderLine & " Address Line 2/District" & ","
For k=34 To 37
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(k) & ","
Next
'
' AS PROMISED HEADER NOW ENDS WITH A COMMA                   
'
strNewHeaderLine = strNewHeaderLine & arrHeaderFields(38) & ","
Writefile.writeline strNewHeaderLine
For j = 1 To (UBound(arrFileLines)-1)
    ReadLine  = arrFileLines (j)
    arrFields = Split(Readline, Chr(34), -1, 1)
    OutputLine=""   
    ' Split data lines at quote marks
    ' Even numbered lines starting at 0 will be commas
    ' odd numbered lines will be the fields
    ' commas in fields are OK now
   
    ' Process up To & including Invoice Number
    For k=1 To 63 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    ' Add the new blank fields
    ' Escrow ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Invoice ID
    outPutLine=OutputLine & Chr(34) & "" & Chr(34) & ","
    ' Add all remaining fields except last
    For k= 65 To 75 step 2
    outPutLine=OutputLine & Chr(34) & arrFields(k) & Chr(34) & ","
    Next
    '
    ' Add last field *** with *** trailing comma
    '
    outPutLine=OutputLine & Chr(34) & arrFields(77) & Chr(34) & ","
    Writefile.writeline outPutLine
Next
Writefile.Close
dummy = MsgBox("Processing completed" , vbInformation, MsgTitle)


That works great, thank you so much for writing this for me, it will be very useful.
Most appreciated.
thanks again and Happy New Year.

You are very welcome - do feel free to suggest any tweaks or alterations that you feel may be useful.

Seem to open myself up as a target when I say something, but have to say I am impressed with all this even if it is way over my head.