Discussion Locked

Import number w/2 implied decimal places

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am trying to import a text file into Access. The file contains fixed width fields. One of the fields contains a number value with 2 implied decimal places (the decimal is not physically in the field). How do I specify this field in Access so that I end up with a numeric field with 2 decimal positions?

9 replies

by dklippert

Without having to run the data through Excel for editing, you could write a simple Update Query:

[field]/100

by bob sellman

As mentioned, the basic requirement is to run an update query that divides the value by 100.

However, depending upon how you are importing the file you may run into problems because Access may want to treat the field of interest as either text oras an integer.

If either situation happens, you might want to import to a temporary table with the field defined as text or integer, whichever works for the import.

Then define a new column [newfield] that is a number field with 2 decimal places. The update query to run would then be either
[newfield] = (val([field]))/100 if [field] is a text
or it would be the following if [field] is an integer:
[newfield] = [field]/100

Then just append the temporary table field (without the [field]) into your permanent table.

One good thing about calculating the correct value into a new field is that if you accidently run the update query twice, the calculated values will still be correct, rather than effectively dividing the original value by 10000 (100*100).

by sl-campbell

If you are doing this regularly, you might want to consider doing the import in code, using a proceedure in a Module. I do this regularly, (using DAO so you will need to change to ADO if needed).

What I do is I have a definition file (a Schema.Ini) that identifies the structure of the Text/ASC file. When this definition file is located in the same folder as the text file to be imported, the ISAM driver used by ADO or DAO will use it automatically, overiding the standard settings and overiding the act of trying to identify the field types and sizes (note: only one Schema.Ini is allowed per folder - you can have as many text files listed in the Ini as you want that and they do not actually need to be present). Then, in code you create a database object and use the Execute method to import the data into a table and at the same time make any changes needed as done below with the hours being divided by 100 to allow for a decimal. This way you can FORCE the column to be a Number with DOUBLE precision:

myDB.Execute "INSERT INTO Employee (EmpID,LName,FName,Hours) " _
& "SELECT EmpNr AS EmpID,LastName as LName, StdHours AS Hours/100 " _
& "FROM [Emp.Asc] " _
& "IN '' [TEXT;DATABASE=" & sASCPath & ";];"

(Where sASCPath is the path and name of the text file - the ending doesn't matter - it could also be .Txt)

I used the INSERT INTO above as the table already exists, but you could use the SELECT INTO in order to create a table (first checking if it already exists)

The Schema.Ini usd by DAO is written like this:

[Emp.Asc]
rem Employee
ColNameHeader=False
Format=Delimited(,)
MaxScanRows=0
CharacterSet=OEM
NumberLeadingZeros=True
DateTimeFormat=dd.mm.yy
DecimalSymbol=.
Col1=EmpNr TEXT WIDTH5
Col2=LastName TEXT WIDTH 40
Col3=StdHours DOUBLE

[NextTable.Txt]
Rem Def for the next text file
.
.
.

by sl-campbell

(ran out of space above)

This all is VERY simple and once the code and INI are created, you only need to click a button or execute a macro and the rest is done for you. I have been doing it this way for quite some time now and never have any problems (learned this under Old DAO) - and best of all is when you have another new table, you only need to do some simple copying and manipulations and with-in a matter of minutes you have your new import code and def. file ready.
Please note that in this example as well as in the above, it is always assumed that there are always exactly 2 digits available that will always be used as the (only) decimal digits.

by sl-campbell

Please note: The table definition in the Schema.Ini could have been written like this:

[Emp.Asc]
ColNameHeader=False
Format=Delimited(,)
Col1=EmpNr TEXT WIDTH 5
Col2=LastName TEXT WIDTH 40
Col3=StdHours DOUBLE

leaving out some of thesettings to be used (there are also more settings that can be added). The ISAM drive will use the default settings for any that are left out.

This discussion is closed to new replies.