Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: importing choppy header text file

    Hi,

    I am trying to import this header intensive report into Access in an automated way,if possible. The Federal Project Number is actually the main header that I want in a separate field. There can be more than one maps project under a federal project and there can be many Job numbers under a under the Maps project. The Maps Project detail can be rather choppy but the job number data is more organized. I have 1,200 pages of this data that I would like to import into access. Can anyone point my to some code to start with or would anyone know how to import this "stuff" into access.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    John,

    Boy do I sympathize with you on this one! I've tried to import the file into MS Access and it's all just putting the data in one field. Tried putting it into MS Excel and it's the same way.

    I think it'd be easy to make a database with the data, starting from scratch because it looks like the fields and data would be extremely easy to set up. However, importing it into MS Access and have it make it function. Well, I am stuck but will continue to try to figure it out for you as I'm sure will other members.

    I'll get back to you.

    B

  3. #3
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    I was hoping that maybe I could start with some code that someone used out there for a similiar purpose and try and modify. I'm having trouble just getting the thing off the ground.

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    John,

    If you're just trying to import the file, you can do that easily through the "File" - "Get External Data" - "Import" and just change the file type at the bottom. I've attached what happens if you do it that way though.

    I will se if anything that I have has a way to import via VBA code into Access from either a text/MS Word file or Excel.

    Bryan
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Yeah Brian,

    No I want to separate each category into a new field. Anything that is after the colon would be a record for that field. I want to at least get a good rough pull in and then maybe parse from there. It goes like: Federal project number, maps project number, job number and that job number detail should each have a separate field for that top header...job number, job pi, responsible org, start date.....

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dont think a simple import will work here, although you may be able to improt into a temporary table and then use a query to explode the data into its constituent parts

    personally I'd use a vba procedure, but it depends on your expertese in VBA.

    the problem I can see on importing the text file into a table is how you maintian the realtionship to the parent

    but effectively you have 4 statements you are interested in
    FEDERAL PROJECT NUMBER:
    MAPS PROJECT:
    ..+ its extension line(s)
    the project detail line

    fortunately most of the data is usign fixed postions.. in the sample data you are using spaces to pad the report so breaking apart the lines, once you have recognised what type of line it is is relatively easy using a mis of mid$() statements

    Id expect to see a flow process somethign along the lines of

    open your recordsets for each table to be written to
    open file
    stay in file until eof reached
    while myfile.eof=false
    check it is the expected format (ie check it matches the FPRG format)
    then cycle through the file passing each line to a parsing module.
    if its a FEDERAL PROJECT NUMBER: line then

    if substr(strLineFromFile,1,22) = "FEDERAL PROJECT NUMBER"
    strFedProjNo = trim(mid$(strlinefromfile),22) ' use trim to remove any spaces
    elseif substr(strLineFromFile,1,12) = "MAPS PROJECT"
    strMapsCode = trim(mid$(strlinefromfile)15,7)
    strMapsStatus= trim(mid$(strlinefromfile)37,1)
    .....etc

    then after each line write to the appropriate table
    rsfedproject.addnew
    rsfedproject!projcode = strfedprojno
    rsfedproject.update
    ...etc

    stay in the loop until you have reached the end of file
    close all recordsets
    close the file

    have a look at opening text file sin access
    string functions
    opening recordsets
    write to recordsets
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by John2Chr
    Hi,

    I am trying to import this header intensive report into Access in an automated way,if possible. The Federal Project Number is actually the main header that I want in a separate field. There can be more than one maps project under a federal project and there can be many Job numbers under a under the Maps project. The Maps Project detail can be rather choppy but the job number data is more organized. I have 1,200 pages of this data that I would like to import into access. Can anyone point my to some code to start with or would anyone know how to import this "stuff" into access.
    You need a Perl expert. S/he can turn it into something useful, like CSV or tab delimited format, or send it directly into Access or SQL Server.

    There's really no generic code that's going to do the job. And once you've got a rough import going, you need to check it against all 1200 pages to make sure it's handling any glitchy stuff correctly.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    heres a fairly crude & naive app which may give few pointers as to what you could do

    HTH
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    When I use fs As FileSystem I get the error "compile error automation type not supported in visual basic. Am I missing the correct reference? What library is this one in? Textstream isn't working either.

Posting Permissions

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