Selecting&importing text file into table

[BusMgr]

I am looking for the code that will allow user to select .txt file and then to import into access and update a predefined table. The data in the .txt is tab delimited.

Any help would be appreciated.

BusMgr

 

[BanditWk]

This is what I have - use what you need:

Dim Ext
Dim BackupSearch As String
Dim ErrCode As String
Dim Acct As String
Dim FBErr As String
Dim Fil As Integer
Dim Pos As Integer
Dim XPos As Integer
Dim PrepVer As Boolean
Dim PrepSoc
Dim Today

100 Set Dbs = CurrentDb
200 Set Rst = Dbs.OpenRecordset("***BatchConf2***", dbOpenDynaset)
900 Ctr = 0
1000 XPos = 1
1100 Today = IIf(DatePart(&quot;m&quot;, Date) < 10, &quot;0&quot; & DatePart(&quot;m&quot;, Date), DatePart(&quot;m&quot;, Date)) & IIf(DatePart(&quot;d&quot;, Date) < 10, &quot;0&quot; & DatePart(&quot;d&quot;, Date), DatePart(&quot;d&quot;, Date)) & DatePart(&quot;yyyy&quot;, Now())

1200 Select Case Len(CStr(XPos))
Case 1
1300 Ext = &quot;00&quot; & CStr(XPos)
1400 Case 2
1500 Ext = &quot;0&quot; & CStr(XPos)
1600 Case 3
1700 Ext = CStr(XPos)
1800 End Select
1900 Fil = 0
1950 PrepVer = False
1970 PrepSoc = Null


2000 Do While Len(Dir(&quot;c:\Progra~1\Sent\Tt708t0.&quot; & Ext)) <> 0
2100 Fil = Fil + 1
2200 DirNm = Null
2300 Ctr = 0
2400 Err.Number = 0
2500 On Error Resume Next
2600 Rst.MoveLast
2700 If Err.Number = 3021 Then GoTo ImportText
2800 Rst.MoveFirst
2900 Ttl = Rst.RecordCount

ImportText:
' Importing text file
3300 Forms!StatusBox!Status.Caption = &quot;Importing Confirmation File...&quot;
3400 DoEvents
3500 Open &quot;C:\Progra~1\Sent\Tt708t0.&quot; & Ext For Input As #1
3600 Do While Not EOF(1)
3700 Ctr = Ctr + 1
3800 Forms!StatusBox!Status.Caption = &quot;File = &quot; & Fil & &quot; | Line = &quot; & Ctr
3900 DoEvents

4000 Line Input #1, LineData
4100 Select Case Left(LineData, 1)
Case &quot;5&quot;
4200 If IsNull(DirNm) Then DirNm = MID(LineData, 72, 2) & MID(LineData, 74, 2) & &quot;20&quot; & MID(LineData, 70, 2)
4300 Case &quot;6&quot;
4400 Rst.AddNew
4500 If MID(LineData, 13, 1) <> &quot; &quot; Then Rst!ErrorCode = MID(LineData, 13, 3)
4600 If MID(LineData, 23, 3) <> &quot; &quot; Then FBErr = MID(LineData, 23, 3)
4700 Rst!AcctNum = Trim(MID(LineData, 55, 22))
4800 Case &quot;7&quot;
4900 Rst!SSN = MID(LineData, 28, 3) & &quot;-&quot; & MID(LineData, 31, 2) & &quot;-&quot; & MID(LineData, 33, 4)
5000 Select Case MID(LineData, 4, 3)
Case &quot;CDT&quot;
5100 Rst!ErrorCode = &quot;TRM&quot;
5150 PrepVer = False
5200 Case &quot;CDA&quot;
5300 Rst!ErrorCode = &quot;030&quot;
5350 PrepVer = True
5375 PrepSoc = Rst!SSN
5400 Case &quot;CDR&quot;
5500 Rst!ErrorCode = MID(LineData, 52, 3)
5550 PrepVer = True
5575 PrepSoc = Rst!SSN
5600 Case &quot;CDV&quot;
5700 If PrepVer And PrepSoc = MID(LineData, 28, 3) & &quot;-&quot; & MID(LineData, 31, 2) & &quot;-&quot; & MID(LineData, 33, 4) Then
5710 PrepVer = False
5730 PrepSoc = Null
5750 Else
5775 Rst!ErrorCode = &quot;VFD&quot;
5785 End If
5790 Rst!FBError = Format(MID(LineData, 22, 10), &quot;Currency&quot
5800 Case Else
5900 If MID(LineData, 22, 2) <> &quot; &quot; Then
5925 Rst!FBError = &quot;FBC&quot; & FBErr
5950 Else
5975 Rst!FBError = &quot;FBD&quot; & FBErr
5980 End If
5985 PrepVer = True
6000 End Select
6100 If MID(LineData, 37, 2) <> &quot;00&quot; Then
6125 Rst!ClientID = Trim(MID(LineData, 37, 10))
6150 Else
6175 Rst!ClientID = DLookup(&quot;[ClientID]&quot;, &quot;Client&quot;, &quot;[SSN1]= '&quot; & Rst!SSN & &quot;'&quot
6180 End If
6200 Rst.Update
6300 End Select
6400 Loop
6500 Close #1

6600 Forms!StatusBox!Status.Caption = &quot;Moving Confirmation File to appropriate directory...&quot;
6700 FileCopy &quot;C:\Progra~1\Sent\Tt708t0.&quot; & Ext, &quot;C:\Progra~1\Sent\&quot; & Today & &quot;\&quot; & Today & &quot;.&quot; & IIf(Len(CStr(Second(Now()))) < 2, &quot;0&quot; & CStr(Second(Now())), CStr(Second(Now()))) & &quot;0&quot;
6800 Kill &quot;C:\Progra~1\Sent\Tt708t0.&quot; & Ext

6900 DoCmd.OpenQuery &quot;DMPReturn Update&quot;

ByPass:
7000 XPos = XPos + 1
7100 Select Case Len(CStr(XPos))
Case 1
7200 Ext = &quot;00&quot; & CStr(XPos)
7300 Case 2
7400 Ext = &quot;0&quot; & CStr(XPos)
7500 Case 3
7600 Ext = CStr(XPos)
7700 End Select
7800 Loop

7900 If CloseG Then DoCmd.Close acForm, &quot;G&quot;
8000 Forms!StatusBox!Status.Caption = &quot;Completed with Confirmation File(s)...&quot;
8100 DoEvents
8200 Rst.Close
8300 Wait1
8400 Wait1
8500 DoCmd.Close acForm, &quot;StatusBox&quot;

I just did a quick copy and paste - a lot of this stuff, will not make sense to you, but maybe just use that which you feel will be beneficial to you. Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

&quot;I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped.&quot; ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas