Export query to csv file (Access 2000)

Topic

New Reply

I have a query which I have to output as a csv file. One of the fields has data which is to 3 decimal places. My problem is that the csv file formats the numbers to 2 decimal places. Any numbers such as 0.006 are converted to 6.0e-03. These scientific numbers are not recognised by the system importing the data.

Is there any way to specify the number of decimal places in the export specification?
TIA Graham

Reply | Quote

Replies

Replace the field by an expression like this: Format([NameOfField],”0.000″). This will output the value as formatted text.

Reply | Quote

Hans

Thanks for your quick response, however I’m still having problems. I’ve replaced the field with the format expression and it looks OK in the query when viewed in Access, but inspecting the csv file in notepad still shows the scientific notation for values less than 0.01 and all other numbers to 2 decimal places. I am using an export specification as the recipient of the file does not want text qualifiers in the csv file.

Any ideas?
Graham

Reply | Quote

Strange, it seems to work OK on my system – see screenshot (my system uses the comma as decimal separator)

Reply | Quote

Strange, it seems to work OK on my system – see screenshot (my system uses the comma as decimal separator)

Reply | Quote

Are you sure you’re inspecting the right csv file? The export doesn’t change the data … unless you’re using an export spec of some sort that conflicts with the format you’re trying to achieve. Once you export a new csv file using the formatting in the query, you should not see the problem you described. Have you tried deleting the old csv file before creating a new one?

Reply | Quote

Charlotte

I deleted the csv file before exporting to make sure I was looking at the right file. Looking at the first few lines in the export wizard shows the field formatted as I want (3 decimal places). Looking in the resulting csv file with notepad the numbers are two decimal or scientific notation. You mention an export spec that conflicts with the format I’m trying to achieve. I am using an export specification but I cannot see anywhere to alter the format of the fields – just text qualifiers and separators.

Graham

Reply | Quote

Charlotte

I deleted the csv file before exporting to make sure I was looking at the right file. Looking at the first few lines in the export wizard shows the field formatted as I want (3 decimal places). Looking in the resulting csv file with notepad the numbers are two decimal or scientific notation. You mention an export spec that conflicts with the format I’m trying to achieve. I am using an export specification but I cannot see anywhere to alter the format of the fields – just text qualifiers and separators.

Graham

Reply | Quote

Sorted!
I deleted the old export specification and recreated it. It works fine. Thanks for pointing me in the right direction.

Cheers
Graham

Reply | Quote

Sorted!
I deleted the old export specification and recreated it. It works fine. Thanks for pointing me in the right direction.

Cheers
Graham

Reply | Quote

Are you sure you’re inspecting the right csv file? The export doesn’t change the data … unless you’re using an export spec of some sort that conflicts with the format you’re trying to achieve. Once you export a new csv file using the formatting in the query, you should not see the problem you described. Have you tried deleting the old csv file before creating a new one?

Reply | Quote

Hans

Thanks for your quick response, however I’m still having problems. I’ve replaced the field with the format expression and it looks OK in the query when viewed in Access, but inspecting the csv file in notepad still shows the scientific notation for values less than 0.01 and all other numbers to 2 decimal places. I am using an export specification as the recipient of the file does not want text qualifiers in the csv file.

Any ideas?
Graham

Reply | Quote

Replace the field by an expression like this: Format([NameOfField],”0.000″). This will output the value as formatted text.

Reply | Quote