Need help reading text file.

[FatMan]

Hi all:
I need help in processing a text file that will be used to populate a
database. The text file is a delimited file (“;†as the separation
character) but is unlike any I have seen or used before. It if makes a
difference the text file is being created by a piece of equipment used to
grade fruit.

The text file looks like:

188;20071122000001;19;37;290077;
2300;16:15:07;07:29:16;15:14:08;0;1;073260610001;1 ====== Filling data
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15 === Content data
20071121-1-004;120;816,1;2;7;290077; 2300 ============ Origin data

199;20071122000002;25;37;290080;
2221;16:23:41;07:50:21;15:26:39;0;1;073260610002;1
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15
20071121-1-004;120;816,1;2;7;290080; 2221

179;20071122000003;20;35;290117;
1929;18:18:11;07:50:31;13:32:20;0;1;073260610003;1
COX;70-75;Class I;;Red 40-100%;WCOXCAPBGP70E;15
20071121-1-004;120;816,1;2;7;290117; 1929

Please note:
Filling data appears in the text file on one line (no word wrapping).
Origin data may be more than one line but each line contains the same number
of pieces of data.

My problem is I have only seen/worked with text files where there is the
same number of pieces of data for each record.

My ultimate goal would be to read/loop through the text file and save each
part (filling, content and origin data) in its own table with a “fillingIDâ€
saved as part of the content and origin records.

Can anyone tell me if this can be done in Access 2000 and if so how would
you go about it?

Any and all help is greatly appreciated,
FatMan

 

[pietlinden]

Hi all:
I need help in processing a text file that will be used to populate a
database.  The text file is a delimited file (“;” as the separation
character) but is unlike any I have seen or used before.  It if makes a
difference the text file is being created by a piece of equipment used to
grade fruit.

The text file looks like:

188;20071122000001;19;37;290077;  
2300;16:15:07;07:29:16;15:14:08;0;1;073260610001;1  ====== Filling data
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15 === Content data
20071121-1-004;120;816,1;2;7;290077;  2300  ============ Origin data

199;20071122000002;25;37;290080;  
2221;16:23:41;07:50:21;15:26:39;0;1;073260610002;1
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15
20071121-1-004;120;816,1;2;7;290080;  2221

179;20071122000003;20;35;290117;  
1929;18:18:11;07:50:31;13:32:20;0;1;073260610003;1
COX;70-75;Class I;;Red 40-100%;WCOXCAPBGP70E;15
20071121-1-004;120;816,1;2;7;290117;  1929

Please note:
Filling data appears in the text file on one line (no word wrapping).
Origin data may be more than one line but each line contains the same number
of pieces of data.

My problem is I have only seen/worked with text files where there is the
same number of pieces of data for each record.

My ultimate goal would be to read/loop through the text file and save each
part (filling, content and origin data) in its own table with a “fillingID”
saved as part of the content and origin records.

Can anyone tell me if this can be done in Access 2000 and if so how would
you go about it?

Any and all help is greatly appreciated,
FatMan

You could probably use Chuck Grimsby's class to read a text file and
then you could process the file in code and write the records where
they belong. Not trivial, but not terribly difficult if you know the
rules and what you are doing.

 

[pietlinden]

Hi all:
I need help in processing a text file that will be used to populate a
database.  The text file is a delimited file (“;” as the separation
character) but is unlike any I have seen or used before.  It if makes a
difference the text file is being created by a piece of equipment used to
grade fruit.

The text file looks like:

188;20071122000001;19;37;290077;  
2300;16:15:07;07:29:16;15:14:08;0;1;073260610001;1  ====== Filling data
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15 === Content data
20071121-1-004;120;816,1;2;7;290077;  2300  ============ Origin data

199;20071122000002;25;37;290080;  
2221;16:23:41;07:50:21;15:26:39;0;1;073260610002;1
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15
20071121-1-004;120;816,1;2;7;290080;  2221

