CODEWITHSUNDEEP
vbacsvms-access
Abrakadabra
Abrakadabra

Reputation: 11

How to import a CSV file when its name contains dots?

I need to import .csv files via script from a read-only directory to an Access table.

It fails for files with dots in the name: e.g. fi.le.name.csv.

I found these solutions:

  1. rename the file
  2. copy the file and give it a name without dots

Is it possible to solve it differently?

Dim strSelect as String
Dim strFile as String 
Dim strConnectionString as String
Dim strFolder as String
Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

strFolder = "C:\path"

strConnectionString = "Provider=" & _
  CurrentProject.Connection.Provider & _
  ";Data Source=" & strFolder & Chr(92) & _
  ";Extended Properties='text;HDR=YES;FMT=Delimited'"

cn.Open strConnectionString

strFile = "fi.le.name.csv"
strSelect = "SELECT * FROM " & strFile

rs.Open strSelect, cn, adOpenForwardonly 'and here is the failure

Upvotes: 0

Views: 723

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

I tried use shortname, it's working for me:

    Sub dots()
    Dim strSelect As String
    Dim strFile As String
    Dim strConnectionString As String
    Dim strFolder As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    
    strFolder = "c:\Users\Alex20\Documents" ' my path
    
    strConnectionString = "Provider=" & _
    "Microsoft.ACE.OLEDB.12.0" & _
    ";Data Source=" & strFolder & _
    "\;Extended Properties='text;HDR=YES;FMT=Delimited'"
    
    'Debug.Print strConnectionString
    
    cn.Open strConnectionString
    
    strFile = "fi.le.name.csv"
    strFile = ShortName(strFile)
    strSelect = "SELECT * FROM `" & strFile & "`"
    
    Debug.Print strSelect   'SELECT * FROM `FILENA~1.CSV`
    rs.Open strSelect, cn, adOpenForwardonly 'and here is the failure
    
    Do While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1)
        rs.MoveNext
    Loop
End Sub

Function ShortName(filespec)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    ShortName = f.ShortName
End Function

Upvotes: 2

Dy.Lee
Dy.Lee

Reputation: 7567

Copy the file to another folder and rename it by deleting the dot. The connection syntax is also specified in another folder.

Sub test()
    Dim strSelect As String
    Dim strFile As String
    Dim strConnectionString As String
    Dim strFolder As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim strTarget As String
    
    Set rs = New ADODB.Recordset 
    Set cn = New ADODB.Connection
    
    strFolder = "C:\path"
    strTarget = "C:\"
    
    strConnectionString = "Provider=" & _
    "Microsoft.ACE.OLEDB.12.0" & _
    ";Data Source=" & strTarget & Chr(92) & _
    ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    
    cn.Open strConnectionString
    
    strFile = "fi.le.name.csv" 
    Dim vF As Variant, newFile As String
    
    vF = Split(strFile, ".")
    newFile = Replace(strFile, vF(UBound(vF)), "")
    newFile = Replace(newFile, ".", "") & "." & vF(UBound(vF))
    
    FileCopy strFolder & "\" & strFile, strTarget & newFile
    
    strSelect = "SELECT * FROM " & newFile
    
    rs.Open strSelect, cn 'and here is the failure
    
    Debug.Print rs(0)
  
End Sub

Upvotes: 0

Related Questions