Reading Txt file into ms access 2007 and creating related tables

[Crônico]

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.

 

[Galaxiom]

The lack of a common field for a relationship is a problem with importing the file to a single table.

Use a loop to read the file line by line as a TextStream parsing each line with the Split() function and writing to the two recordsets based on the tables. Include another field in each table to accept the value of the loop counter. These fields will become the relationship fields. Increment the counter each time you encounter a header line.

 

[Tieval]

I would suggest going slowly here, an expert could write a piece of code to just do everything instantly but it is rather complicated.

The first thing you need to do is get the data into access and you can do this using the import text file function. Once you have managed to get this to import everything successfully you can save the method used in the import text file function and then use it in a vba function using the Transfertext function.

Once you can automatically import it you can monthly either import an individual file or a date range from a file with everything in it.

The table you have created can be used as a temporary table and queries used to copy data to correct places (table fields) in the database, you can then delete the contents of the initial table.

As to reference points, you cannot beat google:
Try: Access import text file and Access VBA Transfertext

 

[arnelgp]

identify first the column names for each delimited text on the txt file.

 

[Galaxiom]

I would suggest going slowly here, an expert could write a piece of code to just do everything instantly but it is rather complicated.

It isn't complicated at all, just a simple loop.

The first thing you need to do is get the data into access and you can do this using the import text file function. Once you have managed to get this to import everything successfully you can save the method used in the import text file function and then use it in a vba function using the Transfertext function.

Once you import the text into a single table you would have to use queries to extract it into the two tables. At this point you no longer have control of the order of lines and the association between the header and items.

How are you going to add the fields for the relationship between the two tables?

 

[Tieval]

If you walk through the import text file wizard it will help you identify all the fields from the text file and create a suitable table.

Afterwards using the saved configuration file with transfertext you can just import into the same table if you have emptied the data from it.

