Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Location
    Spring, Texas
    Posts
    7

    Unanswered: need help with importing data

    I need help with an import. Data is represented in record sets. I can import the data into excel and see it line up, I can not figure out how to import it into access 2000 or 2003.

    Here is a sample of the data in a tsv file format. I have included a few records to show that the number of line items can vari.

    Group 1 Group 2 Ins Phone Pat Name/DOS Pat Ins ID/CPT Dr./Chg $ Referring/Ins Pmt Billed/Oth Ins Pmt Pers Pmts Ttl Adjs % Paid Balance
    Aetna HMO (800) 323-9930 Adam, John BBJTD3WA Beth K Bones MD Oct 22 2003
    Oct 22 2003 99000 15.00 0.00 0.00 15.00 0.00 0 0.00
    Oct 22 2003 76817 331.00 0.00 0.00 40.00 291.00 0 0.00
    Total Total 346.00 0.00 0.00 55.00 291.00 0 0.00
    Aetna HMO (800) 323-9930 Adam, John BBJTD3WA Beth K Bones MD Oct 29 2003
    Oct 28 2003 76817 331.00 0.00 0.00 20.00 311.00 0 0.00
    Aetna HMO (800) 323-9930 Adam, Jane BBH98FSA Virginia M Bones MD Sep 21 2003
    Sep 16 2003 11421 300.00 92.16 0.00 0.00 197.60 30.72 10.24
    Aetna HMO (800) 323-9930 Adam, Jane BBH98FSA Virginia M Bones MD Sep 21 2003
    Sep 11 2003 99214 115.00 67.32 0.00 34.00 37.68 58.5391 -24.00

    Thank you,

    Jumpinjax

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It should work in Access, but if it works in Excel the easiest answer is to save it as a CSV in Excel and then try to import the csv.

  3. #3
    Join Date
    Oct 2006
    Location
    Spring, Texas
    Posts
    7
    Thanks,

    This does not work. I will upload a sample file as a text file. The original format is .tsv tab separated. The problems lies with the rows of data not being properly related, so the first row contains all the name and an ID, then the next row or rows contain the detail for that first row of data.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ouch...nasty..I think you're going to have walk through that one row by row to do an import. The excel export/import might work but it's still going to be difficult. I had to do an import like that for Fannie Mae and it wasn't fun. It involved opening (importing) the file, and then walking through it line by line looking for characters. Sorry I'm not much help but I don't think you'll find an easy import routine, at least not if you care about where each of the characters go. If you don't care about where the characters go, just import it as an image or something like that and save it as an ole object.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2006
    Location
    Spring, Texas
    Posts
    7
    Thanks PFstormy,

    I am afraid that would be the only way. Do you think that a script could be written to handle this? Maybe in Perl where you can identify each tab?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •