Import From .txt --- numeric/text problem 2

[Joeclueless]

Hi,

I have a field that I'm importing into a table that is a numeric (sample "000002953&quot. The problem is that this number is a dollar amount and when I import as long integer I get "2953" this is fine except what I want is "29.53". When I import as double, fixed with 2 decimal places I get "2953.00". If I import as text I get "000002953".

In past imports, if I used number, double, fixed with 2 decimal places the decimal would not be tagged onto the end of the numeric value.

So at the end of the day, what I want to accomplish is the imported field "000002953" to become a text field as "29.53".

Anyone know what I can do??

 

[tlbroadbent]

Do the import in multiple steps. Import the data into a temporary table as a Long Integer or Double data type. Run a query to insert the data into the permanent table. Divide the column by 100 or multiply by .01. As a final touch, *if you really want to store numeric data as text, convert it with a format statement or other string function.

format(colname/100.)

Be sure to delete the records from the temp table after processing.

* I don't recommend storing numeric data as text. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[james33]

Terry,
Along the same lines,
I import data from a .txt file into Access to do some adjustments to a field that contains Currency but in the original it is formatted as text.

I format the field as Currency rounding off the calculation to 2 decimal places, then I find I have to export the Table once and then reimport it as Text and strip off the Currency sign before finally exporting it.

All this to avoid Access retaining the extra decimal places.

I know this sounds really longwinded, so is there an alternative?
Grateful for any help
Jim
:-(

 

[MoGryph]

Well, I'm not sure if this is something you're doing on a regular basis, or the exact nature of what you're importing- like- you might have duplicates in the data you're pulling in, or such but- how I do it is this....

I create a Linke'd table, to the text file (if it's the same text file each time, otherwise you'll have to use code create the link'd table on the fly each time).

Then I'd use an create-table query, or append query, whatever, and run that formatting stuff as I want it. I usually do this in code, so it would be like this-

I'd have 2 Recordsets, one being the text file, one being the Table I've just created, or I'm trying to update.

Then I'd run some code like:

DoCmd.RunSQL("APPEND txtLnk.Field1, txtLnk.Field2, txtLnk.Field3, (([txtLnk].[Field4]) /3) " _
    & "FROM txtLnk INTO tbl.Field1, tbl.Field2, tbl.Field3;"

But you should be save that as a query too, if you're always pointing to the same file in the same place. If not, you just have to write some kind of code to DROP the Linke'd text file, and recreate it (same field names), then run the query. Of course, this can all be done straight through VB, or a Macro (calling VB functions) too.

BTW- I could be a little off on the APPEND query up there, since I don't do them much, but you can just make a dummy query with the wizard, and test it from there.

-MoGryph
[8O)

 

[Joeclueless]

Thanks MoGryph

 

[james33]

Thank you MoGryph too!

 

[MoGryph]

Oh boy- I don't know where that "/3" came from. Of course, I meant to have a "/100" in there- just wanted to clarify that.

And, you're welcome 8O)

-MoGryph
[8O)