Utility to modify CSV text file ?

[nqtraderman]

Hi, I would like to use an old label printing programme that will only open a specific format CSV text file. The format of the data I have to feed into it is very close but it needs a few tweaks to make it compatible. Namely the inclusion of 2 new blank fields just before the address fields and a couple of column header names changing.

My skills are not up to the job so could someone be kind and write me a utility that will read the input CSV file, convert it and output to a new CSV file ?

I have attached an example of a source file "Source.csv" and the file "Download.csv" which is in the format I require (which I made manually) so you can see the changes needed. (the files are attached as .txt due to this sites restrictions on attaching .csv files)

If it would be possible to drop a CSV file (of any name) onto an icon which the utility converts and creates the new CSV file "Download" that would be brilliant.

I am running Win XP but soon to move to Win 7.

Many thanks

[year+ old attachment deleted by admin]

[Squashman]

When posting a question please try and be more specific and use as much detail as possible.  You said the 2 new fields are being added just before the address fields.  That is not accurate.  They are being added after the Closing Date field. The address fields are still 5 fields farther down. Would have been helpful if you would have stated what fields were being added as well.

I believe this can be done in batch.  Since a FOR loop can easily split the input up based on the comma as a delimiter.  Don't need to worry about changing the Field names.  That can be hard coded into a variable and then immediately output to the new file.  The FOR loop can skip the first record which is your header and add two commas after your closing date field as it is being output to the new file. 

I just need to research again the order of FOR LOOP variables because you have 39. 

[nqtraderman]

Hi, thank you for looking into this for me. I have listed below the changes required to the file format to make it compatible :

1. new field required "Escrow ID" inserted after field "Closing Date"
2. new field required "Invoice ID" inserted after field "Escrow ID"
3. both new fields are empty for all records ("")
4. rename field header "Postage and Packing Amount" to "Postage and Packing"
5. rename field header "Address Line 2/District/Neighbourhood" to "Address Line 2/District"
6. input file name will vary but will always have the .csv suffix
7. output file name to be called "Download.csv"

I hope this helps.

thanks again

[Salmon Trout]

I just need to research again the order of FOR LOOP variables because you have 39.

There are only 26 letters in the alphabet... consider VBScript.

[Squashman]

There are only 26 letters in the alphabet... consider VBScript.

Read about half way down on this page. This can actually be done with a single for loop because the OP only needs the new fields added after the 28th token.
 http://www.robvanderwoude.com/clevertricks.php

I have the for loop written for it already. Just didn't have time to write the echo statement before I went out tonight. Will post it in the morning.

[BC_Programmer]

If you have to call a technique "clever", oftentimes, it's not a very smart technique.

There can only be 52 of the special FOR variables eg %%A %%z active at any one time within the same batch file. Reason: They are case sensitive and there are only 26 letters of the alphabet.

From Here

And:

You can use %%a to %%z, %%A to %%Z and %%1 to %%9 as loop variables, although the number ones are not documented. Obviously you can't have as many implicit variables using "tokens=1,2,3" etc, if you choose to use numbers as the variable symbols in a loop.

,
http://www.computerhope.com/forum/index.php/topic,58229.msg368097/topicseen.html#msg368097


