CODEWITHSUNDEEP
sqlms-accessvba
Sid Vicious
Sid Vicious

Reputation: 1

file name to access table

I am a novice and your help and patience will be greatly appreciated. Many tks in advance. I need the correct syntax/code to get a file name into an access dbase table. Below is the code and output. Output is 8 colums, first 7 columns are numbers and column 8 is where I want the file name. The code goes to a directory, checks for all the csv files there then imports the csv data into an access dbase table.I need to populate column 'com' with the csv filename. At the moment it is populated with a string. 

Sub Import_multiple_csv_files()


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

 '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
     SQL = " UPDATE Test SET Com = ""'strFile'"" WHERE Com IS NULL OR Com=''"
For intFile = 1 To UBound(strFileList)

DoCmd.TransferText acImportDelimi, , _
    "Test", strPath & strFileList(intFile)
   'DoCmd.RunSQL SQL
   CurrentDb.Execute SQL

Output seems not to 

F1          F2      F3      F4      F5      F6      F7      com
20111128    2.6922  2.6922  2.6922  2.6922  3340    17696   'strFile'
20111129    2.7229  2.7229  2.7229  2.7229  5010    18141   'strFile'
20111130    2.7401  2.7401  2.7401  2.7401  3641    18723   'strFile'
20111201    2.7376  2.7376  2.7376  2.7376  8087    19321   'strFile'
20111202    2.7784  2.7784  2.7784  2.7784  0       0       'strFile'
20111128    2.6727  2.6727  2.6727  2.6727  3889    26111   'strFile'
20111129    2.7039  2.7039  2.7039  2.7039  4562    26647   'strFile'
20111130    2.722   2.722   2.722   2.722   3043    27099   'strFile'
20111201    2.7218  2.7218  2.7218  2.7218  9180    27037   'strFile'
20111202    2.7623  2.7623  2.7623  2.7623  0       0       'strFile'

Upvotes: 0

Views: 1616

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

Seems you don't actually want single quotes in the data:

SQL = "UPDATE Test SET Com = '" & strFile & "' WHERE LEN(Com) = 0;"

...but I think you need to change the SQL inside your later loop e.g. 

'cycle through the list of files &  import to Access
'creating a new table called MyTable

SQL = "UPDATE Test SET Com = '{0}' WHERE LEN(Com) = 0;"

For intFile = 1 To UBound(strFileList)

  DoCmd.TransferText acImportDelimi, , _
      "Test", strPath & strFileList(intFile)
  CurrentDb.Execute Replace$(SQL, "{0}", strPath & strFileList(intFile))

...but that would only take effect once when the null and blank values are updated!

Upvotes: 0

Simon
Simon

Reputation: 6152

You would be better off editing your original question than raising a new one but the answer is that you are confusing your quotes and not concatenating the variable into the string, Should be something like:

SQL = "UPDATE Test SET Com = '''" & strFile & "''' WHERE Com IS NULL OR Com = ''"

Surrounding your strFile with quotes is causing it to be treated as as string literal rather than getting the value of the string variable itself.

Upvotes: 1

Related Questions