Export to TXT file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pnas - 15 Jan 2008 09:29 GMT

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.

Signature

pnas

Reply to this Message

Jeanette Cunningham - 15 Jan 2008 10:43 GMT

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

>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.

Reply to this Message

Jamie Collins - 15 Jan 2008 11:37 GMT

On Jan 15, 10:43 am, "Jeanette Cunningham"
<n...@discussions.microsoft.com> wrote:

> Access 2003
> There is a way to get the numbers to export with more than 2 decimal places
[quoted text clipped - 6 lines]
> --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.

--

Reply to this Message

Jeanette Cunningham - 15 Jan 2008 19:17 GMT

Thanks Jamie

Jeanette Cunningham

> On Jan 15, 10:43 am, "Jeanette Cunningham"
> <n...@discussions.microsoft.com> wrote:
[quoted text clipped - 50 lines]
>
> --

Reply to this Message

Jeanette Cunningham - 15 Jan 2008 11:13 GMT

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

>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.

Reply to this Message