note: Contrex=Dias=Salmon Trout (I hope this wasn't supposed to be "secret"... )

Anyway, and obviously I cannot speak for him, but when you've dealt with tech long enough you learn that it's easier to write a script once, rather than more than once. Where does this come in? Well, undocumented "clever" workarounds and techniques are typically undocumented and take advantage of unforeseen consequences of the behaviour and side-effects of programs, to the point where such behaviour could very well be a bug, in order to achieve some needed effect or acquire some needed piece of information. However, for the most part, what many people call "clever" would be best described as a "disgusting hack"; eventually, something is going to give in the command language and if anything 'buckles' it's going to be the undocumented  "clever" hacks and side effects, not the supported and documented functionality which typically stays usable. Meaning that at that time, the script will need to be rewritten. I've experienced this in Programming languages before; either me, or somebody else, rather than researching what they needed and how they should do it, simply hacked together something that works almost as if by chance, depending on undocumented features or syntax, side effects of functions, and even on occasion the side effects caused by specific versions of system libraries. These break First, and they need to be rewritten. At which point, the person rewriting it has a choice- they can do it quick, or they can do it right. "quick" of course means simply finding and using more hacks, whereas the latter is researching the proper way to do it or staying withing the documented confines of the environment. There is nothing "Clever" about using undocumented features, one might even go so far as to call it foolish. They aren't undocumented because they ran out of paper, they are undocumented because they don't want to commit to any one specific implementation, and as such it is subject to change without notice. The functionality could break with a service pack, or even a simple hot-fix.

Again, I can't speak for him, but I get the feeling Salmon Trout may have the same sort of "why write it twice" idea when it comes to usage undocumented features.

[Salmon Trout]

After I wrote "There are only 26 letters in the alphabet I had a "duh" moment because, of course, there are 52 letters altogether in the upper (%%A - %%Z) and lower case (%%a-%%z) alphabets so if you have 27 to 52 tokens to process you can overflow like this by having a 25th asterisked token and taking 2 cracks at the input line (I did not correct myself before because it was 8 PM on New Years Eve and there was a party to go to.)

@echo off
for /f "tokens=1-25* delims=," %%A in ( ' type "%~dpnx1%" ' ) do (
   for /f "tokens=1-14 delims=," %%a in ( "%%Z" ) do (
      echo The 39 tokens in each line read are now in the %%%% variables A to Z and a to n
      )
   )


So if you just remember which letters refer to which fields it is a trivial procedure to write out the necessary matter to the output file, altering or inserting fields where required.

Trivial but cumbersome, and the code won't be very pretty. I do wonder if just using comma delimiters is going to capture all the blank fields... I'll have to report back on that...

I know about the undocs (everybody who has made a more than cursory examination of alt.msdos.batch.nt* in the Google archive does) but for reasons already mentioned by BC_P I prefer not to *censored* around with them (life's too short). You only have to read the Daily *censored* to see where clever tricks get you, or rather, the poor sap who has to maintain your code in the future. Anyhow that's just my personal opinion.

I also think that they are probably here to stay, they've been there since Windows 2000 at least, and VBScript and Powershell are where the effort is these days. Cmd was always a backwards-compatibility-friendly thing after all. I sometimes wonder if they are "undocumented" simply because they would have made the documentation too complicated. After all, most people who use the Roman alphabet know that Q comes after P, and can tell M from m, but how many people carry ASCII charts around in their heads?

* I recommend this

Anyhow, I am noodling around with a VBScript, and it would help if the OP (nqtraderman) can advise if that would do? Drag and drop would work as long as VBScript is operative on his machine. I must say that this looks like a thoroughly commercial type of task, and while I don't mind bashing out a quick script now and then, (for nothing) I wonder if the OP has set up a business without budgeting for an appropriate level of IT support? Just asking.

(I hope this wasn't supposed to be "secret"...

Now I will have to kill you...

[nqtraderman]

Anyhow, I am noodling around with a VBScript, and it would help if the OP (nqtraderman) can advise if that would do? Drag and drop would work as long as VBScript is operative on his machine. I must say that this looks like a thoroughly commercial type of task, and while I don't mind bashing out a quick script now and then, (for nothing) I wonder if the OP has set up a business without budgeting for an appropriate level of IT support? Just asking.

Hi, thanks for your help. as long as it works is all that matters to me. I'm not sure if I have VBScript on my computer, is it freeware ? how do I check ? The reason for the prog request is so that my other half can print out labels for the occasional eBay sales she does. And the reason for using this freeware label software is that if you want to print just one label on a part used sheet of labels it allows you to do that.  Its not a commercial venture there's just me, my other half and my Border Collie

many thanks

[Salmon Trout]

Hi, thanks for your help. as long as it works is all that matters to me. I'm not sure if I have VBScript on my computer, is it freeware ? how do I check ?

Visual Basic Script is run by the Windows Script Host, which should be present in every Windows version since Windows 2000. I have not seen anywhere a mention of what Windows version you are using, but if you can open a command window and at the prompt type WScript /? and hit Enter, you should see something like this



Click OK to get rid of it, and if you then type CScript /? at the prompt you should get a message like this

Usage: CScript scriptname.extension [option...] [arguments...]

Options:
 //B         Batch mode: Suppresses script errors and prompts from displaying
 //D         Enable Active Debugging
 //E:engine  Use engine for executing script
 //H:CScript Changes the default script host to CScript.exe
 //H:WScript Changes the default script host to WScript.exe (default)
 //I         Interactive mode (default, opposite of //B)
 //Job:xxxx  Execute a WSF job
 //Logo      Display logo (default)
 //Nologo    Prevent logo display: No banner will be shown at execution time
 //S         Save current command line options for this user
 //T:nn      Time out in seconds:  Maximum time a script is permitted to run
 //X         Execute script in debugger
 //U         Use Unicode for redirected I/O from the console

If these things happen then you're OK to use Visual Basic Script.

my Border Collie

That's what convinced me...

[nqtraderman]

If these things happen then you're OK to use Visual Basic Script

Hi, I am using WinXP but likely to move to Win7 sometime this year, and yes both of those work so it looks like I have it.
thanks for your help.

[Salmon Trout]

Quick question - Are you using Excel?

And....

I was analysing your source.txt with a quick script which splits the lines at the commas and I noticed something. When a simple split based on commas was done, comparing the data line to the header line, the data fields after No. 33 (Column AG in Excel) (Address Line 1) were all shifted one to the right because the example field contains a comma ("The Wall, Main Lane"). This does not affect Excel because the data fields have quote marks around them, but clearly it means that any script whether batch or VBScript is going to have to be a bit more cunning than I first thought. Personally when I am setting up Excel spreadsheets like this to use with mail merge e.g. for labels or letters, I don't combine elements like that. What I mean is I don't have this kind of thing


Address1                      Address2     Address3     Postcode
Rose Cottage, High Street     Bromley      Kent         BR1 1AA             
                                                   

I design in sufficient Address fields that I can have just one item in each like this. In fact a few spare don't hurt because MS Word mail merge ignores blank ones.


Address1       Address2        Address3     Address4  Postcode
Rose Cottage   High Street     Bromley      Kent      BR1 1AA             
     

Bearing in mind that a county name has not been part of a Fully Qualified Address for Royal Mail purposes for donkeys years I don't bother with a separate field for that, but I do know that some geographic analysis software requires it. Bristol hasn't been part of "Avon" since 1994, but I still get mass mailed letters with that county name as part of the address. I also know that some people get all funny if they don't see the old ceremonial county name in their address. My socially ambitious auntie was made up that she had got out of Inner London (West Norwood) and made it to a suburb (Beckenham) which was in Kent. She was not pleased that in 1965 the London Borough Of Bromley was created and forced her back into being a Sarf Londoner and she insisted on seeing "Kent" on her address even though you could see Penge from her house. She moved to Tonbridge eventually, her second husband was a bank manager. (And didn't we know it!)

Anyhow, back to my point. Are the data fields liable to contain commas?

(And: if you have Excel and MS Word, did you know it's a piece of, er, pie to do label mailmerges using "Avery" type A4 label sheets? (I get mine from Banner)

And the reason for using this freeware label software is that if you want to print just one label on a part used sheet of labels it allows you to do that

I do this all the time in Word. I have a document saved which is a page of the right type of blank A4 labels (selected in Page Setup) and I just paste the address into the right place on the sheet. (I use my employer's HP laser printer, and I do know that (genuine) Avery's "no jams" guarantee is only valid if the sheet has only been through a printer once, but I have never had any trouble. Anyway maybe you are using an inkjet?)

Just a few thoughts.

[EDIT]

The comma thing could be solved quite easily from Excel - when you export the CSV, first do a search/replace and turn any commas into something else e.g. a space.

Pls let me know your thoughts?






 
 

[nqtraderman]

Hi, let me start by describing the problem end of things and lets see if there is an easier solution.

When my other half sells stuff on ebay she prints a label off (Inkjet) from an A4 sheet of paper with many sticky labels on it. She prints it (manually I think) on the first label on a sheet of 12 sticky labels. The next time, she will use a new sheet of labels even though only 1 label from that sheet will be printed. I only found out when I noticed sheets of hardly used sticky labels in the rubbish bin. So I thought I'd look around on the i*net for a free utility to make printing labels easier and found this programme:

http://www.free-label-printing.co.uk/

which allows her to print an address label(s) and tell it where on the A4 label sheet to start printing from. So if the first 5 labels have already been used you can tell it to start at label #6. Brilliant I thought, no more wasted sheets of labels.

It requires you to do a download of completed transactions from your PayPal account creating a file called "download.csv" The format is as per my earlier attached file Source.txt so I'm stuck with that format. If someone uses a comma in their address, then unfortunately its in the download file. Alas the programme is a few years old and PayPal must have changed the download format slightly so the programme will no longer import the .csv file.

So I first tried importing "download.csv" into Excel and made the changes manually but the resultant .csv file saved gave errors when imported into the label programme. So than I tried OpenOffice 3.3 Calc and it did the same. Although it was better than Excel it insisted on adding double quotes around the field header labels which the label programme didn't like. As I couldn't get around those problems hence why I thought of a another way. If I could have a utility that converted "download.csv" to the required format then she could use the new file and by dragging and dropping onto an icon and it would be dead easy to use and eliminate her - ahem - "occasional" typos !

So thats the problem I'm trying to fix. If there is an easier way for her to print labels starting at any unused label position on a multi-label sheet then I'm open to suggestions ?

I hope this helps
many thanks

[Salmon Trout]

Well, I don't have to pay for the labels I use at work, but even so I shudder at the thought of printing one label and throwing away eleven. They aren't cheap. [quick Google] Well, Label Direct do them at £6.99 per box of 100 sheets, but even so...

I'll see about the script...

[nqtraderman]

Many thanks, that would be great, she isn't good with cut & paste so a utility would be much easier.

Don't worry about the occasional comma in a single address field as per your earlier note, if it occurs in the occasional address its no big deal to spot afterwards and reprint

[Salmon Trout]

I nearly have a first effort ready. Be ready in about half an hour - I'll post the code & guidance on how to run and you can give it a whirl. (I believe I've fixed the comma issue)

[Salmon Trout]

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"


[nqtraderman]

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

[Squashman]

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.

[Salmon Trout]

"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"



[nqtraderman]

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.

[Salmon Trout]

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)


[nqtraderman]

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.

[Salmon Trout]

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

[Daisymae70]

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.