MS Access – Leading Zero Missing When Exporting to CSV

csvexportms access

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.

Best Answer

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)

Related Solutions

Ms-access – Access exporting to CSV is weirdly slow

Off the top of my head I might think it to be related having 4 different DB files and large ones at that (not sure why that would be). Can you easily scope the query to hit 1 vs 4 DB files to see if the preformance is increased?

Sql-server – query a tab-delimited file from SSMS

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

If this activity needs to be repeated frequently, I would suggest a script to create schema.ini. Multiple files can be referenced in the same schema.ini or a separate schema.ini can be included with each text file in its own directory.

Best Answer

Related Solutions

Ms-access – Access exporting to CSV is weirdly slow

Sql-server – query a tab-delimited file from SSMS

Related Question