Importing a text file to a table

[AnthonyJ20]

Hello. I'm trying to use the script below to import a tab delimited text file to a table I have but I'm having a problem. The data appears garbled in the fields. Here is my script. I commented out all of the rs.field lines except for the first one. Below the script is an example of what the file I'm trying to import looks like. Can anyone tell what I'm doing wrong?

Import Script

Private Sub cmdReadit_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeletImport", acViewNormal
DoCmd.Close acQuery, "DeletImport"



Dim filnam As String, s As String, MyLine As String, MyLocation As Long, ch As String
Dim rs As DAO.Recordset, once As Boolean

filnam = "C:\ACCESS\JUNQUE\lbchrupld.txt"
Set rs = CurrentDb.OpenRecordset("lbchrupld")
once = False
If Not IsNull(Me.Text2) Then filnam = Me.Text2
Open filnam For Binary As #1
MyLine = ""
MyLocation = 0
Do While MyLocation < LOF(1)
ch = Input(99, #1)
If once Then
rs.AddNew
rs.Fields(1) = Mid(MyLine, 1, 1)
'rs.Fields(2) = Mid(MyLine, 3, 3)
'rs.Fields(3) = Mid(MyLine, 6, 2)
'rs.Fields(4) = Mid(MyLine, 8, 13)
'rs.Fields(5) = Mid(MyLine, 21, 5)
'rs.Fields(6) = Mid(MyLine, 26, 15)
'rs.Fields(7) = Mid(MyLine, 48, 6)
'rs.Fields(7) = "" & Mid(MyLine, 48, 2) & "/" & Mid(MyLine, 50, 2) & "/" & Mid(MyLine, 52, 2) & ""
'rs.Fields(8) = Mid(MyLine, 55, 5)
'rs.Fields(9) = Mid(MyLine, 62, 4)
'rs.Fields(10) = Mid(MyLine, 66, 5)
'rs.Fields(11) = Mid(MyLine, 90, 8)
rs.Update
End If

once = True
MyLine = ""
MyLine = MyLine & ch
MyLocation = Loc(1)
Loop

MsgBox " File has been successfully imported! "
Close #1 ' Close file.
rs.Close
Set rs = Nothing

DoCmd.OpenForm "frmlbchrupldNew", acNormal

End Sub


Text File


"CH" 1 "FUMC" "" 08/23/2005 813 813 221 221 101 72 0 0 0 0 8 0 11 0 0 0 0 0 0 1734164.16
"CH" 3 "World Com" "" 08/23/2005 190 190 31 31 12 13 206 3 0 0 7 7 96 0 0 0 0 0 0 34514.00

 

[gol4]

just on a quick glance
Open filnam For Binary As #1 ??
since it is a text file you probably should use
open filname for input as #1

 

[Remou]

From your text file example, this:
rs.Fields(1) = Mid(MyLine, 1, 1)
is a quote mark " ...
Also, TransferText works quite well for most cases.

 

[AnthonyJ20]

Thanks for the help guys. You got my mind working again. I opted to use a script that I have in an ASP page. All I needed to do was change one or two lines and TA DA!!! The script is hand in that it allows a person to pick the file they want and then import it. Here is the complete script for importing into access.

Private Sub cmdReadit_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeletImport", acViewNormal
DoCmd.Close acQuery, "DeletImport"

'Declare variables
Dim objFile
Dim objText
Dim strTextLine
Dim sql, sql2
Dim CheckDated
Dim CheckDated2
Dim MyTest

'Create FileSystem Object
Set objFile = CreateObject("Scripting.FileSystemObject")

'Set the objStream variable to open the text file for reading.
'The OpenTextFile method for objFile (the FileSystem Object) has the following
'attributes: FileName, IOMode, and Create.
'So it looks like this, objFile.OpenTextFile(FileName, IOMode, Create)
Dim MyFile, MyFileNew
MyFile = Me.Text2
Set objText = objFile.OpenTextFile(MyFile)

'---------------------------Make a connection to the db--------------------------------------------------------
Dim cmdDC, Recordset, Cnt, Rlog, Rlog2, Conn
Set Rlog = CurrentDb.OpenRecordset("tblCoreImport2")


Do While Not objText.AtEndOfStream
Dim MyDate, MyCustomer, MyChecksFull

'assign a line to strTextLine
strTextLine = objText.Readline
Data = Split(strTextLine, vbTab)

'-------------------------------------------------------
Rlog.AddNew
Rlog("fldUnknown") = Data(0) 'Site ID
Rlog("fldAccountName") = Data(1) 'Client ID
Rlog("CustomerName") = Data(2) 'Client Name
Rlog("fldDDAAccount") = Data(3) 'DDA Account *******New Field********
Rlog("fldDate") = Data(4) 'Date
Rlog("fldChecksFull") = Data(5) 'Good Checks Full
Rlog("fldStubsFull") = Data(6) 'Good Stubs Full
Rlog("fldChecksPartial") = Data(7) 'Good Checks Partial
Rlog("fldStubsPartial") = Data(8) 'Good Stubs Partial
Rlog("fldChecksMulti") = Data(9) 'Good Checks Multi
Rlog("fldStubsMulti") = Data(10) 'Good Stubs Multi
Rlog("fldChecksOnly") = Data(11) 'Check Only
Rlog("fldChecksOnlySuspense") = Data(12) 'Check Only Suspense *******New Field********
Rlog("fldCheckAndList") = Data(13) 'Check And List Stub
Rlog("fldCheckAndListChecks") = Data(14) 'Check and List Check *******New Field********
Rlog("fldOCRScanLineRejects") = Data(15) 'OCR Scanline Rejects
Rlog("fldMICRScanLineRejects") = Data(16) 'MICR Scanline Rejects *******New Field********
Rlog("fldExpressMail") = Data(17) 'Express Mail
Rlog("fldLSARCChecksAttempted") = Data(18) 'LS ARC Checks Attempted *******New Field********
Rlog("fldHSARCChecksAttempted") = Data(19) 'HS ARC Checks Attempted *******New Field********
Rlog("fldLSARCChecksConverted") = Data(20) 'LS ARC Checks Converted *******New Field********
Rlog("fldHSARCChecksConverted") = Data(21) 'HS ARC Checks Converted *******New Field********
Rlog("fldLookupStubs") = Data(22) 'Lookup Stubs *******New Field********
Rlog("fldStubsOnly") = Data(23) 'Stubs Only *******New Field********
Rlog("fldTotalDollarsDeposited") = Data(24) 'Total Dollars Deposited For DDA
Rlog.Update

Loop

MsgBox " File has been successfully imported! "
'close and erase the file from memory
objText.Close
Set objText = Nothing

Rlog.Close
Set Rlog = Nothing

DoCmd.OpenTable "tblCoreImport2", acViewPreview

End Sub