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

A DMax on an indexed field will be very fast. Other Domain aggregates like Sum and Avg can be very slow.

Answer 2

"You should have gotten an e-mail from UA about responses. "

Sorry Scottgem, but what is UA?? How can I find your article related with my question?

Answer 3

UA is Utteraccess.com You go there, sign in and look for your discussions.

Answer 4

dim s as string 

open "C:\1.txt" for input as #1

line input #1,s

close #1

i would like to read the text file directly and use split function to get each field.