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|1170,4|198,96|0|0|0|0|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 have tried:
To read: the OPEN VBA instruction with INPUT LINE.
To create related tables: append a long integer into
C100
line and the same integer into
C170
lines immediately below.
I appreciate any help.
Thanks in advance.