179;20071122000003;20;35;290117;  
1929;18:18:11;07:50:31;13:32:20;0;1;073260610003;1
COX;70-75;Class I;;Red 40-100%;WCOXCAPBGP70E;15
20071121-1-004;120;816,1;2;7;290117;  1929

Please note:
Filling data appears in the text file on one line (no word wrapping).
Origin data may be more than one line but each line contains the same number
of pieces of data.

My problem is I have only seen/worked with text files where there is the
same number of pieces of data for each record.

My ultimate goal would be to read/loop through the text file and save each
part (filling, content and origin data) in its own table with a “fillingID”
saved as part of the content and origin records.

Can anyone tell me if this can be done in Access 2000 and if so how would
you go about it?

Any and all help is greatly appreciated,
FatMan

post a few more examples and an explanation of what the different
delimiters mean.
Also, how do these examples map to your data? It looks like you would
need to read line by line and then parse those results using Split.
But then what do you do with those results?

 

[FatMan]

Pietlinden:
Thanks for the reply.

I cannot post any more examples because the equipment has not been fully
installed and parts of it are still between here and Europe.

The text file should look like the example posted. The only difference
would be that there might be any number of records (sample only contained 3)
and that there might be any number of lines per record for the “origin dataâ€.

My idea is to create three tables (Filling, Content and Origin) and have the
data from the text file appended to the appropriate table. The content and
origin table would have a field (fillingID) that would link them back to the
filling data.

What I need to figure out is how do I accomplish reading the file and
separating the data and placing it in the correct table.

The segments (I would create a field in the appropriate table for each
segment) of the text file are as follows:

Filling data (one line per record):
Seg01 – Temp bin number
Seg02 – Unique bin number
Seg03 – Physical outlet
Seg04 – Main Group number
Seg05 – Bin weight (in grams)
Seg06 – Number of pieces
Seg07 – Start time filling
Seg08 – Stop time filling
Seg09 – Filling time
Seg10 – Number of days
Seg11 – Full batch message
Seg12 – Unique bin number according to format

Content data (one line per record):
Seg01 – Variety
Seg02 – Size
Seg03 – Quality
Seg04 – Color
Seg05 – Blush
Seg06 – Length
Seg07 – Remark
Seg08 – Barcode

Origin data (one or more lines per record):
Seg01 – Lot number
Seg02 – Order number
Seg03 – Client description
Seg04 – Sorting key
Seg05 – Harvest year
Seg06 – Weight in grams in this bin from the supplier
Seg07 – Number of pieces in this bin from this supplier
Seg08 – Infeed number

I have a basic knowledge of VBA and normally can work my way through things
if pointed in the right direction….although some days I believe I need more
pointing than others.

Any help you can provide would be and is greatly appreciated.

Thanks,
FatMan

 

[pietlinden]

Pietlinden:
Thanks for the reply.

I cannot post any more examples because the equipment has not been fully
installed and parts of it are still between here and Europe.

The text file should look like the example posted.  The only difference
would be that there might be any number of records (sample only contained3)
and that there might be any number of lines per record for the “origin data”.

My idea is to create three tables (Filling, Content and Origin) and have the
data from the text file appended to the appropriate table.  The contentand
origin table would have a field (fillingID) that would link them back to the
filling data.

What I need to figure out is how do I accomplish reading the file and
separating the data and placing it in the correct table.

Fatman,

Ok, I can do this, I understand it now. That explanation of what
each bit means is significant. The basic plan is this... (so someone
else can comment)...

1. get Chuck Grimsby's code to read a text file (it's a class module
that exposes a couple of extra properties.)
2. use it to read the text file.
3. Read each line and use SPLIT to separate the values separated by
semi-colons.
4. loop through the array the SPLIT function returns and process each
piece individually and write it to a table (eventually).

I'll work on it a little later on and post it back here.

Pieter

 

[FatMan]

Pieter:
Any help/advice you can help is greatly appreciated.

Looking forward to you next post.

Thanks,
FatMan