Results 1 to 6 of 6
  1. #1
    jdawgwill is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3

    Exporting From Access to .CSV File - single vs double decimals problem!

    Working on an application that imports excel spreadsheets, transforms the data into a specific formats, etc and then exports the data out to CSV files.

    I append all the data to an export table right before I export it out.

    I use a query as the export to get the exact fields I want, order, etc.

    I have set an export specification that is comma delimited, no text qualifiers, etc.



    My issue is that the application that uses these export files needs one of the numeric fields to be a single decimal.

    For whatever reason it keeps exporting the numeric field as 2 decimals and it is driving me crazy.

    I have set the table field to be a double with 1 decimal, so I know the data has only one decimal and I can see it sitting in the table correctly.

    I have set the exporting query to format as general number with 1 decimal, but it still exports with 2 decimals.

    Now, earlier in the project before I had really worked out all the bells and whistles it was exporting correctly with only 1 decimal. This was when I was just using a query to transform and export the data and not appending it to an export table first.

    I am thinking there has to be a way to enforce the single decimal on the data that is exported? And if the data is sitting in the table with single decimal, why is it it exporting as 2?

    Appreciate any input!

    Thanks

    jdawgwill

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when you query the data to the final table, (that field a string),
    then correct it to 1 decimal with: left([field],instrRev([field],".")+1)
    then export to csv.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have set the table field to be a double with 1 decimal,
    if you have used the format property for the field, that merely sets the view and hides the underlying value. The format property does not form part of the export process. Suggest try using a decimal number type with 1dp and scale of 1. Or use the round function


    By way of note


    I append all the data to an export table right before I export it out.

    I use a query as the export to get the exact fields I want, order, etc.
    not sure if you mean column order or row order. But since you are exporting a table, the column order is irrelevant - that will come from the table. Similarly databases store data randomly, so there is no guarantee that the row order of your query will be the same in the table. Better to export a query - which also means less bloat due to appending and deleting data from your table

  4. #4
    jdawgwill is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    when you query the data to the final table, (that field a string),
    then correct it to 1 decimal with: left([field],instrRev([field],".")+1)
    then export to csv.
    My issue with that solution is that I do a number of other queries and reports that use that export data and so it needs it in numeric format otherwise I get data type errors. I am just perplexed why the table can be set as a double data type with 1 decimal precision and yet it will still export with 2 decimals?

    joe

  5. #5
    jdawgwill is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    Appreciate the reply, understood on the format property - the actual table is a double with decimal precision of 1 so the data is sitting in the table correctly - just when I export it as a CSV I get two decimals no matter what.

    re: the round function, I read about how access uses "round to even" logic or bankers rounding - How does this differ from normal rounding over large amounts of data? The data I am working with only goes to one place decimal.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if your calcs only result in 1dp then 1.1 becomes 1.1, there is no impact on rounding.

    I understand your point - you get two dp's on export and only want one. I'm just suggesting things you can try which will take a few seconds or minutes. Do so or not, it is up to you. At the moment don't even know how you are exporting - manually, using transfertext or some other method, also haven't seen any example data of what 'it keeps exporting the numeric field as 2 decimals' actually means - you see 5.2 in the table and 5.20 is exported, or perhaps it is 5.23?.


    I tried with my suggestion of using a decimal datatype and it exported one dp.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem exporting to .dbf file.
    By J Bhujanga in forum Access
    Replies: 1
    Last Post: 03-24-2017, 09:21 PM
  2. Exporting Multiple Queries to a single Text File
    By sam.eade in forum Import/Export Data
    Replies: 6
    Last Post: 05-13-2014, 09:24 AM
  3. Replies: 1
    Last Post: 03-15-2012, 05:41 PM
  4. Replies: 7
    Last Post: 08-05-2011, 10:59 AM
  5. Exporting single query and single report.
    By rfhall50 in forum Programming
    Replies: 2
    Last Post: 02-18-2011, 12:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums