Linking to multiple CSV files and renaming tables without ".CSV"

[OneDayOver]

Hi All

I have been using the following macro to link to multiple CSV files in a folder.

Would anyone know how to change it so that the linked tables don't include the ".CSV" part of the file name?

Many thanks

Sub Link_To_Excel_Test()
'Macro Loops through the specified directory (strPath)
'and links ALL Excel files as linked tables in the Access
'Database.

Const strPath As String = "C:\TABLES\" '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 & link to Access
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acLinkDelim, , _
strFileList(intFile), strPath & strFileList(intFile), True, ""
'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 Linked"
End Sub

 

[June7]

Drop the .csv extension.

Left(strFile, Len(strFile)-4)

Please use CODE tags when posting lengthy code to retain indentation and readability.

I wouldn't bother with the array.

    strFile = Dir("C:\TABLES\*.csv")
    Do While strFile <> ""
        DoCmd.TransferText acLinkDelim, , Left(strFile, Len(strFile)-4), strPath & strFile, True, ""
        strFile = Dir
    Loop

 

[arnelgp]

check this also.
will add .csv and those "without" extensions.

Sub Link_To_Excel_Test()
'Macro Loops through the specified directory (strPath)
'and links ALL Excel files as linked tables in the Access
'Database.

    Const strPath As String = "C:\TABLES\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    
    'arnelgp
    'resizing the array during runtime
    'may cause delay penalty
    'just create the biggest array then
    'resize it later
    ReDim strFileList(1 To 5000)
    
    'Loop through the folder & build file list
    strFile = Dir(strPath & "*.*")
    While strFile <> ""
        'arnelgp
        'check if it is a .csv or doesn't have extension
        If Right$(strFile, 3) = "csv" Or InStr(1, strFile, ".") = 0 Then
            'add files to the list
            intFile = intFile + 1
            'ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir$()
        End If
    Wend
    'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
    'arnelgp
    'resize the array
    ReDim Preserve strFileList(1 To i)
    
    'cycle through the list of files & link to Access
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acLinkDelim, , _
        strFileList(intFile), strPath & strFileList(intFile), True, ""
        '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 Linked"
End Sub

 

[OneDayOver]

Drop the .csv extension.

Left(strFile, Len(strFile)-4)

Please use CODE tags when posting lengthy code to retain indentation and readability.

I wouldn't bother with the array.

    strFile = Dir("C:\TABLES\*.csv")
    Do While strFile <> ""
        DoCmd.TransferText acLinkDelim, , Left(strFile, Len(strFile)-4), strPath & strFile, True, ""
        strFile = Dir
    Loop

I implemented your code and was able to get it working perfectly. Thank you, your help is much appreciated.

 

[MsAccessNL]

You can also import textfiles with SQL directly, may be it can shorten your code ( i love short codes).

SELECT * INTO NewTable
FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\Daniel\Documents\Test].TestFile.txt

 

[June7]

That SQL would create local table, not set up a link. If you want to import into existing local table, can use DoCmd.TransferText or SQL (INSERT INTO SELECT). I do wonder why you need a procedure for linking CSV files - would this be a repetitive event?

 

[OneDayOver]

That SQL would create local table, not set up a link. If you want to import into existing local table, can use DoCmd.TransferText or SQL (INSERT INTO SELECT). I do wonder why you need a procedure for linking CSV files - would this be a repetitive event?

The multiple CSV files I'm linking to will be updated weekly and in setting up I was having to change the .CSV manually was starting to become very time consuming (well over 100 files to link to). The files are now all linked and I'm hoping will refresh in the database automatically each week.

 

[June7]

Yes, data should refresh as long as CSV file replaced with file of same name and structure.