Feb 22, 2008
I have a database that a user wanted created. They gave me code used in another database to import the text file automatically using code. I looked at the code but I don't see where the file is coming from. I am not familiar with the transfertext code. I need to import a text file into an access table. The text file needs cleaning up because it comes from another program and it has breaks and the header repeats in the middle of the file several different times. I need code to clean up before it is imported into the table. I can't see how this code works enough to use it:
Code:
Private Sub cmdImport_Click()
Dim mResponse As String
Dim mDir As String
Dim intRecordLength As Integer
Dim strRecord As String
Dim strRecordType As String
Dim strSQL As String
Dim strID As String
Dim strDate As String
Dim strLev As String
Dim strCode As String
Dim strBranch As String
'************** Check for populated stat table
If DCount("[ID]", "tblStats") > 0 Then
MsgBox "Clear previous data before importing a new file.", vbOKOnly, "Clear Data"
Exit Sub
End If
'************** End Check
'************** Get File
mResponse = GetOpenFile_CLT(mDir, "Select file to be imported.")
mResponse = LCase$(mResponse)
If mResponse = "" Then
'If no path specified, abort
MsgBox "No file selected, import cancelled."
Exit Sub
End If
mDir = mResponse
Do While Right$(mDir, 1) <> ""
mDir = Left$(mDir, Len(mDir) - 1)
Loop
'************* End Get File
'************* Set Label/Form
Me.lblProcess.Caption = "Importing " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
'************* Start Import
Open mResponse For Input As 1
Do While Not EOF(1)
intRecordLength = 28
Input #1, strRecord
If Len(strRecord) < intRecordLength Then
GoTo sLoop
End If
'If length is 41
If Len(strRecord) > intRecordLength Then
strID = Trim(Left(strRecord, 12))
strDate = Trim(Mid(strRecord, 14, 11))
strLev = Trim(Mid(strRecord, 26, 3))
strCode = Trim(Mid(strRecord, 33, 2))
strBranch = Trim(Mid(strRecord, 40, 2))
End If
'If length is 28
If Len(strRecord) = intRecordLength Then
strDate = Left(strRecord, 11)
strCode = Mid(strRecord, 20, 2)
strBranch = Mid(strRecord, 27, 2)
End If
'Insert into table
strSQL = "INSERT INTO tblStats VALUES ('" & strID & "','" & strDate & "','" & _
strLev & "','" & strCode & "','" & strBranch & "');"
CurrentDb.Execute (strSQL)
sLoop:
Loop
Close 1
'********** End Import
sExit:
'************* Set Label/Form
Me.lblProcess.Caption = "Import Complete " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
End Sub
Please help
Thanks
View 5 Replies
View Related
Aug 27, 2007
Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.
1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338
I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.
View 4 Replies
View Related
