Importing Large Text File

[FrymanSMU]

I'm trying to Import a fixed width text file that is 1200 bytes and my code keeps getting stuck. Can a string not contain that long of a variable? I am trying to build the import in VBA so I can import the Header Row to one table and the Details to seperate table. I'm sure there are other ways to do this and any help is appreciated.

Thanks,

Rich

Public Sub FixedWidth_Text()
Dim FileTxt As String
Dim db As DAO.Database
Set db = CurrentDb

' RETRIEVE DATA FROM FILE
' Open file for input.
Open "C:\DST_File.txt" For Input As #1
' Loop until the end of file is reached.
Do While Not EOF(1)
' Read data into variables.
Input #1, FileTxt$
' Print data to Debug window.
If Left$(FileTxt$, 1) = "H" Then
''***Header Row***
If IsNull(DLookup("OutputFileName", "[Output_Files]", "[Output_Files]![OutputFileName] ='" & Trim$(Mid$(FileTxt$, 6, 47)) & "'")) Then
Dim H_Rst As DAO.Recordset
Set H_Rst = db.OpenRecordset("Output_Files")
H_Rst.AddNew
H_Rst![FileRevision] = Trim$(Mid$(FileTxt$, 2, 4))
H_Rst![OutputFileName] = Trim$(Mid$(FileTxt$, 6, 47))
H_Rst![InputFileName] = Trim$(Mid$(FileTxt$, 53, 47))
H_Rst![DST_OutputDir] = Trim$(Mid$(FileTxt$, 100, 187))
H_Rst![ProcessRtnValue] = Trim$(Mid$(FileTxt$, 287, 1))
H_Rst![RecordCount] = Trim$(Mid$(FileTxt$, 288, 8))
H_Rst![AEC_Type] = Trim$(Mid$(FileTxt$, 296, 1))
H_Rst![InputFileFormat] = Trim$(Mid$(FileTxt$, 297, 4))
H_Rst.Update
Dim OutputID As String
OutputID$ = DLookup("OutputFileID", "[Output_Files]", "[Output_Files]![OutputFileName] ='" & Trim$(Mid$(FileTxt$, 6, 47)) & "'")
Else
Exit Sub
End If

ElseIf Left$(FileTxt$, 1) = "D" Then
''***Detail Row***
Dim D_Rst As DAO.Recordset
Set D_Rst = db.OpenRecordset("Output_Files")
D_Rst.AddNew
D_Rst![OutputFileID] = OutputID$
D_Rst![CustomerKey] = Trim$(Mid$(FileTxt$, 2, 50))
D_Rst![Old_Address1] = Trim$(Mid$(FileTxt$, 61, 60))
D_Rst![Old_Address2] = Trim$(Mid$(FileTxt$, 121, 60))
D_Rst![Old_Address3] = Trim$(Mid$(FileTxt$, 181, 60))
D_Rst![Old_Address4] = Trim$(Mid$(FileTxt$, 241, 60))
D_Rst![Old_Address5] = Trim$(Mid$(FileTxt$, 301, 60))
D_Rst![Old_Address6] = Trim$(Mid$(FileTxt$, 361, 60))
D_Rst![Old_CASS_PrimaryAdd] = Trim$(Mid$(FileTxt$, 421, 60))
D_Rst![Old_CASS_City_State_ZIP] = Trim$(Mid$(FileTxt$, 481, 60))
D_Rst![NCOA_ReturnCode] = Nz(Trim$(Mid$(FileTxt$, 541, 2)), "X")
D_Rst![MoveType] = Nz(Trim$(Mid$(FileTxt$, 543, 1)), "X")
D_Rst![New_CASS_PrimaryAdd] = Trim$(Mid$(FileTxt$, 544, 60))
D_Rst![New_CASS_City_State_ZIP] = Trim$(Mid$(FileTxt$, 604, 60))
D_Rst![New_Address1] = Trim$(Mid$(FileTxt$, 664, 60))
D_Rst![New_Address2] = Trim$(Mid$(FileTxt$, 724, 60))
D_Rst![New_City] = Trim$(Mid$(FileTxt$, 784, 28))
D_Rst![New_State] = Trim$(Mid$(FileTxt$, 812, 2))
D_Rst![New_ZIP] = Trim$(Mid$(FileTxt$, 814, 5))
D_Rst![New_ZIP+4] = Trim$(Mid$(FileTxt$, 819, 4))
D_Rst![New_DPBC] = Trim$(Mid$(FileTxt$, 823, 2))
D_Rst![New_CarrierRoute] = Trim$(Mid$(FileTxt$, 825, 4))
D_Rst![MoveDate] = DateSerial(Mid$(FileTxt$, 829, 4), Mid$(FileTxt$, 833, 2), 1)
D_Rst![CASS_Error] = Trim$(Mid$(FileTxt$, 835, 6))
D_Rst![CASS_StatusCode] = Trim$(Mid$(FileTxt$, 841, 6))
D_Rst![CASS_StatusCode1] = Trim$(Mid$(FileTxt$, 841, 1))
D_Rst![CASS_StatusCode2] = Trim$(Mid$(FileTxt$, 842, 1))
D_Rst![CASS_StatusCode3] = Trim$(Mid$(FileTxt$, 843, 1))
D_Rst![CASS_StatusCode4] = Trim$(Mid$(FileTxt$, 844, 1))
D_Rst![DPV_FootNote] = Trim$(Mid$(FileTxt$, 847, 12))
D_Rst![DPV_Status] = Nz(Trim$(Mid$(FileTxt$, 859, 1)), "X")
D_Rst![DPV_MatchCode] = Nz(Trim$(Mid$(FileTxt$, 860, 1)), "X")
D_Rst![LACS_Indicator] = Nz(Trim$(Mid$(FileTxt$, 861, 1)), "X")
D_Rst![LACS_ReturnCode] = Nz(Trim$(Mid$(FileTxt$, 862, 2)), "X")
D_Rst![LACS_ReturnType] = Nz(Trim$(Mid$(FileTxt$, 864, 1)), "X")
D_Rst![IMB_DataString] = Trim$(Mid$(FileTxt$, 865, 31))
D_Rst![IMB_EncodedData] = Trim$(Mid$(FileTxt$, 896, 64))
D_Rst![Suite_ReturnCode] = Nz(Trim$(Mid$(FileTxt$, 960, 2)), "X")
D_Rst![PuertoRicanUrbanName] = Trim$(Mid$(FileTxt$, 962, 35))
D_Rst![AEC_Flag] = Trim$(Mid$(FileTxt$, 997, 1))
D_Rst![AEC_RecordTypeCode] = Trim$(Mid$(FileTxt$, 998, 1))
D_Rst![MatchNamePrefix] = Trim$(Mid$(FileTxt$, 999, 6))
D_Rst![MatchFirstName] = Trim$(Mid$(FileTxt$, 1005, 15))
D_Rst![MatchMiddleName] = Trim$(Mid$(FileTxt$, 1020, 15))
D_Rst![MatchLastName] = Trim$(Mid$(FileTxt$, 1035, 20))
D_Rst![MatchNameSuffix] = Trim$(Mid$(FileTxt$, 1055, 6))
D_Rst.Update
End If
Loop
Close #1
End Sub

[OBP]

VBA String Variables can easily handle that many characters. I would add some error handling as it may be a coding error that is causing your problem.

[FrymanSMU]

I have not seen any errors, I have not tested the code fully I'm just stepping through the code and it hangs at the line

' Read data into variables.
Input #1, FileTxt$

[OBP]

I would use a TransferText and import the whole file in to a Table and work that.