importing text - how to get decimals correct

[Don Williams]

I am trying to import a text file using fixed width fields and can't figure
out how to get implied decimals.

Data like part_no qty amount

a12345tt001200005000

part_no is a12345tt
qty is 001200
amount is 005000

I want it to import the qty as 12.00 and the amount as 50.000

Can I do that?

Thanks in advance.

 

[Pieter Wijnen]

No way Directly (using DoCmd.Transfer....)
Import it into a text only table
Append the data to the "real" table using a query where the relevant numeric
fields are appended divided by 100
ie
SomeThing like
INSERT INTO RealTable(PartNo, QTY, Amount)
SELECT A.PARTNO, A.QTY/100, A.Amount/100 From TmpImportTable

HTH

Pieter

 

[Jamie Collins]

SELECT A.PARTNO, A.QTY/100, A.Amount/100 From TmpImportTable

From the subject line of this thread and the fact these are amounts, I

would advise the OP to avoid coercing the values to an approximate data
type (i.e. DOUBLE). This would retain the values as an exact data type
(i.e. DECIMAL):

SELECT A.PARTNO,
A.QTY * 0.01, A.Amount * 0.01
FROM TmpImportTable

Jamie.

--

 

[Don Williams]

Thanks for idea. It worked. I wrote a macro to import to temp file and
selected data from temp file and inserted into real table.

I really have much more data than I indicated, wanted to keep it simple,
just needed the principle of how to do it.

Now the new problem, I also have two files to get data from.

The second file has 25 data fields and when I try to write a macro, I can't
when using 2 character field name (which makes reviewing incomming data
difficult), I can't get the sql statement in 255 characters. Short of
making field names one character, how can I overcome this 255 limitation.
Could I write a query getting the fields and replace the select in your
suggestion with a reference to the query and how would I do that?

I tried doing:

insert into realtable(f1,f2,f3) select * from temptable

I only got the first field in the realtable plus I can't divide by 100 or
1000 this way.

Thanks for any help you can offer.

Don

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.repl
ace.with.norway> wrote in