Export to TXT file

[pnas]

I have a table that i need to export to a txt file and the problem is when i
convert to txt a decimal number like 23,343667 it export to txt only 23,34.

 

[Jeanette Cunningham]

Access 2003
There is a way to get the numbers to export with more than 2 decimal places
--save the table as a query
--create a calculated field for the number with more than 2 decimal places
for example
if you want 6 decimal places, in an empty field in the query type
A:Format([YourNumberField], "0,000000")
0 is for all numbers to the left of the decimal place holder
000000 is for 6 decimal places, change the number of zeros to suit

--export the query instead of the table
--unfortunately this method gives all the numbers exactly the number of
decimal places specified in the format
--if your number originally was 23.156 it would be exported as 23.156000

I didn't realise that Access 2003 always cut numbers to 2 decimal places
when exporting a table to .txt format.
That is not very useful.

Jeanette Cunningham

 

[Jeanette Cunningham]

There is a better way to do this.
You still need to save the table as a query.
Use a calculated field
In an empty column in the query type this:
B: IIf(Len([YourNumberField])>0,CStr([YourNumberField]),"")

--B is the new name for this calculated field ( you can give a different
name)
--converting the number to a text string saves the correct number of decimal
places for each number
--export the query

Jeanette Cunningham

 

[Jamie Collins]

Access 2003
There is a way to get the numbers to export with more than 2 decimal places
--save the table as a query
--create a calculated field for the number with more than 2 decimalplaces
for example
if you want 6 decimal places, in an empty field in the query type
A:Format([YourNumberField], "0,000000")

--unfortunately this method gives all the numbers exactly the number of decimal places specified in the format
--if your number originally was 23.156 it would be exported as 23.156000

That would export the data as text, could round values (and do you
know which rounding algorithm?), etc. Perhaps better to work with the
raw data

Use a schema.ini file to specify data types or simply get the engine
to generate one for you e.g. ANSI-92 Query Mode syntax:

CREATE TABLE Test (
dec_col DECIMAL(10, 5) NOT NULL
)
;
INSERT INTO Test (dec_col) VALUES (12345.6789)
;
INSERT INTO Test (dec_col) VALUES (9876.54321)
;
SELECT dec_col
INTO [TEXT;Database=C:\;].NewFile#txt
FROM Test
;

The generated file named 'NewFile.txt' contains this data:

"dec_col"
12345.6789
9876.54321

The generated schema.ini file (but could have been created manually in
order to be different from the mdb's schema if desired) contains the
following information schema:

[NewFile.txt]
ColNameHeader=True
CharacterSet=1252
Format=CSVDelimited
Col1=dec_col Decimal Precision 10 Scale 5

Jamie.

--

 

[Jeanette Cunningham]

Thanks Jamie

Jeanette Cunningham

Access 2003
There is a way to get the numbers to export with more than 2 decimal
places
--save the table as a query
--create a calculated field for the number with more than 2 decimalplaces
for example
if you want 6 decimal places, in an empty field in the query type
A:Format([YourNumberField], "0,000000")

--unfortunately this method gives all the numbers exactly the number of
decimal places specified in the format
--if your number originally was 23.156 it would be exported as 23.156000

That would export the data as text, could round values (and do you
know which rounding algorithm?), etc. Perhaps better to work with the
raw data

Use a schema.ini file to specify data types or simply get the engine
to generate one for you e.g. ANSI-92 Query Mode syntax:

CREATE TABLE Test (
dec_col DECIMAL(10, 5) NOT NULL
)
;
INSERT INTO Test (dec_col) VALUES (12345.6789)
;
INSERT INTO Test (dec_col) VALUES (9876.54321)
;
SELECT dec_col
INTO [TEXT;Database=C:\;].NewFile#txt
FROM Test
;

The generated file named 'NewFile.txt' contains this data:

"dec_col"
12345.6789
9876.54321

The generated schema.ini file (but could have been created manually in
order to be different from the mdb's schema if desired) contains the
following information schema:

[NewFile.txt]
ColNameHeader=True
CharacterSet=1252
Format=CSVDelimited
Col1=dec_col Decimal Precision 10 Scale 5

Jamie.