Solved

How do I convert flat file to MSACCESS

Posted on 2009-06-30
7
456 Views
Last Modified: 2012-05-07
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).  
0
Comment
Question by:epicazo
  • 3
  • 3
7 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 24750927
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
 
LVL 6

Assisted Solution

by:BALMUKUND KESHAV
BALMUKUND KESHAV earned 250 total points
ID: 24751835
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
 

Author Comment

by:epicazo
ID: 24754398
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 45

Expert Comment

by:aikimark
ID: 24754414
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
 
LVL 45

Expert Comment

by:aikimark
ID: 24754616
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
 

Author Comment

by:epicazo
ID: 24809544
sorry experts, I got tied up with another project.   I will look into this and provide some feedback.
0
 

Author Closing Comment

by:epicazo
ID: 31598572
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question