how do i import a text file with line wraps into a table

[Guest]

I've pasted a record from my text file below (which really is only two lines,
but wrapped more here). Is there anyway I can get text data like this in one
record in an access table, omitting the ----------------- lines?

------------------------------------------------------------------------------------------------------------------------
ACT ACT SVC
REQ# DATE TIME DATE USER BILL CODE ALT CODE TYPE
ACTION ORDER $ CHARGE
------------------------------------------------------------------------------------------------------------------------
GRANT,JACQUELIN MARIE C000010001 41/F <REG REF 02/06> (S093637) LAB.CHMS
Pinkston,John R. M
01458238 02/06 0705 02/06 CHMS.CDG 3001435
NEW BIOSCREEN 9.35

==========

9.35

 

[Roger Carlson]

Whenever I need to import a text file that is not in a format for the
TransferText method, I always use VB low-level I/O. There's two things I
do:
1) Import the file directly
2) Clean up the text file, then use the TransferText method.

Note: You can find both of the following on my website
(www.rogersaccesslibrary.com) in a sample called: "ImportLineInput.mdb"

1) Import: I'll use code that looks like this:
-------------------------------
Function ImportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Table1"
DoCmd.SetWarnings True

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\LineInput.txt" For Input As #1

' Create a dynaset-type Recordset object based on Table1.
Set rst = dbs.OpenRecordset("Table1")

Do While Not EOF(1)
Line Input #1, MyString
'Add a new Record
'the next line will skip any line that starts with "--"
If left(MyString,2) <> "--" Then
rst.AddNew
' code goes here to parse the string. Something like this:
'Note: YOUR code will vary to meet the needs of your file!
rst!DayOfWeek = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!Month = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!URL = MyString
rst.Update
End If
Loop
' Close text file.
MsgBox "Done!"
Close #1
rst.Close
Set dbs = Nothing
End Function
------------------------------------

2) Clean the text file:
Sub CleanFile(DirtyFile As String, CleanFile As String, Offset)
Dim dbs As Database
Dim Directory As String
Dim MyString As String
Dim i As Integer

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\" & DirtyFile For Input As #1
Open Directory & "\" & CleanFile For Output As #2

'ignore specified number of header lines
For i = 1 To Offset
Line Input #1, MyString
Next

Do While Not EOF(1)
Line Input #1, MyString
If left(MyString,2) <> "--" Then
'Output the line Record
Print #2, MyString
End If
Loop
' Close text file.
MsgBox "Done!"
Close #1
Close #2
Set dbs = Nothing
End Sub
-------------------------------


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

After re-reading the above, I see that it is confusing. I should have said
that I do ONE OR THE OTHER of the following:
1) Import the file directly
2) Clean up the text file, then use the TransferText method.

With (1) you import your text file directly into your table with code. With
(2), you run your text file through the clean-up routine, which leaves it in
a format that the TransferText method can deal with. If you use one method,
you don't need the other.

Sorry for the confusing post.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Sorry to be ignorant, but what is VB low level I/O? I don't know VB or VBA,
so is the code you provided to be written in a module in access?

 

[Roger Carlson]

Low-level I/O just refers to the very old way that BASIC accessed data files
back in the "Olden Days". It is low-level, because VB has much more
advanced ways of accessing data -- a database for instance. But for
importing text files, it still works very well.

If you're not familiar with VB, probably the easiest one to implement is the
file clean up. Basically, it just reads a line in the file, then writes it
to another file. The clean up comes in based on which lines you decide NOT
to write to your new file. So in your case, you would read every line, but
only write those lines that are not a line of dashes.

If you ALWAYS use the same file names, I can simplify the code event
further. (Note: Yes, put this code in a general Module.)

Sub CleanFile()
Dim Directory As String
Dim MyString As String

Directory = (Mid(CurrentDb.Name, 1, Len(CurrentDb.Name) _
- Len(Dir(CurrentDb.Name))))

Open Directory & "\" & "DirtyLineInput.txt" For Input As #1
Open Directory & "\" & "LineInput.txt" For Output As #2

Do While Not EOF(1)
Line Input #1, MyString
If left(MyString,2) <> "--" Then
'Output the line Record
Print #2, MyString
End If
Loop
' Close text file.
MsgBox "Done!"
Close #1
Close #2
Set dbs = Nothing
End Sub

NOTES: Replace your initial file for "DirtyLineInput.txt" and your new clean
file name for "LineInput.txt". The way this code is set up, the DIRTY file
must be in the same directory as the database application. Your new, clean
file does not have to exist first. The code will create it in the same
directory.

To run the code, just open the code window, place your cursor anywhere in
the code and then click the Run button on the toolbar. After you run the
code, you should have a new, clean file that does not have the lines of
dashes. Then you import it with the TransferText method (or simply using
the Import Wizard.)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L