Solved

How do I convert flat file to MSACCESS

Posted on 2009-06-30
7
458 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 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
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.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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