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.

 

[txab]

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

Is it possible to use a wildcard in the filename here without resorting to VBA? My csv filenames change regularly but are always located in the same folder.

 

[CJ_London]

no - just build the sql and substitute

dim fName as string
dim sqlStr as string

fname=dir(C:\Users\Daniel\Documents\Test\*.txt)
while fname<>""
    sqlStr="SELECT * INTO NewTable FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\Daniel\Documents\Test]." & fname
    currentdb.execute sqlStr
    fname=dir
wend

 

[MsAccessNL]

Is it possible to use a wildcard in the filename here without resorting to VBA? My csv filenames change regularly but are always located in the same folder.

with text files you can set the folder (without the file name) as database, all the text files are tables in the db.

 

[txab]

with text files you can set the folder (without the file name) as database, all the text files are tables in the db.

If I remove the filename following the bracket (like below), I get a "cannot find table" error.
SELECT * INTO NewTable<br>FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\Daniel\Documents\Test]
How should I proceed? Thank you!

 

[CJ_London]

How should I proceed?

per post #10? It cannot be done in a query - only by creating the query

 

[Pat Hartman]

The name of the linked table does not have to be the same as the name of the file. You can have a static TableName and relink it to FileA, FileB, FileC, whatever without changing any of your queries or code provided the structure of the linked files remain identical.

 

[txab]

per post #10? It cannot be done in a query - only by creating the query

Sorry per post #11. I don't want to use VBA for this scenario.

 

[CJ_London]

since it is the only way you can do it and you don’t want to use vba, I guess you are going to be very disappointed

Only other ways are to create a linked table to the txt file and change the source manually (without using vba) then run your query

Or there’s the transfertext method- but that’s vba

Or I guess you could open the text file in excel then manually copy/paste to your table

 

[CJ_London]

with text files you can set the folder (without the file name) as database

how would you do that in Access? and I guess without using vba

 

[txab]

how would you do that in Access? and I guess without using vba

Yeah - that's the sticking point for me. The connection string works when I include the filename but fails without one. The issue is that the CSV filenames change every day and do not exhibit patterns like datetime stamps, etc.

 

[GPGeorge]

Sorry per post #11. I don't want to use VBA for this scenario.

Without VBA, this will be a completely manual process. Perhaps copy/paste will work. Perhaps going through the steps of the External Data-->Linking wizard will work.

 

[Pat Hartman]

Why would you not want to use VBA? You are handicapping yourself.