Need help processing text file.
FatMan
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
roger
Its a very complex set of loops opening each line, cutting it up, building
the output record and repeat.
Its alot like a thing a wrote that reads Poker Hand history text files, and
builds a lovely database out of them. (I just looked at that code but its way
to complex to explain.)
I could do it for you though.
My usual rate is $140 an hour but I could knock this out for $100.
You can send me example files. I 'll write an importer and send you back
imported data. If you like it, pay me and I'll send you the code.
That's truely the best and fastest answer I can give you.
let me know
pietl...@hotmail.com
Depends on how good you are at VBA...
Use Chuck Grimsby's class to read text files. (www.mvps.org/access) in
the Modules section.
Then you would need to add logic to determine what to do with the line
of code.
Once you cleaned/parsed the line, you could write it to a table using
a recordset.
But it's hard to find a real pattern with so few examples and no
explanation of what any of it means. or what you want to do with
specific elements.
FatMan
Please do not take this the wrong way but I am a little nervous of
contracting someone to do some programing for me that I know nothing about.
If I pay you for the code based on your imported data before I get a chance
to test your code how do I know the code actually works. Like I said nothing
against you but I just don't know you or your work. Maybe if you could
provide references from some of the people you have created programs for or
maybe you could create the code send it to me and allow me to test it then
allow me to send you payment. I know sending me the code before payment is
asking you to trust me but you can check us out at www.scotiangold.com. When
you say you can knock it out for $100 is that a flat rate or a per hour rate?
If a per hour rate...how many hours are we talking?
If you are interested please let me know.
Thanks,
FatMan
pietl...@hotmail.com
> I need help in processing atextfile that will be used to populate a
> database. Thetextfile is a delimited file (“;” as the separation
> character) but is unlike any I have seen or used before. It if makes a
> grade fruit.
>
> Thetextfile 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:
> Origin data may be more than one line but each line contains the same number
> of pieces of data.
>
> same number of pieces of data for each record.
>
> 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
What is the first line? Something you don't need?
pietl...@hotmail.com
> Wow, I've been trying to figure out how to explain to you this for a while.
>
> Its a very complex set of loops opening each line, cutting it up, building
> the output record and repeat.
>
> Its alot like a thing a wrote that reads Poker Hand history text files, and
> builds a lovely database out of them. (I just looked at that code but its way
> to complex to explain.)
>
> I could do it for you though.
>
> My usual rate is $140 an hour but I could knock this out for $100.
>
> You can send me example files. I 'll write an importer and send you back
> imported data. If you like it, pay me and I'll send you the code.
>
> That's truely the best and fastest answer I can give you.
>
Complex? Hardly. Open the file (look up Freefile in the help,
there's an example).
Then read a line at a time, and use Split() to break the individual
items out.
Then use an append-only recordset to write the various parts to a
table. Maybe the hardest part is figuring out what to do with all the
pieces.
$100?! For free help, that's a lot of money!
pietl...@hotmail.com
Public Sub CallProcessTextFile(ByVal strFile As String)
'Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Dim intLineNo As Integer
Dim intItemNo As Integer
Dim varValues As Variant
'Set db = CurrentDb
'Set rs = db.OpenRecordset("TableName", dbOpenTable)
Open strFile For Input As #1
'Read a single line from an open sequential file and assign it to
a String variable.
Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)
If Len(sTrimmed) = 0 Then
intLineNo = 0
Else
intLineNo = intLineNo + 1
End If
Select Case intLineNo
Case 1
'skip it?
Case Else
sTrimmed = Replace(sTrimmed, ";", ":")
varValues = Split(sTrimmed, ":")
'Debug.Print intLineNo, sTrimmed
For intItemNo = LBound(varValues) To UBound(varValues)
Debug.Print intLineNo, intItemNo,
Trim(varValues(intItemNo))
Next intItemNo
End Select
Loop
Close #1
End Sub
Here's the output...
callprocesstextfile "C:\testdata.txt"
2 0 2300
2 1 16
2 2 15
2 3 07
2 4 07
2 5 29
2 6 16
2 7 15
2 8 14
2 9 08
2 10 0
2 11 1
2 12 073260610001
2 13 1 ====== Filling data
3 0 COX
3 1 65-70
3 2 Class I
3 3
3 4 Red 40-100%
3 5 WCOXCAPBGP65E
3 6 15 === Content data
4 0 20071121-1-004
4 1 120
4 2 816,1
4 3 2
4 4 7
4 5 290077
4 6 2300 ============ Origin data
2 0 2221
2 1 16
2 2 23
2 3 41
2 4 07
2 5 50
2 6 21
2 7 15
2 8 26
2 9 39
2 10 0
2 11 1
2 12 073260610002
2 13 1
3 0 COX
3 1 65-70
3 2 Class I
3 3
3 4 Red 40-100%
3 5 WCOXCAPBGP65E
3 6 15
4 0 20071121-1-004
4 1 120
4 2 816,1
4 3 2
4 4 7
4 5 290080
4 6 2221
2 0 1929
2 1 18
2 2 18
2 3 11
2 4 07
2 5 50
2 6 31
2 7 13
2 8 32
2 9 20
2 10 0
2 11 1
2 12 073260610003
2 13 1
3 0 COX
3 1 70-75
3 2 Class I
3 3
3 4 Red 40-100%
3 5 WCOXCAPBGP70E
3 6 15
4 0 20071121-1-004
4 1 120
4 2 816,1
4 3 2
4 4 7
4 5 290117
4 6 1929
the problem lines are the ones like (4,2) where there are two values
in the split data. What do I do with that?
Also, how are these tables related? They don't appear to be related
at all. Once I know that, I know what values to store first and then
how to create the child records.
Yeah, complex if you have the IQ of an eraser.
FatMan
The output file is very close to what I am looking for.
The actual text file provided to my so far to date from our supplier looks
like and I am not sure why the "path" is included as part of the text file:
O:\Greefa\Results\Trace\20071122.txt
188;20071122000001;19;37;290077;
2300;16:15:07;07:29:16;15:14:08;0;1;073260610001;1
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15
20071121-1-004;120;816,1;2;7;290077; 2300
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
The thing to keep in mind is that the first two lines of each block of data
displayed above is one continuous line in the text file and not wrapped like
it appears here in this post. In other words the second line that appears in
the first block of data starting with "2300" would appear right after the
"';" of the first line (188;20071122000001;19;37;290077;). The same is true
for each block of data.
In my early post the "============ Filling data", "============ Origin data"
and "============ Content data" was just there to help show to which table
the data would be appended to.
Regarding the problem lines (4,2): I am not sure why there is a "," used in
the text file as it is to be a delimited text file with ";" being the
separator. I have asked the supplier to provide further sample data and will
question them on this.
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. The Filling table would be the parent record with the content
and origin records being the child records....I hope I said that right.
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) to be added to the "filling table" -
table will have an autonumber field (FillingID):
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) table will have a field to store the
"fillingID" from the filling table:
Seg01 – Variety
Seg02 – Size
Seg03 – Quality
Seg04 – Color
Seg05 – Blush
Seg06 – Length
Seg07 – Remark
Seg08 – Barcode
Origin data (one or more lines per record) table will have a field to store
the "fillingID" from the filling table:
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
Please know how much I appreciate all your help in this matter. If I could
find some way to pass a cold beverage of your choice through computer and
Internet I would!!!
Thanks,
FatMan
FatMan
The output file is very close to what I am looking for.
The actual text file provided to my so far to date from our supplier looks
like and I am not sure why the "path" is included as part of the text file:
O:\Greefa\Results\Trace\20071122.txt
188;20071122000001;19;37;290077;
2300;16:15:07;07:29:16;15:14:08;0;1;073260610001;1
COX;65-70;Class I;;Red 40-100%;WCOXCAPBGP65E;15
20071121-1-004;120;816,1;2;7;290077; 2300
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
Thanks,
FatMan