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

Mr Scott McDaniel

Thank you for your clever explanation.

Although I have understood the main idea of your bright code, I have some questions about:

1. " 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"

So importing by External Data - Import & Link - Text File works even if the C100 line have a number of columns diferent from the C170 line' s number of columns??

2.  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)   '<<<< code row Y

    Dim rstInvoice As DAO.Recordset

    Set rstInvoice = Currentdb.OpenRecordset("SELECT @@IDENTITY")    '<<<<< code row X

I never heard about the statemant 'Select @@Identity' until now. I presume that the entire code row X creates a record that is identical to the last record created by code row Y. Is that correct? Does this statemant 'Select @@Identity' work at ms access 2007 plataform?

Well, you give a lot of code to work with. The result I will tell you soon.

Best regards.

Answer 2

So importing by External Data - Import & Link - Text File works even if the C100 line have a number of columns diferent from the C170 line' s number of columns??

No, you can't Import or Link to a file that has different records on each line. That's why you HAVE to read the file in line by line.

Answer 3

Hi Scottgem

Helpful your answer about ask one.

What about the question number two (see number "2")?

2.  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)   '<<<< code row Y

    Dim rstInvoice As DAO.Recordset

    Set rstInvoice = Currentdb.OpenRecordset("SELECT @@IDENTITY")  '<<< code row X

I never heard about the statemant 'Select @@Identity' until now. I presume that the entire code row X creates a record that is identical to the last record created by code row Y. Is that correct? Does this statemant 'Select @@Identity' work at ms access 2007 plataform?

By the way, C190 line in the example just to say that there are another kind of "record".

You have answered this post in UA, you said. Where do I find this?

Thanks.

Answer 4

I'll let Scott McD answer that because I'm not sure what that does, I've never used it in Access SQL.

I would not bother with Recordsets, here. I would just parse the line into an array or variables to populate the Values clause from. 

You should have gotten an e-mail from UA about responses. You can also log in and check "My Discussions:

Answer 5

I am wondering is it doable without using a recordset - he has to know the ID of C100 and use that ID on C170?

(or to manually generate a number - ID - on the fly and use such number like max() +1)?