Import multiple .csv

[New_to_this]

HI,

I'm very new to this so please explain in simple terms!

I have found the code below to import multiple .csv files into one table but I now need to append the file name to each file once its been imported so I can keep a track, if this is possible and as I'm very new to all this could you insert the code into the one below, so I cant make any mistakes?

many thanks in advance

Sean


Sub Import_multiple_csv_files()


Const strPath As String = "C:\Addresspoint" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"PETA VSK Merged", strPath & strFileList(intFile)
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

 

[Ranman256]

after import, set the null FILENAME field

Public Sub btnImport_click()
  ImportFilesInDir "c:\myfolder\folder\"
end sub


Public Sub ImportFilesInDir(ByVal pvDir)
Dim FSO, oFolder, oFile, oRX
Dim sTxt As String, sFile As String,  sSql as string

On Error GoTo errGetFiles

docmd.setwarnings false
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)

If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"

For Each oFile In oFolder.Files
 If InStr(oFile.Name, ".csv") > 0 Then            'import file here
      sFile = pvDir & oFile.Name
      DoCmd.TransferText acImportDelimi, ,"PETA VSK Merged",sFile

      sSQl = "update [PETA VSK Merged] set [Filename]='" & sFile & "' where [Filename] is null)"
      docmd.runSql sSql
 End If
Next

endit:
docmd.setwarnings true
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
Exit Sub

errGetFiles:
If Err = 3265 Then     'catch error if NO Import table errors
  ' "no errors found"
   Resume Next
Else
  MsgBox Err.Description, , Err
End If
End Sub

 

[New_to_this]

Hi Sorry,

thanks for this but as I said before I'm a total newbie when I paste your query I get path not found, now I know its something really silly. the folder my files are located in is called "originals" on my C:\ and it contains 5 .csv file called 1, 2, 3, 4 , 5
the error message I get is "path not found"

once again, my apologies for this will soon pick it up!!

thanks
Sean

 

[Ranman256]

The import_click code should use YOUR path.

 

[New_to_this]

Thanks Ranman, I managed to sort out what I was doing wrong the code works a treat, your help is much apricated, have many more questions regarding macros for dedupe queries etc. will get these typed up tomorrow and hopefully you can assist again!