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)