Reading text file into Access 2007 and creating related tables

Question

Reading Txt file into ms access 2007 and creating related tables

Hello everybody

In a folder I have multiple txt files. Each txt file refers to a month of Digital Tax Bookkeeping of a given company.

Each line that begins with | C100 | corresponds to general data of an invoice while the lines immediately below that start

with | C170 | correspond to their invoice's products. See example below for illustration purpose.

Notice that there is not a "field" with common data between line C100 and lines C170  so that there is no explicit

relationship between a line C100 and the C170 lines immediately below.

|C100|0|1|1369F|01|00|1|2935||01022013|01022013|1170,4|1|0|0|1170,4|9|0|0|0|

|C170|1|000072|LEITE FERM YAKULT 80G|1020|UN|714|0|0|000|1102||714|17|121,37|0|0|0|

|C170|2|033075|QUEIJO POLENGUINHO TRAD 20G|10|CX24|150|0|0|000|1102||150|17|25,5|0|0|0|0||

|C190|000|1102|17|1170,4|1170,4|198,96|0|0|0|0||

|C100|0|1|6990F|55|00|1|35384|20241210557528000274661010000464952334671545|23012013|01022013|63,12|2|

|C170|1|033044|BEB VODKA ICE SMIR LT 269 CRANB LIMAO|24|UN|63,12|0|0|060|1910||0|0|0|0|0|0|0||

|C190|060|1910|0|63,12|0|0|0|0|0|0||

The goal

Create an Access database containing two tables: 'tabC100' table populated with C100 type records and 'tabC170' table populated with C170 type records involving all monthly txt files. The tables should have a relationship linking each other. This field can be an integer Long.

Questions

1. From the standpoint of performance, I must append all monthly txt files into a single big txt file and then import data from the big txt file into the tables  OR I must import via loop each monthly txt files directly into the tables of DB?
2. To create a relationship between the two tables, I must work with the the big text file writing a long X on the line C100 and writing the same long X into C170 lines immediately below the C100 line OR there is other better way (with better performance)?
3. Do you know codes related to these questions? If yes, give me the way to access them (topics or links)?

I appreciate any help.

Thanks in advance.

Answer 1

This is a not a simple to answer, but here is theoretical how it should work. Or how will I do it.

I'll loop through all the files in the folder and, if the file has not been imported yet, do the import. You can easily find a code that will loop through files in a folder.

Create 2 tables first, tblC100 and tblC170 with a number of fields and field types that corresponds to TXT file fields.

Here you need some coding of the import procedure. It will go through files, line by line, and check either for "|C100|" or "|C170|" and write the data into the proper table splitting fields during import.

Also, once when completed, record a file name or/and with the date to avoid duplicate importing.

When you have data into 2 tables, there is no need to set any relations between them, you can easily relate them when you need to.

BTW you do not need manually to create a table. Create 2 text files and copy a single line with C100 into first and a single line with C170 into second and import them into the database using import wizard with text delimiter "|". Access will create tables for you.

Regards,

Branislav Mihaljev

Answer 2

You really should import to a "staging" table, and then perform your data calculations entirely in Access. Assuming your import places the lines in the same order (and it should), you could open a Recordset and walk that recordset.

To import to a staging table, you can use the External Data - Import & Link - Text File ribbon item. Let's assume you import to a table named "tImport":

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM tImport")

Do Until rst.EOF

  If rst(0) = "C100" Then

    ' you're at an Invoice header; create a new record in the Insert recordset:

    Currentdb.Execute "INSERT INTO tC100(Col1, Col2, Col3, etc) VALUES(" & rst(1) & "," & rst(2) & "," & rst(3) & ",etc etc)

    Dim rstInvoice As DAO.Recordset

    Set rstInvoice = Currentdb.OpenRecordset("SELECT @@IDENTITY")    

  Else

    If rst(0) = "C170") Then

      '/ this is an invoice line, so add that:

      Currentdb.Execute "INSERT INTO tC170(Invoice, Col1, Col2, Col3, etc) VALUES(" & rstInvoice(0) & "," & rst(1) & "," & rst(2) & "," & rst(3) & ", etc etc)

    End If

  End If

  rst.MoveNext

Loop

Note you may need to modify your INSERT INTO statements to take into account the datatypes of your fields. For example, if Col2 is a Text file, the INSERT would look like this:

      Currentdb.Execute "INSERT INTO tC170(Invoice, Col1, Col2, Col3, etc) VALUES(" & rstInvoice(0) & "," & rst(1) & ",'" & rst(2) & "'," & rst(3) & ", etc etc)

Note I placed single quotes around the "rst(2)" segment.

If Col2 were a Date field:

      Currentdb.Execute "INSERT INTO tC170(Invoice, Col1, Col2, Col3, etc) VALUES(" & rstInvoice(0) & "," & rst(1) & ",#" & rst(2) & "#," & rst(3) & ", etc etc)

Answer 3

Branislav Mihaljev

Thanks for your help.

Remember. The tables should have a relationship linking each other. Notice that there is not a "field" with common data between line C100 and lines C170  so that there is no explicit  relationship between a line C100 and the C170 lines immediately below.

Answer 4

I see, but this will just add few lines of the code - to read ID of C100 when saved and write it as ID in C170. Of course, ID field must exists in both tables in that case.

Answer 5

Hmm, I thought I had previously replied to this. My solution is similar to Bran's. You HAVE to keep each text file intact. Also because each line is a different record, you have to read them line by line in a loop. As part of your loop you have to store the key value for each C100 record to use as a FK in the C170 records. Also what are the C190 records?

Edit: I did answer this on UA.