Click here to Skip to main content
15,741,818 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

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

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.

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.
Updated 4-Aug-16 2:20am
Maciej Los 4-Aug-16 5:06am    
Do you need to import line which starts with `C190`?

1 solution

1. It would likely be better to import each monthly text file separately. That way, if you encounter a problem with a single file, you will still have entered all the other data.
2. Is there any unique item in the C100 records that identifies the invoice? If so it would be better to use that as the relationship (primary key). Otherwise use some code to generate the unique identifier, perhaps based on year and month of file, plus invoice sequence.
3. Since this is a new system it is unlikely that you will find any code that will do the job. You will need to set about creating a design, based on your requirements and inputs. At minimum you will need to:
3.1. read a file line by line
3.2. split the line into its fields
3.3. generate the invoice number for a C100 line
3.4. add the C100 data to the C100 table
3.5. read each C170 line
3.6. split the line into its fields
3.7. add the C170 data and invoice number to the C170 table


Whether you can do this in VBA is for you to decide based on your knowledge and experience.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900