Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I convert flat file to MSACCESS

Posted on 2009-06-30
7
Medium Priority
?
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 750 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 750 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 46

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 46

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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