How do I convert flat file to MSACCESS

I want to convert a flat from taken from AS400 into MCACCESS database. I established a link to the text file (datafile.txt)
and this is the data -- ALL FIELDS ARE TEXT FIELDS.

MemberID      Patient            DOB            SEX      AGE      PLAN      GRAF      HRAF      CAPAMT
003921062-01      DOE, JOHN1      1928-12-27      M      80      5DK      04224      04287      0130303
007994760-01      DOE, JOHN2      1943-08-12      F      65      65H      01927      00000      0018727
310173353-01      DOE, JOHN3      1933-02-15      F      76      SCN      02333      02666      0063648
310126719-01      DOE, JOHN4      1937-06-15      F      72      SCN      00417      03487      0083248
310157862-01      DOE, JOHN5      1936-07-08      M      72      SCN      01994      03628      004784L

Then, I want to convert the file to a readable format to something like this using SQL in ms access:

MemberID      Patient            DOB            SEX      AGE      PLAN      GRAF      HRAF      CAPAMT
3921062-01      DOE, JOHN1      12/27/1928      M      80      5DK      4.224      4.287      1,303.03
7994760-01      DOE, JOHN2      8/12/1943      F      65      65H      1.927      0      187.27
310173353-01      DOE, JOHN3      2/15/1933      F      76      SCN      2.333      2.666      636.48
310126719-01      DOE, JOHN4      6/15/1937      F      72      SCN      0.417      3.487      832.48
310157862-01      DOE, JOHN5      7/8/1936      M      72      SCN      1.994      3.628      -478.43

-----------------
DOB=DATA/TIME
AGE=NUMERIC
GRAF=NUMERIC
HRAF=NUMERIC
CAPAMT=CURRENCY


I want to create a masterQuery using this converstion.

OUR ASP WILL ONLY PROVIDE FLAT FILE TO US.  I AM SPECIFICALLY HAVING PROBLEMS WITH DATES AND NUMERIC/CURRENCY VALUES.  THE CREDITS HAVE AN "L"  (EX:  004784L should read -$478.40).  
epicazoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Open your database
File
Get External Data > Import
Select txt as the type of file
When prompted, select fixed length fields and the first row as being the column headers
Make sure that each type of field is the desired data type.

After importing, you may need to run some update queries to tweak the data, for instance, your example indicates you will need to trim leading zeroes from the MemberID field.  The numeric fields will need to be one of the floating point types or, possibly, currency for the CapAmt field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BALMUKUND KESHAVCommented:
If you want to do it through VBA then :
sysntex is :
DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

eg:

DoCmd.TransferText acImportFixed, "timport", "tablename", "your text filenamet", yes

Note : for Specificationname , You do import manual first and save the specification with any name and then can be used in VBA. [Its under import and advance option save as spec option]

Bm Keshav

0
epicazoAuthor Commented:
the problem I am having is converting these fields to look like above example:
the GRAF, HRAF, and expecially CAPAMT text field to currency.

eg:  Format([CAPAMT], "Currency") instead of getting $1,303.03 I get $130,303.00 which is not what I want.

Is there a simpler way to import.  I am using wizard and I find myself adding the fields to look like above statement.  
003921062-01      DOE, JOHN1      1928-12-27      M      80      5DK      04224      04287      0130303
007994760-01      DOE, JOHN2      1943-08-12      F      65      65H      01927      00000      0018727
310173353-01      DOE, JOHN3      1933-02-15      F      76      SCN      02333      02666      0063648
310126719-01      DOE, JOHN4      1937-06-15      F      72      SCN      00417      03487      0083248
310157862-01      DOE, JOHN5      1936-07-08      M      72      SCN      01994      03628      004784L
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
you have two choices when converting the date column.  When specifying the type of column in the import dialog, you would specify Date and then specify the ymd format.  If that doesn't work (it should) then you will have to import the column as text and then convert it to a date column.

If you can't convert the column to date in the table design view, you will need to do the following:
1. create a new DATE column
2. save the table change
3. create a new update query that sets the value of the new column equal to CDATE(importedcolumnname)
4. go back into the table design view
5. deleted the imported date column
6. rename the new column as the imported date column name

===============
The conversion of the CapAMT column will actually require two steps.  I hadn't noticed the "L" suffix in the last row.  You will have to import this column as text
1. run an update query on the table
Set CapAMT = "-" & Left(CapAMT, 6)
Where CapAMT Like "*L"

2. Use the table design view to convert the column to float or currency
0
aikimarkCommented:
You will still have to scale the float/currency field by dividing by 100.

Note that the text does not include a decimal character, so the software interprets the value as integer.
0
epicazoAuthor Commented:
sorry experts, I got tied up with another project.   I will look into this and provide some feedback.
0
epicazoAuthor Commented:
Both of you gave me some good tips.  this was a problem with the data I was sent.  Problem corrected.  I split the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.