Importing currency fields from fixed width ascii files

[Fozz]

Background Info:

With Access 2000 on Windows 2000 I've been successfully using DoCmd.TransferText to convert a comma delimited ascii file into a normal Access table.

However, for fixed width ascii files and a schema.ini file I have run into the problem that happens to be described at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;241477
​

This is the error message from the Microsoft item and similar to the one I get:

"Run-time error '3625':
The text file specification 'C:\My Documents\Schema.ini' does not exist. You cannot import, export, or link using the specification."
​

This resolution, below, is mentioned in the Micorsoft item, and works (I'm using a different DATABSE directory than mentioned in the MS item):

Sub ImportSchemaTable()
    Dim db As DAO.Database
    Set db = CurrentDb()

    db.Execute _
    "SELECT * INTO NewContact FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=d:\eraseme;].[Contacts.txt];", _
    dbFailOnError

    db.TableDefs.Refresh

    ' Not in the MS item
    Application.RefreshDatabaseWindow

End Sub

My Current Problem

I cannot figure out how to get fixed width currency fields with an implied decimal point 2 digits from the right, in a given field, to appear in the table correctly. Using USA currency, I want 123456 to appear as $1234.56. What I get is $123456.00

This MS item tells how to specify the number of digits used for the fractional portion of a currency value buy using CurrencyDigits:

http://msdn.microsoft.com/library/d.../en-us/office97/html/workingwithtextfiles.asp
​

Therefore, with this Contacts.txt input data file:

    First     NameLast HireDateCurrency
    Nancy     Davolio  10-22-91    1234
    Robert    King     10-23-91  123456

And with this modified Schema.ini file in the same folder/directory as Contacts.txt, changed to include CurrencyDigits and Col 4:

    [Contacts.txt]
    ColNameHeader=true
    Format=FixedLength
    MaxScanRows=0
    CharacterSet=OEM
    CurrencyDigits=2
    Col1="First Name" Char Width 10
    Col2="Last Name" Char Width 9
    Col3="HireDate" Date Width 8
    Col4="Currency" Currency Width 8

I get $1,234.00 and $123,456.00 in the Currency field.

I guessed at the syntax for CurrencyDigits. Unfortunately, it appears that if you have a spelling error in Schema.ini you don't get an error message. I used Banana=2 instead of CurrencyDigits=2 and did not get an error message

I didn't find any solutions in the "schema.ini" items here.

My next step
I'm going to try one of these alternatives:

1. Use the MS solution mentioned about to create the table and then use queries to "present" the fields in their correct format,
2. Use the MS solution and then a make table query (Select ... Into ... From) to create a corrected table.
3. Write some code to read the ascii file, line by line, and build the table row by, correcting things as I go along,

I'm leaning towards rebuilding the table, using one of the last two alternative, since there are about 250,000 records in the input file. Since I'm going to be importing data more than once, I'll try both alternatives and use the faster one.

Question

1. Is there anything obvious that I have missed in trying to use CurrencyDigits in schema.ini
   
2. Any other suggestions besides the alternatives I mentioned above?

Thanks,
Bill

 

[neileg]

Why not import the numerics as straight integers? Apply formatting and divide by 100 after import either by running an update query or simply using a calculated field in a select query. If this is a one off import, that's what I would do.

 

[Fozz]

Neil,

Thanks for responding. That is probably what I will end up doing in one of my alternatives. It is just annoying that I can't get the CurrencyDigits thing to work.

Thanks again,
Bill