XMPie and Leading Zeros

[Stiv]

I thought I had this solved but it has reared it's ugly head again.

If a Zip5, ZIP4 or DP2 starts with a 0 then it is dropping the 0.

I use a CSV and had been formatting the cells to the proper amount of numbers needed. Now that is no longer working.

I saved the CSV as a TXT tab delimited file but XMPie is still dropping the leading zeros.

Any ideas?

[gnubler]

I would select the ZIP column, copy, and do a Paste Special, select Text, paste into new column. Then just dump the formatted ZIP column.

[t-pat]

I have the answer but am outputting a file right now so can't copy and paste the qlingo - you can figure it out via the gui - the function is FORMAT and you put in however many characters total you want as 0000 - stand by for exact syntax

[t-pat]

FormatNumber(|->[Index], "000")

where"Index" is the name of the content object.

HTH

[gnubler]

Also, when you save as CSV, make sure "Save cell content as shown" is checked in the options, that just might do the trick.

[t-pat]

it won't - and using Excel will quite often ruin any other formatting you need as far as numbers. Try to use Emedit or csved as much as you can. XMPie also likes if every field is surrounded by double quotes.

For this deal I'm sure simply using FormatNumber will work - do this all the time.

[Stiv]

it won't - and using Excel will quite often ruin any other formatting you need as far as numbers. Try to use Emedit or csved as much as you can. XMPie also likes if every field is surrounded by double quotes.

For this deal I'm sure simply using FormatNumber will work - do this all the time.

Is there an easy way to get the double quotes from the CSV into a TXT file? I hate rules in XMPie, I have uDirect bottom of the barrel and I can't save any rules and have to write them over and over again for every file. I copy/paste the QLingo but it is a real pain in my ass.

I don't have the save options from Excel that Gnubler has suggested. Open Office is even worse.

[Stiv]

Not to be a PITA but how would I put the Format into a Postnet formula? This is way beyond my basic skills.

Postnet = Zip5 & Zip4 & DP2 & DPC

I would need FormatNumber for all the columns, yes?

Postnet = FormatNumber(|->[Zip5 ], "00000") & FormatNumber(|->[Zip4 ], "0000")  & FormatNumber(|->[DP2], "00")  & FormatNumber(|->[DPC ], "0")

[t-pat]

it won't - and using Excel will quite often ruin any other formatting you need as far as numbers. Try to use Emedit or csved as much as you can. XMPie also likes if every field is surrounded by double quotes.

For this deal I'm sure simply using FormatNumber will work - do this all the time.

Is there an easy way to get the double quotes from the CSV into a TXT file? I hate rules in XMPie, I have uDirect bottom of the barrel and I can't save any rules and have to write them over and over again for every file. I copy/paste the QLingo but it is a real pain in my ass.

I don't have the save options from Excel that Gnubler has suggested. Open Office is even worse.

use CSVEd to add double quotes. Open the CSV file, go to tools/options uncheck the box that says "Save CSV file with Double Quotes" - then hit ok, then GO BACK and check that box again. Save file. It will have double quotes. Stupid workaround but it is free software and works (it is Windows tho)

Stay away from Excel if you can - sometimes that's all you can use though. CSVed will fix what Excel does to mangle it, 99% of the time.

No way as far as I know to save rules. I have a library of worked up files for different scenarios I saved, and will resize and paste base artwork in these files rather than starting with the original supplied art and pasting qlingo - saves time.

[t-pat]

Not to be a PITA but how would I put the Format into a Postnet formula? This is way beyond my basic skills.

Postnet = Zip5 & Zip4 & DP2 & DPC

I would need FormatNumber for all the columns, yes?

Postnet = FormatNumber(|->[Zip5 ], "00000") & FormatNumber(|->[Zip4 ], "0000")  & FormatNumber(|->[DP2], "00")  & FormatNumber(|->[DPC ], "0")

can't say as we use IMB now and I get it in my data, supplied - we have a dedicated mailing department that preps the data for me, NCOA's it and gets it in the correct presort order and a few other things I have no clue about, I have to add headers to the csv's I get from them and link that to my files and go and make all the fancy stuff function.

[t-pat]

you may want to look at this for future barcoding fun:
https://ribbs.usps.gov/index.cfm?page=intellmailmailpieces

Intelligent Mail Barcode - less stringent rules as far as placement, and more functionality (track and trace, other stuff)

[Stiv]

It works, holy crap. Now I have to figure this out.

Some address' don't have a Zip4, DP2 or a DPC and the new formula is putting 7 zero's after the Zip5.

This is what I meant by these formulas getting out of control.

[t-pat]

then you have to suppress if empty? Not sure what you're doing so ignore if not applicable - do a

if LENGTH [string] > NUMBER 0 then [string] else ""

this is pidgin qlingo but you get the drift?

[Stiv]

I downloaded CSV'ed to my home compy last night. Pretty slick, thanks.

Now all I have to do is get IT to install the app here.

When I add the double quotes do I do it in the first tab at the bottom?

[t-pat]

I downloaded CSV'ed to my home compy last night. Pretty slick, thanks.

Now all I have to do is get IT to install the app here.

When I add the double quotes do I do it in the first tab at the bottom?

just open the csv with csved, go to tools/options/more miscellaneous/double quotes, then uncheck the box that says "Save csv vile with double quotes", click ok. Then go back to the same screen - tools/options..., check the same box you unchecked, click ok again, save. That's all.