Exporting not working right

[kfowlow]

I have been exporting data out of Access for the better part of two days.
What I am doing is highlighting the table, right-click, and then pick csv as
an export. One of the columns in my data should have 7 decimal places. When I
export it only has 2 decimal places. The column is set on double and fixed
with 10 decimal places. I can check to see what is exported in a text editor
in a UNIX. There must be a setting that changes this but I can't find it.
Anyone have a clue?

 

[Jerry Whittle]

If you export it with the Save Formatted option, it keeps the decimals;
however, it adds a bunch other junk like so:
---------------------------------------
| 00 | 0.12345678 |
---------------------------------------
Not acceptable I bet. Here's another trick.

1. Create a query based on the table in question.
2. Put the CStr function around the number field like so to convert it from
a number to a string:

TheNumber: CStr([NumberField])

Problem: The CStr function upchucks on null values. You need to deal with it
if there are any nulls in that column. Something like a 0 would work.

3. Export this query instead of the table.

4. When going through the Export Text wizard, choose {none} for the text
qualifier.

 

[Jeanette Cunningham]

Hi,
Yes this is what happens when you export from a table as .txt files
Export from a query with a calculated field.
Create a query from your table including all the fields you want to export.
Create a calculated field.
In a new column in the query in design view type this:
B: IIf(Len([YourNumberField])>0,CStr([YourNumberField]),"")
Replace YourNumberField with your field's name
--B is the new name for this calculated field ( you can give it a different
name)
Now delete the column with the number field (it's not needed as you are
going to export the value from the calculated field instead)
Export the query instead of the table.

The above converts the number to a text string which saves the correct
number of decimal places for each number

Jeanette Cunningham

 

[kfowlow]

The thing is that I have been exporting data from Access easily over the past
few days. I could even get multiple decimal places to export out. Now I can
only get 2 decimal places to explort out. I just created a query, made the
problem column 10 decimal places long, data shows up okay. Highlighted the
whole query data, copied it to Notepad and the problem column on had 2
deicmal places even though it has 10 decimal places on the screen. I think
there is something wrong somewhere on computer to do this.

Hi,
Yes this is what happens when you export from a table as .txt files
Export from a query with a calculated field.
Create a query from your table including all the fields you want to export.
Create a calculated field.
In a new column in the query in design view type this:
B: IIf(Len([YourNumberField])>0,CStr([YourNumberField]),"")
Replace YourNumberField with your field's name
--B is the new name for this calculated field ( you can give it a different
name)
Now delete the column with the number field (it's not needed as you are
going to export the value from the calculated field instead)
Export the query instead of the table.

The above converts the number to a text string which saves the correct
number of decimal places for each number

Jeanette Cunningham

I have been exporting data out of Access for the better part of two days.
What I am doing is highlighting the table, right-click, and then pick csv
as
an export. One of the columns in my data should have 7 decimal places.
When I
export it only has 2 decimal places. The column is set on double and fixed
with 10 decimal places. I can check to see what is exported in a text
editor
in a UNIX. There must be a setting that changes this but I can't find it.
Anyone have a clue?

 

[John W. Vinson]

The thing is that I have been exporting data from Access easily over the past
few days. I could even get multiple decimal places to export out. Now I can
only get 2 decimal places to explort out. I just created a query, made the
problem column 10 decimal places long, data shows up okay. Highlighted the
whole query data, copied it to Notepad and the problem column on had 2
deicmal places even though it has 10 decimal places on the screen. I think
there is something wrong somewhere on computer to do this.

I don't recall having problems with number of decimals, but certainly
exporting date values from a query will ignore the format specified in the
query. You may want to try what I've done in such circumstances: use a
calculated field

ExpNumber: Format([numberfield], "#.00000000")

to cast the number as a string with (in this case 8) decimal values explicitly
included.

John W. Vinson [MVP]