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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"Pam Deshazier, SRHS" <PamDeshazierSRHS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:4603DB31-34EF-440D-84D8-5102650493F4@xxxxxxxxxxxxxxxx
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


.

Relevant Pages