Text import 1

[NNNNN]

*****************
* *
* Description *
* *
*****************
First record line one
First record line two
= = = = = = = = = = =
Second record line one
Second record line two
= = = = = = = = = = =

Above is an example of a text file
I want to import the data but due to the layout of the text file its proving difficult

I don’t know if this can be done but I want to start the import from the first record
Ignoring the * and anything between the *

Another difficulty is that each record goes over 2 lines
And each record is separated with a line of = =

The text file is basically a log file containing many records, and if frequently appended to through another program. I do not have permission to change the text file in any way.

Can an import with a reasonable layout be achieved through Access?
I fear the answer is no but am an eternal optimist

Thanks

 

[psemianonymous]

You can spend a billion dollars on a program named Monarch that will do all this (and of course, more!) automagically. If you have a billion dollars, or ... okay, it's only $500.

http://www.hallogram.com/monarch/

But if you don't want to spend the money, you'll have to write your own VBA import() function which handles all these weird exceptions.


If possible, tell the programmer of whatever system is outputting all the data to please have a second output in more database-friendly comma-seperated values.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Kotaro24]

Definitely achievable. You code should look something like this:

Dim CHNL As Integer
Dim FirstLineRead as Boolean

'Read Input File, convert the data to an Access Table
CHNL = FreeFile
Open iPathAndFileName For Input As #CHNL
Line Input #CHNL, str
Do
str=trim(str)
if left(str,1)="*" then Goto Next_Line 'Skip Comments
if left(str,2)="==" then Goto Next_Line 'Skip dividers
If FirstLineRead then
'Do your second line's data manipulation here
FirstLineRead=False
Else
'Do your first line's data manipulation here
FirstLineRead=True
End If

Next_Line:
If eof(CHNL) Then Exit Do
Line Input #CHNL, str
Loop

Close #CHNL

 

[beetee]

A good initial approach.

You may also want to consider things like this in designing the program:

1) What if the keys are in the first line but not in the second line
2) what if the format is more like this:
[tt]
*****************
* *
* Description *
* *
*****************
First record line one
First record line two
First record line three
First record line four
...
= = = = = = = = = = =
Second record line one
Second record line two
Second record line three
Second record line four
...
= = = = = = = = = = =
[/tt]
3) what if the actual format has some suprises?

You may want to go with a state machine type approach, e.g.

Expecting a line of *'s: if it's a line of *'s, go to the next state, else error
Expecting a line of ='s: if it's a line of ='s, go to the next state, else error.
Expecting 0 or more instances of a correctly formatted first line
Expecting 'n' instances of a correctly formatted second line, where 'n' is the number of first lines read

This type of implementation lends itself nicely to using a Select Case CurrentState type of construct.

 

[NNNNN]

Thanks for your help guys
I went with the initial approach as I am not too hot on VBA
but will consider beetee's comments

I do have one Q though

how do I append record 2 to record 1 ?

here is the code for importing:

Private Sub Command0_Click()
Dim CHNL As Integer
Dim FirstLineRead As Boolean

Dim Str As String

Dim rs As DAO.Recordset
Dim sDir As Variant

iPathAndFileName = "c:\trash.txt"

CurrentDb.Execute "Delete tTempFiles.* from tTempFiles;"

Set rs = CurrentDb.OpenRecordset("tTempFiles&quot

'Read Input File, convert the data to an Access Table
CHNL = FreeFile
Open iPathAndFileName For Input As #CHNL
Line Input #CHNL, Str
Do
Str = Trim(Str)
If Left(Str, 1) = "*" Then GoTo Next_Line 'Skip Comments
If Left(Str, 1) = "=" Then GoTo Next_Line 'Skip dividers
If FirstLineRead Then
'Do your second line's data manipulation here
FirstLineRead = False
Else
'Do your first line's data manipulation here

End If
FirstLineRead = True
rs.AddNew
rs!TempFileName = Str
rs.Update
Next_Line:
If EOF(CHNL) Then Exit Do
Line Input #CHNL, Str
Loop
rs.Close
Set rs = Nothing
Close #CHNL

End Sub



Thanks again

 

[Kotaro24]

Assuming that there's always 2 lines, you would not perform an rs.Update after setting the first line's fields. Move the 'rs.Update' statement to just before the 'FirstLine=False' statement. (In other words, leave the new record un-updated until you've done a line input of the second line, and set the fields from the second line into your record).

 

[Kotaro24]

Oh, and you'd need a line that says:

rs!TempFileName = rs!TempFileName & Str

under the comment 'Do your second line's data manipulation here

 

[NNNNN]

THANK YOU