Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export query to csv file (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export query to csv file (Access 2000)

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

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query to csv file (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export query to csv file (Access 2000)

    Strange, it seems to work OK on my system - see screenshot (my system uses the comma as decimal separator)
    Attached Images Attached Images
    • File Type: png y.PNG (5.3 KB, 0 views)

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export query to csv file (Access 2000)

    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?
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query to csv file (Access 2000)

    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

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query to csv file (Access 2000)

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

    Cheers
    Graham

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •