1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Importing Text Files in Access

Discussion in 'Business Applications' started by curtswanson, Jan 23, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. curtswanson

    curtswanson Thread Starter

    Joined:
    Jun 22, 2000
    Messages:
    41
    When I import text files into Access, sometimes (roughly 10% of the time) the file records do not import into my table in the same order that they had in the text file. Does anyone else experience this and is there a way to fix this so that the records are in the same order? I am using Access 97.

    Thanks.
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Are you appending records, or are you using an import wizard of some kind and making a new table?

    In any case, it will depend first on what the table's primary key is, and second on what other indexes, if any, you have set up. Finally, you can open the table in datasheet view, select any field(s), and use the sort button on the toolbar to change the order in which they appear, then save the table on closing, or see the data in a query sorted any way you please...

    In short, it will depend on what sort of data you're importing (perhaps you could give us some details...), and on what determines how that data is to be ordered. If there is no rhyme or reason to the ordering, other than that that's how the data was entered into the text file, then you will need to use an AutoNumber field, for example, to control the ordering.
     
  3. curtswanson

    curtswanson Thread Starter

    Joined:
    Jun 22, 2000
    Messages:
    41
    I'm using the following visual basic statement:

    DoCmd.TransferText acImportFixed, textfilename & " Import Specification", "temp_" & textfilename, _
    cpath & textfilename & ".txt", False, ""

    Where textfilename and cpath are strings passed into the function. I am using an Import Specification and I am creating a new table with the imported data. None of the fields in the Import Specification are indexed.

    The input data is a text version of a report downloaded from SAP with data in section headers. So it is crucial that the lines of the report are imported in exact order because I need to loop through the report in the original order to get the data values in the report header areas so that I can associate it with the data of the subsequent detail lines.

    I currently import the textfile, check certain data to verify if it loaded correctly and I loop back and redo it if not. There is a possibility that my checking routine might indicate a good load when it actually is not. I load 7 different reports into this database each day and I track the "misses". I've never had to loop back more than three times to get a good load but over about three weeks of running this, there is about a 10% error rate.

    Thanks in advance for any help.
     
  4. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Hmm. I've never had to do this kind of thing, so I'd be hard-pressed to tell you what's going wrong. But you're obviously at ease with code, which is good, and it seems to me like you're repeating the same kinds of processes over and over. I wouldn't transfer that data into a new table each time; I'd use a table that was already set up to grab the stuff in the right order. Because it can't be random, can it? I mean, something has to be determining how the data comes over. And I'm assuming that the data file is somewhat "messy", or you wouldn't have to hit the column headers right?

    Well, I guess we'll just have to get down in it. What in the incoming text is the key? How do you know if it's a good import or not? What's a good representative data set? Details, please, mr.swanson
     
  5. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    Create a table with three fields:

    tblDocuments
    DocID Unique ID specific to a document
    LineID Sequential UniqueID specific to a line in the document
    LineData Data from that line in the document

    Then, instead of using the TransferText function, try reading the file, line by line, in code. The following is taken from the help on the INPUT function and you should be able to modify it to read a row of data, assign the DocID and LineID and then save the data.

    This example uses the Line Input # statement to read a line from a sequential file and assign it to a variable. This example assumes that TESTFILE is a text file with a few lines of sample data.

    Dim TextLine
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    Debug.Print TextLine ' Print to Debug window.
    Loop
    Close #1 ' Close file.

    This way, you have one table with all of your documents, and mulitple lines that can be read sequentially that make up your document.

    Hope that helps...
     
  6. curtswanson

    curtswanson Thread Starter

    Joined:
    Jun 22, 2000
    Messages:
    41
    Thanks to both of you for your help. I think I'll try opening the file for input approach. I haven't done that before. But, in the meantime, I'm attaching a textfile which is the simple example of baseball standings.

    There are nested section headers for Year, League and Division. The detail line is Team, W, L, Pct and GB. The goal is to populate a table with record fields:

    Year
    League
    Division
    Team
    W
    L
    Pct
    GB

    What I do is import the file, in this case, I would probably use fixed widths. Then I read each record, when I find the left-most characters of the first field are "Year:" or "League:" or "Division:" I store the year, league or division in a variable. When I find that the fields "W", "L" and "Pct" are all non-zero, non-null numeric values, I assume it is a detail line. Now I add a record to a new table, using the variable values for year, league and division and the current record values for W, L, Pct and GB.

    So, you can see how it is crucial to have the records imported in exactly the same order as the original document. Otherwise, you might get to a detail line and not have the correct header values.
     

    Attached Files:

  7. curtswanson

    curtswanson Thread Starter

    Joined:
    Jun 22, 2000
    Messages:
    41
    I just looked at my attached text file and none of the line feeds are there so it is pretty jumbled. Here is what it looks like (only the columns would line up properly!):

    Year: 2001

    League: American

    Division: East

    Team W L Pct GB
    NY Yankees 95 65 .594 -
    Boston 82 79 .509 13.5
    Toronto 80 82 .494 16
    Baltimore 63 98 .391 32.5
    Tampa Bay 62 100 .383 34

    Division: Central

    Team W L Pct GB
    Cleveland 91 71 .562 -
    Minnesota 85 77 .525 6
    White Sox 83 79 .512 8
    Detroit 66 96 .407 25
    Kansas City 65 97 .401 26

    and so on
     
  8. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Yes, I'd agree with th here, you're not trying to load into tables correctly (because your initial text-file layout only barely resembles a table), but rather you need to read the line into code, and then, depending on the appearance of certain key words in the line, you need to tell Access where to stick the data.

    There's no point trying to make something that isn't a table act like a table. You can read directly into memory (what you really have is lines of strings, not hard to manipulate in code using InStr and such), and if your table formats are fixed (i.e. you import the same kinds of data day after day), it's very easy to just load up the data into existing tables, either replacing or appending as you wish. But you have to be at ease with VBA to do it; there's no other way that I can see offhand, short of reformatting the text files themselves, breaking them up etc., which wouldn't be the simplest way to go.
     
  9. curtswanson

    curtswanson Thread Starter

    Joined:
    Jun 22, 2000
    Messages:
    41
    This worked great. Thanks for the help!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/66036

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice