Transfer Text append file info to field

[Ziggy1]

Hello,

I'm working on code that checks a directory and imports files matching a certain criteria...I have this working

What I want to do now is take the filename and add it to a field so the records will be related to the import file...do I need an append query and then pass the variables to the query?

One way I was thinking would be that the import table is temporary and then an append query takes the filename variable and adds it to the records when appending...but I am not sure how to pass the variable

 

[namliam]

Yes you are spot on... The way I do it... usually is...
1) Import file into temporary table
2) Store the filename in a "processed file" table with an autonumber
3) Append the file from the temp table to the fixed table including the autonumber.

This is the fastest way you can do this, advantage of having a seperate (small) "processed file" table is you can check it and add stuff like file size, imported date, # of records etc and use it for e.g. reporting purposes.

Let me know if you need more info.

 

[Ziggy1]

thanks, I figured out a solution using CurrentDb.Execute StrSQL

I made up a string for an append query and included the variables I wanted to Pass. I'll post and example when I clean it up...I'm doing modifications as I think the whole process through.

so far I am importing, appending and Renaming (adding BK) the source files so they can't be imported again, and also checking if the file has previously been imported...then renaming it as a duplicate + orig filename...the duplicate check continues to rename with an incremental counter ( just in case it happens more than once).

It's looking pretty cool right now.

 

[namliam]

Great, sounds like you are enjoying yourself !

 

[Ziggy1]

yes it I was having fun manipulating the files from Access...never had a need to until now..good learning experience. Anyways, here is the code I put together...I had found some code by rsmonkey which got me pointed in the right direction. I added several pieces to it..notably renaming the files and checking for duplicates....I could have arranged it differently but I'm more concerned with the fact that it works...and after a lot of testing it does the job.


Here is the sample code...

Option Compare Database

Public Function fileImportPuro()

'http://www.access-programmers.co.uk/forums/showthread.php?&p=628333#post628333  by rsmonkey
' Modified Ziggy Mar 25 2008



Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim DEC As String ' check for second dot in filename and replace
Dim OldName, NewName, OrigName As String
Dim StrSQL As String
Dim qryDef As DAO.QueryDef

    Dim filectr As Integer
    filectr = 1

  On Error GoTo ErrorHandler

 ' DoCmd.SetWarnings False
  
  'Test locations
  path = "paste your folderpath to import from"
  
   
 'MsgBox path
  'Loop through the folder & build file list
  strFile = Dir(path & "*.csv")
 
  While strFile <> ""
     'add files to the list if meeting Criteria
     
     'DEC = Len(strFile) ' checks filename length
      DEC = Mid(strFile, 19, 4) ' Identifies if dot in 19th position zs  you can also use LEN to check file length or a mixed string to check for.
 
        If DEC = ".210" Then ' only valid files have dot +210 in 19th position zs
       ' If DEC = 14 Then ' only valid files with lenght of 14
  
            intFile = intFile + 1
             ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
     
        End If
     
      strFile = Dir()
  Wend
 
  'see if any files were found
  If intFile = 0 Then
    MsgBox "No 210 files found "
    Exit Function
  End If
 
  'cycle through the list of files
  For intFile = 1 To UBound(strFileList)
     
     
  ' Modified Ziggy 
  
            OrigName = strFileList(intFile)
     
            OldName = path & strFileList(intFile)
  
            'NewName = path & Left(strFileList(intFile), 10) & "BK.csv"
            NewName = path & Left(strFileList(intFile), 18) & "-" & Mid(strFileList(intFile), 20, 3) & "BK.csv"
            
            Name OldName As NewName    ' Rename file
  
              filename = NewName
    
            DoCmd.TransferText acImportDelim, "Purolator4", "tblPuroInvoiceTEMP", filename, False, ""
  

'form for viewing SQL output..disabled
'DoCmd.OpenForm "form2"

'Forms.form2.txtsql = StrSQL

  
ErrorHandler:
     'Display error information.
    'MsgBox "Error number " & Err.Number & ": " & Err.Description
    
    If Err.Number = 58 Then
    
   
Error2:
    NewName = path & "DUPLICATE_" & filectr & "_" & strFileList(intFile)
    MsgBox OrigName & "  File already imported please confirm" & vbNewLine & vbNewLine & "Renaming to:  " & "DUPLICATE_" & filectr & "_" & strFileList(intFile)
    
    
    filectr = filectr + 1
    Resume
    
        On Error GoTo Error2
    Name OldName As NewName    ' Rename file
        
 Exit Function
End If


' SQL for Append table to move data also adds the filename to a field for reference create a form to debug the SQL as some fields are tricky
  StrSQL = "INSERT INTO tblPuroInvoice_App ( [PARTNER-TAG], [INV-NO], [MAIN-AC], [CUST-NAME], [HEADER-ADDR], [HEADER-CITY], PROV, PCODE, [DATE], [INV-ATTN], [CR-DB], [GIRV-CUST-CODE], [GIRV-PAY-CODE], [INV-TOTAL-AMT], [MATCH-SURCHARGE], [MATCH-SURCH-GST], [CONTRACT-NUM], [SAC-SUB-AC], [SAC-NAME], [SAC-ADDR], [SAC-CITY], [SAC-PROV], [SAC-PCODE], [SAC-INV-TOTAL-AMT], [REC-TYPE], [BL-NO], [SERV-DATE], PIECES, WT, [WT-UNITS], AMT, COLLECT, 900AM, BEYOND, [ACCOUNT-NO-CHG], [NON-PACK-SRCH], INSURANCE, [FUEL-SURCHARGE], DANGR, QUICKS, GST, [INCOMPLETE-ADDRESS], [COS-SURCH], [PC-FLAG], WEEKENDER, [NON-PACK-PIECES], [SERV-TYPE], REFERENCE, PRODUCT, [TRANS-CODE], PST, AOD, POD, PUROTHERM, ECO, [CONSOL-PIN], [CONSOL-CITY], [CONSOL-PROV], [WEIGHT-8], [CONSOL-SHIP], [SENDER-NAME], [SENDER-ADDR], [SENDER-UNIT], [FROM-CITY], [FROM-PROV], [FROM-POST-CODE], [RECEIVER-NAME], [RECEIVER-ADDR], [RECEIVER-UNIT], [TO-CITY], [TO-PROV], [TO-POST-CODE], [MAN-TOTAL-INV-AMT], [MANIFEST-NO], [CONT-NBR], [CONT-DESC1], [CONT-DESC2]," & _
  "[CONT-BILL-AMT], [CONT-OVRWT-LB], [CONT-OVRWT-RATE], [CONT-FUEL-CHRG], [CONT-GST-CHRG], [CONT-PST-CHRG], [BANK-DESC], [BANK-DAYS], [BANK-AMT-PER-DAY], [BANK-NO-OF-LOC], [BANK-BILL-AMT], [BANK-FUEL-CHRG], [BANK-GST-CHRG], [BANK-NO], [BANK-PST-CHRG], [PARCEL-CUBE-VOL], Field94, filekey, AddDate )" & _
" SELECT tblPuroInvoiceTEMP.[PARTNER-TAG], tblPuroInvoiceTEMP.[INV-NO], tblPuroInvoiceTEMP.[MAIN-AC], tblPuroInvoiceTEMP.[CUST-NAME], tblPuroInvoiceTEMP.[HEADER-ADDR], tblPuroInvoiceTEMP.[HEADER-CITY], tblPuroInvoiceTEMP.PROV, tblPuroInvoiceTEMP.PCODE, tblPuroInvoiceTEMP.DATE, tblPuroInvoiceTEMP.[INV-ATTN], tblPuroInvoiceTEMP.[CR-DB], tblPuroInvoiceTEMP.[GIRV-CUST-CODE], tblPuroInvoiceTEMP.[GIRV-PAY-CODE], tblPuroInvoiceTEMP.[INV-TOTAL-AMT], tblPuroInvoiceTEMP.[MATCH-SURCHARGE], tblPuroInvoiceTEMP.[MATCH-SURCH-GST], tblPuroInvoiceTEMP.[CONTRACT-NUM], tblPuroInvoiceTEMP.[SAC-SUB-AC], tblPuroInvoiceTEMP.[SAC-NAME], tblPuroInvoiceTEMP.[SAC-ADDR], tblPuroInvoiceTEMP.[SAC-CITY], tblPuroInvoiceTEMP.[SAC-PROV], tblPuroInvoiceTEMP.[SAC-PCODE], tblPuroInvoiceTEMP.[SAC-INV-TOTAL-AMT], tblPuroInvoiceTEMP.[REC-TYPE], tblPuroInvoiceTEMP.[BL-NO], tblPuroInvoiceTEMP.[SERV-DATE], tblPuroInvoiceTEMP.PIECES, tblPuroInvoiceTEMP.WT, tblPuroInvoiceTEMP.[WT-UNITS], tblPuroInvoiceTEMP.AMT, tblPuroInvoiceTEMP.COLLECT," & _
"tblPuroInvoiceTEMP.[900AM] , tblPuroInvoiceTEMP.BEYOND, tblPuroInvoiceTEMP.[ACCOUNT-NO-CHG], tblPuroInvoiceTEMP.[NON-PACK-SRCH], tblPuroInvoiceTEMP.INSURANCE, tblPuroInvoiceTEMP.[FUEL-SURCHARGE], tblPuroInvoiceTEMP.DANGR, tblPuroInvoiceTEMP.QUICKS, tblPuroInvoiceTEMP.GST, tblPuroInvoiceTEMP.[INCOMPLETE-ADDRESS], tblPuroInvoiceTEMP.[COS-SURCH], tblPuroInvoiceTEMP.[PC-FLAG], tblPuroInvoiceTEMP.WEEKENDER, tblPuroInvoiceTEMP.[NON-PACK-PIECES], tblPuroInvoiceTEMP.[SERV-TYPE], tblPuroInvoiceTEMP.Reference, tblPuroInvoiceTEMP.PRODUCT, tblPuroInvoiceTEMP.[TRANS-CODE], tblPuroInvoiceTEMP.PST, tblPuroInvoiceTEMP.AOD, tblPuroInvoiceTEMP.POD, tblPuroInvoiceTEMP.PUROTHERM, tblPuroInvoiceTEMP.ECO, tblPuroInvoiceTEMP.[CONSOL-PIN], tblPuroInvoiceTEMP.[CONSOL-CITY], tblPuroInvoiceTEMP.[CONSOL-PROV], tblPuroInvoiceTEMP.[WEIGHT-8], tblPuroInvoiceTEMP.[CONSOL-SHIP], tblPuroInvoiceTEMP.[SENDER-NAME], tblPuroInvoiceTEMP.[SENDER-ADDR], tblPuroInvoiceTEMP.[SENDER-UNIT], tblPuroInvoiceTEMP.[FROM-CITY]," & _
"tblPuroInvoiceTEMP.[FROM-PROV] , tblPuroInvoiceTEMP.[FROM-POST-CODE] , tblPuroInvoiceTEMP.[RECEIVER-NAME], tblPuroInvoiceTEMP.[RECEIVER-ADDR], tblPuroInvoiceTEMP.[RECEIVER-UNIT], tblPuroInvoiceTEMP.[TO-CITY], tblPuroInvoiceTEMP.[TO-PROV], tblPuroInvoiceTEMP.[TO-POST-CODE], tblPuroInvoiceTEMP.[MAN-TOTAL-INV-AMT], tblPuroInvoiceTEMP.[MANIFEST-NO], tblPuroInvoiceTEMP.[CONT-NBR], tblPuroInvoiceTEMP.[CONT-DESC1], tblPuroInvoiceTEMP.[CONT-DESC2], tblPuroInvoiceTEMP.[CONT-BILL-AMT], tblPuroInvoiceTEMP.[CONT-OVRWT-LB], tblPuroInvoiceTEMP.[CONT-OVRWT-RATE], tblPuroInvoiceTEMP.[CONT-FUEL-CHRG], tblPuroInvoiceTEMP.[CONT-GST-CHRG], tblPuroInvoiceTEMP.[CONT-PST-CHRG], tblPuroInvoiceTEMP.[BANK-DESC], tblPuroInvoiceTEMP.[BANK-DAYS], tblPuroInvoiceTEMP.[BANK-AMT-PER-DAY], tblPuroInvoiceTEMP.[BANK-NO-OF-LOC], tblPuroInvoiceTEMP.[BANK-BILL-AMT], tblPuroInvoiceTEMP.[BANK-FUEL-CHRG], tblPuroInvoiceTEMP.[BANK-GST-CHRG], tblPuroInvoiceTEMP.[BANK-NO], tblPuroInvoiceTEMP.[BANK-PST-CHRG], tblPuroInvoiceTEMP.[PARCEL-CUBE-VOL]," & _
"tblPuroInvoiceTEMP.Field94," & "'" & OrigName & "'&" & " [tblPuroInvoiceTEMP.BL-NO]" & " as [filekey] , Now() AS AddDate" & _
" FROM tblPuroInvoiceTEMP;"


  CurrentDb.Execute StrSQL
  ' Deletes the data in Temp table
  DoCmd.OpenQuery "qryDelete_PuroInvoiceTEMP"
  
  Next intFile
 ' DoCmd.SetWarnings True


End Function

 

[namliam]

Good show !

Comments
- Make your sql readable! Make proper lines out of it like it were a real query.
You can do this by concatting the query like so:
strSQL = ""
StrSQL = STRSQL & "SELECT ..."
StrSQL = STRSQL & " ..."
StrSQL = STRSQL & " ..."
StrSQL = STRSQL & "FROM ..."
Etc...
You will thank yourself some day that you were allways doing this.... Or curse yourself to hell some day for not doing this.

- Use proper parameters
Dont use filename as a parameter. Atleast make sure for every parameter it starts with the type...
intFile for example is a good one, so not filename, but txtFileName
Same goes for DEC offcourse.

Tips:
- FSO
Check out the FileSystemObject someday when you have time. This will enable you to do things on the filesystem like create folders and stuff.
To use it:
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Now you can use i.e. fs.movefile to move files....

- FS
FileSearch much easier than using the current dir structure tho it does much the same and more
Dim FS As Object
Set FS = Application.FileSearch
FS.newsearch
'Change path to suit
FS.LookIn = folder
FS.FileName = "*.txt"
FS.Execute
Look for more details in the help

Note tho for both you need to reference one of the two following references:
- Microsoft scripting runtime
- Microsoft Offce XXX Object library (XXX dependant on version)

 

[Ziggy1]

thanks for the tips, I DO like what you said about the SQL...I see it is appending to the variable with each line, i will definately use that...I'm not a big fan of writing SQL, I'll load up my tables in the query grid and then copy the SQL.

I understand what you mean about the naming convention of the variables with a prefix to give you the visual of the datatype..I do that for the controls but not really for in the code...but good point.


when I have time..ha ha that's a good one..but ya I'll have to check out the other method you suggested....

thanks a lot.




Ziggy

 

[namliam]

thanks for the tips, I DO like what you said about the SQL...I see it is appending to the variable with each line, i will definately use that...I'm not a big fan of writing SQL, I'll load up my tables in the query grid and then copy the SQL.

I do it the same a lot of times, but still for postterity sake... make it readable... it is not that much work and, it makes live so much easier when
1) You have to go back after 2 years and change something
2) Someone else has to look at your code

when I have time..ha ha that's a good one..but ya I'll have to check out the other method you suggested....

I am pretty much the same... normaly... Busy is good tho... means you are adding to the business