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

Hi Branislav

You said:

"I see, but this will just add few lines of the code - to read ID of C100 when saved and write it as ID in C170. Of course, ID field must exists in both tables in that case."

My question:Do you have same codes to do anything similar to this?

Answer 2

Branislav

The C100 line has its ID but it is too large and will slow down the processing. That is why I thought at creating an ID like a Long.

Answer 3

Unfortunately no, but I've worked many times on similar data conversion. I think this will take around an hour to do it, maybe two, but I am working on something else now, I have no time to do it.

Answer 4

I am wondering is it doable without using a recordset - he has to know the ID of C100 and use that ID on C170?

(or to manually generate a number - ID - on the fly and use such number like max() +1)?

Sure it is. By using SQL statements to append each record. As I said earlier, you have to parse each line into a set of variables, either using named variables (which I would opt for as the code will be easier to follow though it would be more lines) or using an array.

And yes its possible to use an AutoNumber after you execute the SQL statement Appending the 100 record you do something like:

lngPK = DMAX("[C100ID]","tablename")

then use lngPK in your Append statements for the child records.

Answer 5

I get used to avoiding domain aggregate functions cause they can work really slow with big tables. :)

But working with recordset will be slow as well, maybe even slower. SELECT TOP 1 ORDER BY... may be the solution to get the last ID fast. Actually, all depends on how many data are there. I'll also try with, as Alogon said - he has ID for C100 but it is too long, given ID, no matter how big it is.

So, we gave enough ideas, now someone needs to put this together and see which works best in this case.

Regards,

Branislav Mihaljev