(That's for you Cronico, not arnelgp who I know is far better at this sort of thing than me!).

 

[Tieval]

Galaxiom, I am not disputing your method, just offering an alternative for a beginner.

In answer, if you have a set of tables with all your relationships set you can transfer into them from the original table. Your text file will have the same order as your original table you import into so no ordering should be lost. If you need anything else you can always set it in the append queries.

 

[Galaxiom]

Galaxiom, I am not disputing your method, just offering an alternative for a beginner.

Your proposed technique does not support the association between the header and item lines. How do you propose to get the headers into one table and the items into another with a common field to form a relationship when the values for those fields are not in the original data?

If you need anything else you can always set it in the append queries.

Please elaborate the nature of such queries.

You could open a recordset against the imported table and loop through it adding the counter to the relationship fields. However there is no guarantee that the records in the recordset will be in the same order as the original text file. You might as well loop through the text file where the order is guaranteed.

Your text file will have the same order as your original table you import into so no ordering should be lost.

The order of records you see in an Access table should never be relied upon under any circumstance.

 

[jdraw]

cross posted at http://www.utteraccess.com/forum/index.php?showtopic=2037831

and here

 

[Tieval]

The order of records you see in an Access table should never be relied upon under any circumstance.

Append the records to fields in a table with a primary key, the records will append in the correct order and can then be sorted against the primary key to maintain the correct order

 

[Galaxiom]

Append the records to fields in a table with a primary key, the records will append in the correct order and can then be sorted against the primary key to maintain the correct order

Ok. Now you just need to explain how you are going to use insert queries to add the key fields for the relationship between the two tables.

The loop code also has an advantage of being able to validate the data before adding it to the final tables. When importing a text file, Access writes an ImportError table if it encounters something unexpected. Importing it to a staging table as text avoids this issue but moves the potential for rejection to the proposed insert queries.

Also note that the importing table is another object to manage. Reading the data from the text file directly into the two tables avoids this entirely.

 

[Tieval]

Not arguing here, personally I would use your approach because I can. However for a beginner there are easier options which also help the learning curve.

If you copy the data to a temporary table you can then develop all the queries you like to amend the data and copy it to your correct tables which can also already contain all the correct relationships.

As a rough example you can have a table with types such as C100, C170, C190 etc. related to a main data table. You can use an update query to change C100 to 1 in your temporary table. After importing to your main data table the 1 will relate to the types table and you will know it is a C100.

Rough and ready approach but on completion a beginner will understand tables and queries. They can then work out how to convert the queries to simple vba with a whole series of docmd.runsql statements. Having learnt how to use breakpoints they can then analyse easily how each part works and see it through to fruition.

Once they have done this they may feel confident enough to program vba loops, parse things, use split functions and the like.

 

[Galaxiom]

Not arguing here, personally I would use your approach because I can. However for a beginner there are easier options which also help the learning curve.

You have not demonstrated an easier option. In fact you have not demonstrated a workable option at all, just a sketchy notion that you would probably struggle to implement yourself.

If you copy the data to a temporary table you can then develop all the queries you like to amend the data and copy it to your correct tables which can also already contain all the correct relationships.

The tables can certainly have the relationships already defined but records of the header (C100), items (C170) and presumably the footer (C190) have no relationship because the keys are not in the data. The relationship between the lines is entirely due to their position. To relate the records in the endpoint tables we must add the key.

As a rough example you can have a table with types such as C100, C170, C190 etc. related to a main data table. You can use an update query to change C100 to 1 in your temporary table. After importing to your main data table the 1 will relate to the types table and you will know it is a C100.

OK you have separated the records into three tables. There are still no keys to tie the records together into a single complete invoice. What do you gain by abstracting C100 to 1?

Rough and ready approach but on completion a beginner will understand tables and queries. They can then work out how to convert the queries to simple vba with a whole series of docmd.runsql statements.

The series of queries to update the key values would be well beyond a beginner. If they ever managed to do it they would certainly have developed a good understanding of queries.

Have you actually considered what it would involve? Could you even write such query? Find the first unallocated header record (WHERE ID = "C100") in the Temp table and the Min(TempID) by joining it to the Header table and exclude already allocated headers. Then by a similar process, find the corresponding Footer record. Update the Header and Footer records to a new unused value.

Next return all the records that lie between the header and footer temp table IDs and update them with the same value.

Moreover the whole process is flawed because it relies on an incrementing Autonumber on the original import. A wise developer does not rely on the order of an autonumber.

Does that sound simple for a beginner? And that is aside from dealing with the import itself, the clearing of the temp table and issue of bloating due to the temp table.

If you would do it like I suggested, then why didn't you elaborate my brief outline? Instead of helping the OP I have had to provide a detailed explanation of why pursuing your ill considered and ardently persisted advice would be a big mistake.

 

[Uncle Gizmo]

cross posted at http://www.utteraccess.com/forum/index.php?showtopic=2037831

Definitely s Cross Post!

Sent from my SM-G925F using Tapatalk

 

[Tieval]

Galaxiom,

As previously stated I would do it your way now, however I did a similar thing to what I have suggested when I was a novice and it could all be done without great difficulty and there were no issues with bloat etc..

Perhaps we should leave it there

 

[Crônico]

Galaxiom, thank you for your advices. I understand the main idea.
Tieval, thank you too for your attention but I have to say that I did not understand your approach.
For all Moderators, I realy posted it in another forum. Is it forbidden? If so please forgive me. "We must search for more than one medical opinion" .

 

[Uncle Gizmo]

For all Moderators, I realy posted it in another forum. Is it forbidden? If so please forgive me. "We must search for more than one medical opinion" .

Posting in many different forums may well get you ignored by some people, so it is possibly counterproductive.

From the point of view of moderating a forum; there isn't a problem with you making posts in different forums as long as you link the posts together in/across all the forums, AND make it clear that the question might receive an answer in another forum.

So the short answer is yes you can do it, BUT you mustn't waste people's time by leaving open the possibility that they might answer your question twice or you might receive a valid answer to your question in one forum from one person and again in another forum from another person..

In other words you have taken up a person's valuable freely given time by trying to save yourself a bit of time...

 

[Galaxiom]

Galaxiom, thank you for your advices. I understand the main idea.

Let us know if you need more details.

Tieval, thank you too for your attention but I have to say that I did not understand your approach.

I not surprised. Tieval clearly doesn't understand it either, even after I explained what would be involved.