Hi all,
I am wanting to read the values of a tab delimited file into an access table. To allow the user, to input the a file path(via a text box). When a command button is pressed a table in Access will populate with the data from the .tab file. I am not sure how to reference the tab character that seperates each column of the .tab file. This is the code I have so far. The file contains 79 columns and about 150,000 rows.
Code:Sub LoadDataFromTab() 'copy data from the .tab file into MS Access cell
Dim strPath As String
strPath = Me.txtPath
Dim AppSourceCode As String
Dim abaNumber As String
Dim location As String
Dim charter As String
Dim divNumber As String
Dim appNumber As String
Dim loanNumber As String
Dim appDate As String
Dim actiontaken As String
Dim contractDate As String
Dim closeout As String
Dim custShortName As String
Dim address As String
Dim cityStateZip As String
Dim ssn As String
Dim appType As String
Dim hmdaCode As String
Dim purposeCode As String
Dim occupancy As String
Dim totHMDAFunds As String
Dim loanAmt As String
Dim actTaken As String
Dim applicantRace As String
Dim applicantGender As String
Dim coAppRace As String
Dim coAppGender As String
Dim income As String
Dim purchaserType As String
Dim RejectReason1 As String
Dim RejectReason2 As String
Dim rejectReason3 As String
Dim rejectReason4 As String
Dim denialCode1 As String
Dim denialCode2 As String
Dim denialCode3 As String
Dim overrideCode As String
Dim appMethod As String
Dim officerNum As String
Dim incomeIND As String
Dim jointAppInd As String
Dim productCode As String
Dim recCode As String
Dim priAlg As String
Dim finalPointScore As String
Dim basedonIndicator As String
Dim collCode As String
Dim debtToIncRatio As String
Dim intRate As String
Dim intRateInd As String
Dim userDefinedField As String
Dim msaNo As String
Dim fipsStateCode As String
Dim fipsCountyCode As String
Dim censusTract As String
Dim blockGroup As String
Dim category As String
Dim acapsProCode As String
Dim conversionData As String
Dim originatingSource As String
Dim marketCode As String
Dim acapsLoc As String
Dim propType As String
Dim preApproval As String
Dim appRace2 As String
Dim appRace3 As String
Dim appRace4 As String
Dim appRace5 As String
Dim coAppRace2 As String
Dim coAppRace3 As String
Dim coAppRace4 As String
Dim coAppRace5 As String
Dim appEth As String
Dim coAppEth As String
Dim hoepaStatus As String
Dim lienPos As String
Dim term As String
Dim lockDate As String
Dim apr As String
Dim rateSpread As String
Dim tabFileSize As Long
'Call clearTableData
Dim rstStatus As ADODB.Recordset
Set rstStatus = New ADODB.Recordset
rstStatus.CursorLocation = adUseClient
rstStatus.Open "tblfiltereddata", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Close #2
Open strPath For Input As #2
'+++++++++++++++++++++++++++++++++++++++++++++++++++
'+
'+ Populate database
'+
Do Until EOF(2)
Input #2, AppSourceCode, abaNumber, location, charter, divNumber, appNumber, _
loanNumber, appDate, actiontaken, contractDate, closeout, custShortName, address, _
cityStateZip, ssn, appType, hmdaCode, purposeCode, occupancy, totHMDAFunds, loanAmt, _
actTaken, applicantRace, applicantGender, coAppRace, coAppGender, income, purchaserType, _
RejectReason1, RejectReason2, rejectReason3, rejectReason4, denialCode1, denialCode2, _
denialCode3, overrideCode, appMethod, officerNum, incomeIND, jointAppInd, productCode, _
recCode, priAlg, finalPointScore, basedonIndicator, collCode, debtToIncRatio, intRate, _
intRate, intRateInd, userDefinedField, msaNo, fipsStateCode, fipsCountyCode, censusTract, _
blockGroup, category, acapsProCode, conversionData, originatingSource, marketCode, acapsLoc, _
propType, preApproval, appRace2, appRace3, appRace4, appRace5, coAppRace2, coAppRace3, _
coAppRace4, coAppRace5, appEth, coAppEth, hoepaStatus, lienPos, term, lockDate, apr, rateSpread
rstStatus.AddNew
rstStatus![APPLICATION SOURCE CODE] = AppSourceCode
rstStatus![ABA NUMBER] = abaNumber
rstStatus![LOCATION/BRANCH NUMBER] = location
rstStatus![charter] = charter
rstStatus![DIVISION NUMBER] = divNumber
rstStatus![APPLICATION NUMBER] = appNumber
rstStatus![LOAN NUMBER] = loanNumber
rstStatus![APPLICATION DATE] = appDate
rstStatus![ACTION TAKEN DATE] = actiontaken
rstStatus![CONTRACT DATE] = contractDate
rstStatus![CLOSEOUT/EXTRACT DATE] = closeout
rstStatus![CUSTOMER SHORT NAME] = custShortName
rstStatus![ADDRESS/STREET] = address
rstStatus![CITY/STATE/ZIP] = cityStateZip
rstStatus![SOCIAL SECURITY NUMBER] = ssn
rstStatus![APPLICATION TYPE] = appType
rstStatus![HMDA PURPOSE CODE] = hmdaCode
rstStatus![PURPOSE CODE] = purposeCode
rstStatus![occupancy] = occupancy
rstStatus![TOTAL HMDA FUNDS] = totHMDAFunds
rstStatus![LOAN/LINE AMOUNT] = loanAmt
rstStatus![ACTION TAKEN] = actTaken
rstStatus.Update
Loop
Close #2
End Sub
