Importing through code based on string value

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello all.... We have a text file that gets dumped to our computer on a
daily basis with information in regards to our downtime/uptime etc. The way
the data comes in we can't change and it was first created just to be
printed off well now they want the information stored in the computer and
because of it's format (as in not comma delimted/tab etc.....) The only way
I can think of being able to grab the information is by doing a string
search or something like that within the file itself.... Example search for
the string "START" and then grab the next 15 characters and cut out the
space that comes behind it. All the importing I find for example code is
based on the text file having , | or tabs. I have searched an so far found
nothing. I want to search for multiple things in the text file assign those
to a variable and then import the values into a table.... Any examples
would be great. Below I provided just a partial look at what the text file
looks like the whole thing is 5 pages long. Many TIA



S********** Company NAME (Blah) (LEFT)
CORPORATION LOG STATISTICS (8-14') 16:31 MON
07/14/03
PRODUCTION PERIOD:
START: 06:00 MON 07/14/03
END: 16:30 MON 07/14/03
LOT #: 100

|------------------------- LENGTH -------------------------|
DIAM 8.0 8.5 9.0 9.5 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0
 
Hi Brian,

One way to do this is with commercial software designed for the purpose,
such as Monarch
http://www.datawatch.com/dataconversionsoftware/personal_edition.htm.
You set up a sort of template showing where the fields you're interested
in are, and then the software pulls out the data for you.

Otherwise, the general approach is to write code that reads the file a
line at a time and parses each line according to its contents and
context (i.e. the preceding lines), then puts the extracted data into
records in your tables.

Depending on how regularly and consistently your file is structured this
can be fairly simple or very difficult. For example, it's simple to
write VBA code that can identify a line that starts with " START: "
and then take the "06:00 MON 07/14/03" and convert it to "#07/14/2006
06:00#" ready to include in an SQL append query. But it's much harder
(though not impossible) if one time it says

START: 06:00 MON 07/14/03
and the next time it's something like
START 6:00 AM 07/14/03

Which approach is best depends on the balance between your budget, your
time and your programming skills<g>. Post back here if you decide to
roll your own.


Hello all.... We have a text file that gets dumped to our computer on a
daily basis with information in regards to our downtime/uptime etc. The way
the data comes in we can't change and it was first created just to be
printed off well now they want the information stored in the computer and
because of it's format (as in not comma delimted/tab etc.....) The only way
I can think of being able to grab the information is by doing a string
search or something like that within the file itself.... Example search for
the string "START" and then grab the next 15 characters and cut out the
space that comes behind it. All the importing I find for example code is
based on the text file having , | or tabs. I have searched an so far found
nothing. I want to search for multiple things in the text file assign those
to a variable and then import the values into a table.... Any examples
would be great. Below I provided just a partial look at what the text file
looks like the whole thing is 5 pages long. Many TIA



S********** Company NAME (Blah) (LEFT)
CORPORATION LOG STATISTICS (8-14') 16:31 MON
07/14/03
PRODUCTION PERIOD:
START: 06:00 MON 07/14/03
END: 16:30 MON 07/14/03
LOT #: 100

|------------------------- LENGTH -------------------------|
DIAM 8.0 8.5 9.0 9.5 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Well here is what we have so far........................

Sub Testing()
Dim DataLine, LOGS, MINSEC
Open "c:\TESTFILE.TXT" For Input As #1
Open "c:\LOGS.TXT" For Output As #2
Open "c:\MINSEC.TXT" For Output As #3

Do While Not EOF(1)
Line Input #1, DataLine
LOGS = Left(DataLine, 6)
MINSEC = Left(DataLine, 6)
If LOGS = "LOADED" Or LOGS = "SCANNE" Or LOGS = "BD.FT." Or LOGS =
"LINES " Then
Write #2, DataLine
Debug.Print DataLine
ElseIf MINSEC = "PROD. " Or MINSEC = "DOWN " Or MINSEC = "NON-PR" Then
Write #3, DataLine
Debug.Print DataLine
End If
Loop
Close #1
Close #2
Close #3
End Sub


What I don't like is that it outputs into the text files like this
"LOADED 16 16 35 17 25 30 13 24 24 33 11
244" as one big string file. Is there any way that I can code in the spaces
to be recognized and then import into my table or am I going about this the
wrong way and should be importing from the get-go with code.. Thanx


John Nurick said:
Hi Brian,

One way to do this is with commercial software designed for the purpose,
such as Monarch
http://www.datawatch.com/dataconversionsoftware/personal_edition.htm.
You set up a sort of template showing where the fields you're interested
in are, and then the software pulls out the data for you.

Otherwise, the general approach is to write code that reads the file a
line at a time and parses each line according to its contents and
context (i.e. the preceding lines), then puts the extracted data into
records in your tables.

Depending on how regularly and consistently your file is structured this
can be fairly simple or very difficult. For example, it's simple to
write VBA code that can identify a line that starts with " START: "
and then take the "06:00 MON 07/14/03" and convert it to "#07/14/2006
06:00#" ready to include in an SQL append query. But it's much harder
(though not impossible) if one time it says

START: 06:00 MON 07/14/03
and the next time it's something like
START 6:00 AM 07/14/03

Which approach is best depends on the balance between your budget, your
time and your programming skills<g>. Post back here if you decide to
roll your own.


Hello all.... We have a text file that gets dumped to our computer on a
daily basis with information in regards to our downtime/uptime etc. The way
the data comes in we can't change and it was first created just to be
printed off well now they want the information stored in the computer and
because of it's format (as in not comma delimted/tab etc.....) The only way
I can think of being able to grab the information is by doing a string
search or something like that within the file itself.... Example search for
the string "START" and then grab the next 15 characters and cut out the
space that comes behind it. All the importing I find for example code is
based on the text file having , | or tabs. I have searched an so far found
nothing. I want to search for multiple things in the text file assign those
to a variable and then import the values into a table.... Any examples
would be great. Below I provided just a partial look at what the text file
looks like the whole thing is 5 pages long. Many TIA



S********** Company NAME (Blah) (LEFT)
CORPORATION LOG STATISTICS (8-14') 16:31 MON
07/14/03
PRODUCTION PERIOD:
START: 06:00 MON 07/14/03
END: 16:30 MON 07/14/03
LOT #: 100

|------------------------- LENGTH -------------------------|
DIAM 8.0 8.5 9.0 9.5 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
There's always more than one way of skinning this kind of cat. It's
certainly possible to put the data directly into your tables: instead of
opening your two output files and writing to them, you can write VBA
code to parse the line and build and execute SQL statements for
single-record append queries to put the data into the relevant table.

The single-record append syntax is
INSERT INTO TableName (Field1, Field2, ...)
VALUES (Value1, Value2, ...);

So you might find yourself doing something like this air code

Dim strXXX As String, strYYY As String
Dim strSQL As String
Dim dbD as DAO.Database

Set dbD = CurrentDB()
....
If LOGS = "xxx" Then
strXXX = Mid(DataLine, 30, 25)
strYYY = Right(DataLine, 6)
strSQL = "INSERT INTO tblZZZ " _
& "(FieldXXX, FieldYYY) VALUES (""" _
& strXXX & """, """ & strYYY & """);"
dbD.Execute strSQL, dbFailOnError
End If
....

Set dbD = Nothing



For lines like this
LOADED 16 16 5 17 25 30 13 24 24 33 11 244
I guess you want to convert each item on the line into a record in a
table. If the spacing is regular (e.g. 8 characters per column) and
there are always the same number of items it's fairly easy along these
lines (air code)

lngItem1 = Val(Mid(DataLine, 8, 8)
lngItem2 = Val(Mid(DataLine, 16, 8)
...






Well here is what we have so far........................

Sub Testing()
Dim DataLine, LOGS, MINSEC
Open "c:\TESTFILE.TXT" For Input As #1
Open "c:\LOGS.TXT" For Output As #2
Open "c:\MINSEC.TXT" For Output As #3

Do While Not EOF(1)
Line Input #1, DataLine
LOGS = Left(DataLine, 6)
MINSEC = Left(DataLine, 6)
Click to expand...

Here you're giving LOGS and MINSEC exactly the same value. Why use two
variables?
If LOGS = "LOADED" Or LOGS = "SCANNE" Or LOGS = "BD.FT." Or LOGS =
"LINES " Then
Write #2, DataLine
Debug.Print DataLine
ElseIf MINSEC = "PROD. " Or MINSEC = "DOWN " Or MINSEC = "NON-PR" Then
Write #3, DataLine
Debug.Print DataLine
End If
Loop
Close #1
Close #2
Close #3
End Sub


What I don't like is that it outputs into the text files like this
"LOADED 16 16 35 17 25 30 13 24 24 33 11
244" as one big string file. Is there any way that I can code in the spaces
to be recognized and then import into my table or am I going about this the
wrong way and should be importing from the get-go with code.. Thanx


John Nurick said:
Hi Brian,

One way to do this is with commercial software designed for the purpose,
such as Monarch
http://www.datawatch.com/dataconversionsoftware/personal_edition.htm.
You set up a sort of template showing where the fields you're interested
in are, and then the software pulls out the data for you.

Otherwise, the general approach is to write code that reads the file a
line at a time and parses each line according to its contents and
context (i.e. the preceding lines), then puts the extracted data into
records in your tables.

Depending on how regularly and consistently your file is structured this
can be fairly simple or very difficult. For example, it's simple to
write VBA code that can identify a line that starts with " START: "
and then take the "06:00 MON 07/14/03" and convert it to "#07/14/2006
06:00#" ready to include in an SQL append query. But it's much harder
(though not impossible) if one time it says

START: 06:00 MON 07/14/03
and the next time it's something like
START 6:00 AM 07/14/03

Which approach is best depends on the balance between your budget, your
time and your programming skills<g>. Post back here if you decide to
roll your own.


Hello all.... We have a text file that gets dumped to our computer on a
daily basis with information in regards to our downtime/uptime etc. The way
the data comes in we can't change and it was first created just to be
printed off well now they want the information stored in the computer and
because of it's format (as in not comma delimted/tab etc.....) The only way
I can think of being able to grab the information is by doing a string
search or something like that within the file itself.... Example search for
the string "START" and then grab the next 15 characters and cut out the
space that comes behind it. All the importing I find for example code is
based on the text file having , | or tabs. I have searched an so far found
nothing. I want to search for multiple things in the text file assign those
to a variable and then import the values into a table.... Any examples
would be great. Below I provided just a partial look at what the text file
looks like the whole thing is 5 pages long. Many TIA



S********** Company NAME (Blah) (LEFT)
CORPORATION LOG STATISTICS (8-14') 16:31 MON
07/14/03
PRODUCTION PERIOD:
START: 06:00 MON 07/14/03
END: 16:30 MON 07/14/03
LOT #: 100

|------------------------- LENGTH -------------------------|
DIAM 8.0 8.5 9.0 9.5 10.0 10.5 11.0 11.5 12.0 12.5 13.0 13.5 14.0
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top