Solved

How do I convert flat file to MSACCESS

Posted on 2009-06-30
7
455 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now