XMPIE uDirect XMPPOSTNET drops leading zeros from zip codes
696 views
Skip to first unread message
Greg
Dec 31, 2008, 1:27:23 PM12/31/08
to XMPie Interest Group
Hi,
I am trying to set up a variable data project that requires me to
create the postnet barcode. The problem that I am having is that
anytime there is a zero at the beginning of the zipcode, it is ignored
and the barcode is not created. All of the zip codes are the correct
length (5, 9 or 11 digits). I have tried to use .csv and .txt files. I
have formatted the zipcode column in Excel as text, numerical, general
and zipcode, but I always get the same result. Has anyone had this
issue or know a way around it?
Thanks in advance for your help! Greg
I am trying to set up a variable data project that requires me to
create the postnet barcode. The problem that I am having is that
anytime there is a zero at the beginning of the zipcode, it is ignored
and the barcode is not created. All of the zip codes are the correct
length (5, 9 or 11 digits). I have tried to use .csv and .txt files. I
have formatted the zipcode column in Excel as text, numerical, general
and zipcode, but I always get the same result. Has anyone had this
issue or know a way around it?
Thanks in advance for your help! Greg
Eyal Arie
Dec 31, 2008, 3:10:53 PM12/31/08
to xmpie...@googlegroups.com
This is a limitation of data sources that do not have a clear type for each column (like txt and csv files). So when reading the data in this column uDirect is mislead to believe these are numbers. And with numbers leading zero's are ignored.
Try putting quotation marks around the zip codes. This way the column has a clear "text" type and the leading zero's will not get lost.
2008/12/31 Greg <ruff...@hotmail.com>
Gregg
Dec 31, 2008, 3:44:38 PM12/31/08
to XMPie Interest Group
The problem is excel. When the zipcodes are imported it will drop the
zeros automatically. There are ways around this but I would suggest
using MS Access as you will not have this problem.
To save the list that you have now you will need to change the field
with zip codes to type text and fun this function on the zip codes
effected:
=CONCATENATE(0,A1)
Or import your data into access and run a select query that will show
you the fields that have 5 characters and fix manually or with an
update query. Here's a sample of the select query that would show you
the fields with only 5 characters
Select [ZIP]
Where Len([ZIP] = 5)
zeros automatically. There are ways around this but I would suggest
using MS Access as you will not have this problem.
To save the list that you have now you will need to change the field
with zip codes to type text and fun this function on the zip codes
effected:
=CONCATENATE(0,A1)
Or import your data into access and run a select query that will show
you the fields that have 5 characters and fix manually or with an
update query. Here's a sample of the select query that would show you
the fields with only 5 characters
Select [ZIP]
Where Len([ZIP] = 5)
Bill
Jan 1, 2009, 3:58:27 AM1/1/09
to XMPie Interest Group
Try this simple QLingo:
XMPPOSTNET(FormatNumber(|->[myField], "00000"))
XMPPOSTNET(FormatNumber(|->[myField], "00000"))
Timothy Perrett
Jan 1, 2009, 7:20:35 AM1/1/09
to XMPie Interest Group
As Gregg and Eyal point out (to Greg), its a problem with the way the
data source is handled. The bottom line is that Excel simply isn't a
robust data-source and you see these kind of nuances - this one being
the most common. I've seen excel change english dates to american
format with no warning before - obviously don't good when your doing
date based calculations!
Excel as a data-source is generally not a great idea as it can modify
things without asking you all too easily - if you need to use a text
based data source (notice the distinct difference between data-BASE
and data-SOURCE), then id go for quoted CSV and then do any type
conversions in the plan. Alternatively, if you need to use a file
based data-source, then yes, access would probably be the way to go as
it will give you much better type handling and these types of errors
will simply go away.
As Gregg says, you can either fix it at source (in the data), or fix
it inside uPlan as Bill suggests. Both are good solutions - its
personal preference depending on what your most comfortable with.
Cheers
Tim
data source is handled. The bottom line is that Excel simply isn't a
robust data-source and you see these kind of nuances - this one being
the most common. I've seen excel change english dates to american
format with no warning before - obviously don't good when your doing
date based calculations!
Excel as a data-source is generally not a great idea as it can modify
things without asking you all too easily - if you need to use a text
based data source (notice the distinct difference between data-BASE
and data-SOURCE), then id go for quoted CSV and then do any type
conversions in the plan. Alternatively, if you need to use a file
based data-source, then yes, access would probably be the way to go as
it will give you much better type handling and these types of errors
will simply go away.
As Gregg says, you can either fix it at source (in the data), or fix
it inside uPlan as Bill suggests. Both are good solutions - its
personal preference depending on what your most comfortable with.
Cheers
Tim
Greg
Jan 1, 2009, 10:16:22 AM1/1/09
to XMPie Interest Group
Thanks to everyone for your feed back.
I don't have Access, so that option is out. I think that my first step
would be to try Bill's suggestion with QLingo. I'm not sure what some
of the symbols mean. Would using "Open Office" software instead of
Excel be a option in this case? The data file that was supplied to me
was a .jto file. I don't know what kind of file that is, but I was
able to open and modify it with Excel. If I put quotation marks around
the zipcodes as Eyal suggests, will uDirect ignore them when I link to
the data source or will I have to "remove" them somehow?
Greg
> > > Thanks in advance for your help! Greg- Hide quoted text -
>
> - Show quoted text -
I don't have Access, so that option is out. I think that my first step
would be to try Bill's suggestion with QLingo. I'm not sure what some
of the symbols mean. Would using "Open Office" software instead of
Excel be a option in this case? The data file that was supplied to me
was a .jto file. I don't know what kind of file that is, but I was
able to open and modify it with Excel. If I put quotation marks around
the zipcodes as Eyal suggests, will uDirect ignore them when I link to
the data source or will I have to "remove" them somehow?
Greg
>
> - Show quoted text -
Timothy Perrett
Jan 1, 2009, 12:58:53 PM1/1/09
to XMPie Interest Group
> I think that my first step
> would be to try Bill's suggestion with QLingo.
|->[myField] // => simple scheme ref; think of it for a placeholder
the database value with the coloum name myField
Format("12345", "00000") // => given a string, Format takes a second
parameter which determines how the string will be formated. The
"00000" is a placeholder which says always have 5 numbers, so even if
you passed "1" it would then make it 00001.
XMPPOSTNET("someString") // => converts the passed string into a
barcode that works with the xmpie barcode font for postnet.
The functions effectively collapse left if that makes sense - they are
nested. We could also write the statement like this:
XMPPOSTNET(
AsString(
FormatNumber(
|->[myField], "00000"
)
)
)
Does that make any sense / clean things up?
Cheers
Tim
Bill
Jan 1, 2009, 1:08:52 PM1/1/09
to XMPie Interest Group
Yes, sorry for being a little "short" with my previous "explanation".
Anyway, all these QLingo functions may also be available through the
dropdown menus which you can see in the "Edit Rule" page, of each ADOR
object (just remember to check the "Advanced"(*) option, else you
would see only a shorter list of function).
Once you are familiar with it, you can click the "View QLingo"(*)
button, and have a look at the QLIngo code, which the dropdown menus
helped you build.
May I also mention that, as far as I know, the "Format" function has
recently been deprecated (it is advised that you now use the
"FormatNumber" or "FormatDate" function instead).
(*) I use a French copy of uDirect, I think this is the terminology
used in the English version.
Anyway, all these QLingo functions may also be available through the
dropdown menus which you can see in the "Edit Rule" page, of each ADOR
object (just remember to check the "Advanced"(*) option, else you
would see only a shorter list of function).
Once you are familiar with it, you can click the "View QLingo"(*)
button, and have a look at the QLIngo code, which the dropdown menus
helped you build.
May I also mention that, as far as I know, the "Format" function has
recently been deprecated (it is advised that you now use the
"FormatNumber" or "FormatDate" function instead).
(*) I use a French copy of uDirect, I think this is the terminology
used in the English version.
Mark Kuehn
Jan 2, 2009, 10:46:17 AM1/2/09
to xmpie...@googlegroups.com
There are a couple a ways around this. The zipcode formatting in Excel does
not work. I format the ZIP column at text and then create a new column and
use the Excel text() function.
For example purposes lets assume the existing zipcode column is "M" and the
newly inserted column is "N".
In cell N2 enter this formula:
=if(len(trim(m2))<6,text(trim(m2),"00000"),if(len(trim(m2))<10,text(trim(m2)
,"000000000"),text(trim(m2),"00000000000")))
The result of this formula is a string that is left padded with zeros, 5
digits in the first case, 9 in the second, and 11 in the last case.
After applying the formula to all the rows, select the entire newly inserted
column and hit "Copy". Then move to the top of the newly inserted column and
hit "Paste Special..." and select "Values" in the popup. You can now delete
the original Zip column.
The data should now be written properly to have the leading zero in the
data.
Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
file.
-Mark
not work. I format the ZIP column at text and then create a new column and
use the Excel text() function.
For example purposes lets assume the existing zipcode column is "M" and the
newly inserted column is "N".
In cell N2 enter this formula:
=if(len(trim(m2))<6,text(trim(m2),"00000"),if(len(trim(m2))<10,text(trim(m2)
,"000000000"),text(trim(m2),"00000000000")))
The result of this formula is a string that is left padded with zeros, 5
digits in the first case, 9 in the second, and 11 in the last case.
After applying the formula to all the rows, select the entire newly inserted
column and hit "Copy". Then move to the top of the newly inserted column and
hit "Paste Special..." and select "Values" in the popup. You can now delete
the original Zip column.
The data should now be written properly to have the leading zero in the
data.
Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
file.
-Mark
Bill
Jan 2, 2009, 1:42:08 PM1/2/09
to XMPie Interest Group
Nice options, indeed.
However, I still see two advantages for QLingo (or JavaScript or
VBScript, if you operate a uPlan-capable configuration):
* if you are a Service Provider (I mean: a subcontractor, who receives
Excel files from a customer), it can be a little hazardous to edit the
Excel file (some customers here even forbid their subcontractors to do
so);
* using QLingo is database independant: if your Excel file suddenly
becomes an Access, CSV or dBase (without mentiong SQL database, MySQL,
Oracle etc. if you have a uPlan-capable configuration), your XMPie
application will still be compatible. When coding the equivalent of an
Excel formula in Access or SQL can only take a couple of minutes, what
if there are dozens of? What if there are two data sources: one for
proofing (say, an Excel file) and one for production (say, an Access
file). Each slight change in an Excel formula needs to be re-coded in
Access... With QLingo, everything is centralized and shared in one
single XMPie code.
On Jan 2, 4:46 pm, Mark Kuehn <mark_ku...@comcast.net> wrote:
> There are a couple a ways around this. The zipcode formatting in Excel does
> not work. I format the ZIP column at text and then create a new column and
> use the Excel text() function.
>
> For example purposes lets assume the existing zipcode column is "M" and the
> newly inserted column is "N".
>
> In cell N2 enter this formula:
> =if(len(trim(m2))<6,text(trim(m2),"00000"),if(len(trim(m2))<10,text(trim(m2)
> ,"000000000"),text(trim(m2),"00000000000")))
>
> The result of this formula is a string that is left padded with zeros, 5
> digits in the first case, 9 in the second, and 11 in the last case.
>
> After applying the formula to all the rows, select the entire newly inserted
> column and hit "Copy". Then move to the top of the newly inserted column and
> hit "Paste Special..." and select "Values" in the popup. You can now delete
> the original Zip column.
>
> The data should now be written properly to have the leading zero in the
> data.
>
> Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
> file.
>
> -Mark
>
However, I still see two advantages for QLingo (or JavaScript or
VBScript, if you operate a uPlan-capable configuration):
* if you are a Service Provider (I mean: a subcontractor, who receives
Excel files from a customer), it can be a little hazardous to edit the
Excel file (some customers here even forbid their subcontractors to do
so);
* using QLingo is database independant: if your Excel file suddenly
becomes an Access, CSV or dBase (without mentiong SQL database, MySQL,
Oracle etc. if you have a uPlan-capable configuration), your XMPie
application will still be compatible. When coding the equivalent of an
Excel formula in Access or SQL can only take a couple of minutes, what
if there are dozens of? What if there are two data sources: one for
proofing (say, an Excel file) and one for production (say, an Access
file). Each slight change in an Excel formula needs to be re-coded in
Access... With QLingo, everything is centralized and shared in one
single XMPie code.
On Jan 2, 4:46 pm, Mark Kuehn <mark_ku...@comcast.net> wrote:
> There are a couple a ways around this. The zipcode formatting in Excel does
> not work. I format the ZIP column at text and then create a new column and
> use the Excel text() function.
>
> For example purposes lets assume the existing zipcode column is "M" and the
> newly inserted column is "N".
>
> In cell N2 enter this formula:
> =if(len(trim(m2))<6,text(trim(m2),"00000"),if(len(trim(m2))<10,text(trim(m2)
> ,"000000000"),text(trim(m2),"00000000000")))
>
> The result of this formula is a string that is left padded with zeros, 5
> digits in the first case, 9 in the second, and 11 in the last case.
>
> After applying the formula to all the rows, select the entire newly inserted
> column and hit "Copy". Then move to the top of the newly inserted column and
> hit "Paste Special..." and select "Values" in the popup. You can now delete
> the original Zip column.
>
> The data should now be written properly to have the leading zero in the
> data.
>
> Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
> file.
>
> -Mark
>
Greg
Jan 3, 2009, 9:10:38 AM1/3/09
to XMPie Interest Group
Thanks for all of the great ideas. I won't be able to try any of them
until Monday the 5th (I have been off since the 31st).
I will let everyone know how everything worked out with this as soon
as I can. By the way, I will have a new post soon with
reguards to the XMUPCA function and creating UPCA barcodes. I hope I
get as much help with that as I have gotten with this post.
Many thanks and cheers,
Greg
On Jan 2, 10:46 am, Mark Kuehn <mark_ku...@comcast.net> wrote:
> There are a couple a ways around this. The zipcode formatting in Excel does
> not work. I format the ZIP column at text and then create a new column and
> use the Excel text() function.
>
> For example purposes lets assume the existing zipcode column is "M" and the
> newly inserted column is "N".
>
> In cell N2 enter this formula:
> =if(len(trim(m2))<6,text(trim(m2),"00000"),if(len(trim(m2))<10,text(trim(m2)
until Monday the 5th (I have been off since the 31st).
I will let everyone know how everything worked out with this as soon
as I can. By the way, I will have a new post soon with
reguards to the XMUPCA function and creating UPCA barcodes. I hope I
get as much help with that as I have gotten with this post.
Many thanks and cheers,
Greg
On Jan 2, 10:46 am, Mark Kuehn <mark_ku...@comcast.net> wrote:
> There are a couple a ways around this. The zipcode formatting in Excel does
> not work. I format the ZIP column at text and then create a new column and
> use the Excel text() function.
>
> For example purposes lets assume the existing zipcode column is "M" and the
> newly inserted column is "N".
>
> In cell N2 enter this formula:
> ,"000000000"),text(trim(m2),"00000000000")))
>
> The result of this formula is a string that is left padded with zeros, 5
> digits in the first case, 9 in the second, and 11 in the last case.
>
> After applying the formula to all the rows, select the entire newly inserted
> column and hit "Copy". Then move to the top of the newly inserted column and
> hit "Paste Special..." and select "Values" in the popup. You can now delete
> the original Zip column.
>
> The data should now be written properly to have the leading zero in the
> data.
>
> Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
> file.
>
> -Mark
>
>
> The result of this formula is a string that is left padded with zeros, 5
> digits in the first case, 9 in the second, and 11 in the last case.
>
> After applying the formula to all the rows, select the entire newly inserted
> column and hit "Copy". Then move to the top of the newly inserted column and
> hit "Paste Special..." and select "Values" in the popup. You can now delete
> the original Zip column.
>
> The data should now be written properly to have the leading zero in the
> data.
>
> Or you could accomplish the same with a Qlingo and/or JavaScript in the Plan
> file.
>
> -Mark
>
> On 12/31/08 12:27 PM, "Greg" <ruffa...@hotmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I am trying to set up a variable data project that requires me to
> > create the postnet barcode. The problem that I am having is that
> > anytime there is a zero at the beginning of the zipcode, it is ignored
> > and the barcode is not created. All of the zip codes are the correct
> > length (5, 9 or 11 digits). I have tried to use .csv and .txt files. I
> > have formatted the zipcode column in Excel as text, numerical, general
> > and zipcode, but I always get the same result. Has anyone had this
> > issue or know a way around it?
>
>
>
>
>
>
> > Hi,
>
> > I am trying to set up a variable data project that requires me to
> > create the postnet barcode. The problem that I am having is that
> > anytime there is a zero at the beginning of the zipcode, it is ignored
> > and the barcode is not created. All of the zip codes are the correct
> > length (5, 9 or 11 digits). I have tried to use .csv and .txt files. I
> > have formatted the zipcode column in Excel as text, numerical, general
> > and zipcode, but I always get the same result. Has anyone had this
> > issue or know a way around it?
>
Greg
Jan 9, 2009, 3:36:49 PM1/9/09
to XMPie Interest Group
Hello again,
Just wanted to let everyone know how my project is going.
I mentioned that I didn't have Access to work with and I also don't
have the option of working in QLingo. My only choice is with the ADORs
in my InDesign plugin. I used Mark's formula in Excel and it worked
perfectly. I also experimented with Open Office software. I opened the
Excel spreadsheet in Open Office, formatted everything as text and
saved the spreadsheet as .csv file. This worked fine.
So, thanks to everyone who helped or tried to help. I am grateful.
Cheers to all,
Greg
Just wanted to let everyone know how my project is going.
I mentioned that I didn't have Access to work with and I also don't
have the option of working in QLingo. My only choice is with the ADORs
in my InDesign plugin. I used Mark's formula in Excel and it worked
perfectly. I also experimented with Open Office software. I opened the
Excel spreadsheet in Open Office, formatted everything as text and
saved the spreadsheet as .csv file. This worked fine.
So, thanks to everyone who helped or tried to help. I am grateful.
Cheers to all,
Greg
Reply all
Reply to author
Forward
0 new messages