1

I have a query I export which is then used to import products into our website.

One of the ways we help people find products more easily is by using a long and short item code. It's basically the last 5 digits of the main code.

People can search for the whole or partial number.

However when I export my query as a csv, any leading zeroes are removed.

Here is how I create the column in Access:

(Right(products_vendor1.shortISBN13,5)) AS item_code

Obviously, in Access I have no problem viewing this query and the values display even with the zeroes.

How can I export the csv without removing the leading zeroes?

These are the settings I'm using when I export:

UTF-8 delimited by comma and quotes ,"

and checking the box Include field names in first row.

And it's not that the leading zeroes are missing only with excel. It doesn't matter if I open with notepad++ or libre office calc

I'm using Access 2013 64bit.

0

Leading zeroes are removed if the exporter thinks the column is numeric. You need to force the exporter to treat that column as text.

For instance, this might work:

chr(34) + (Right(products_vendor1.shortISBN13,5)) + chr(34)
  • This wraps my values in quotes which isn't ideal for my websites importer. Is there another way to declare the column as text instead of number? Because even with adding ch(34) the value still appears as a number if I open the csv in calc. So it requires a two part operation of changing the column to text, then find replacing the quotes. The most ideal situation would be to not have to perform any operations on the file after it is exported. – ZenMasta Oct 16 '15 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.