Reading txt file into ms access and creating related tables

Question

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.

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

Posted 4-Jul-16 2:28am

Member 12085428

Updated 4-Aug-16 2:20am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 4-Aug-16 5:06am

Do you need to import line which starts with `C190`?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Answer 1 · 2016-07-04T03:34:00

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

etc.

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