Decimals Lost when Exporting to CSV file

  • Thread starter Thread starter Len McMorrow
  • Start date Start date
L

Len McMorrow

I have a numeric field that is structured as a
Number/double. When I export the table to a CSV text
file, the decimals are being truncated to 2 places.

Example:

Field in Table = 12.3456
Field in Text File = 12.34

I have alot of data in the table, so I need to keep it as
a CSV instead of fixed width. The only way I can find to
get around this is to store the data as a text field.

Does anyone know how I can keep the decimals from getting
truncated.
 
Use a query to export to the text file. Replace the field with a calculated
field that uses the Format function to properly set the decimal places that
you want:

OutputField: Format([FieldName], "0.0000")
 
thanks for the quick response.

This is very similar to my work-around - where I did it
in the make-table-query. The obstacle I face with this
is that the resulting text file has the field as a text
field (with double quotes around it).

I have some MS Queries that use the resulting CSV/Text
file as an input. If I do this, I then need to unconvert
the text field to a number with an Excel Value function.

I was hopeful for a means to push it out of Access with
all the decimals
in place.

Any thoughts?
-----Original Message-----
Use a query to export to the text file. Replace the field with a calculated
field that uses the Format function to properly set the decimal places that
you want:

OutputField: Format([FieldName], "0.0000")

--
Ken Snell
<MS ACCESS MVP>

I have a numeric field that is structured as a
Number/double. When I export the table to a CSV text
file, the decimals are being truncated to 2 places.

Example:

Field in Table = 12.3456
Field in Text File = 12.34

I have alot of data in the table, so I need to keep it as
a CSV instead of fixed width. The only way I can find to
get around this is to store the data as a text field.

Does anyone know how I can keep the decimals from getting
truncated.
Click to expand...


.
Click to expand...
 
You might try the formatting idea with the val function around that on a per field basis. For example, say you have a field called Weight

Val(Format(Weight, "###.0000")

The Val should ensure that it make it out as a number, not as text. It might help you to export it into Excel and then into comma delimited.
 
Set up an export specification for the text file and use TransferText to
export the query into the text file. In the export specification, you can
specify the formats of the fields being exported.

--
Ken Snell
<MS ACCESS MVP>

thanks for the quick response.

This is very similar to my work-around - where I did it
in the make-table-query. The obstacle I face with this
is that the resulting text file has the field as a text
field (with double quotes around it).

I have some MS Queries that use the resulting CSV/Text
file as an input. If I do this, I then need to unconvert
the text field to a number with an Excel Value function.

I was hopeful for a means to push it out of Access with
all the decimals
in place.

Any thoughts?
-----Original Message-----
Use a query to export to the text file. Replace the field with a calculated
field that uses the Format function to properly set the decimal places that
you want:

OutputField: Format([FieldName], "0.0000")

--
Ken Snell
<MS ACCESS MVP>

I have a numeric field that is structured as a
Number/double. When I export the table to a CSV text
file, the decimals are being truncated to 2 places.

Example:

Field in Table = 12.3456
Field in Text File = 12.34

I have alot of data in the table, so I need to keep it as
a CSV instead of fixed width. The only way I can find to
get around this is to store the data as a text field.

Does anyone know how I can keep the decimals from getting
truncated.
Click to expand...


.
Click to expand...
Click to expand...
 
There is an assumption that exported floats are
currency fields. I guess I don't mind: it is a
useful and helpful assumption for most access
users, and I, like many developers, have my own
VBA code for more specific Inport/Export.

Choosing a VBA example at random from google:
http://groups.google.com/groups?selm=#[email protected]
(Joe Fallon/Export Text File)

(david)
 
Back
Top