.net - Reading Excel csv file with extra dot in file name using OLEDB

Question

I have excel csv file with a dot in file name. (This is the industry naming convention, so I cannot change it.) I am trying to read all data using OLEDB. If I remove the dot from file name, my command works. If not it says invalid object. How to ignore/resolve dot in file name ?

file name : ABC Test bank Trail Feb 2014 $ 18083.65.csv

my code:

Public Function GetAllData(ByVal FileName As String) As DataTable

    Try
        Dim dt As New DataTable

        Using con As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader, Path.GetDirectoryName(FileName))}
            cn.Open()
            Dim SQLAdapter As OleDbDataAdapter = New OleDbDataAdapter()               
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [" + Path.GetFileName(FileName) + "]", con)
            SQLAdapter.SelectCommand = cmd
            SQLAdapter.Fill(dt)

        End Using
        Return dt
    Catch
        Return Nothing
    End Try
End Function

Answer 1

http://social.msdn.microsoft.com/Forums/en-US/22302a07-d599-46c5-be19-6164156e7762/reading-a-csv-file-with-embedded-periods-in-the-file-name-using-oledbcommand?forum=adodotnetdataproviders suggests using the API call GetShortPathName to use the old MS-DOS 8.3 version of the filename.

Imports System.IO
Imports System.Data.OleDb
Imports System.Runtime.InteropServices 'Add this line'

Public Class Form1
  'Add the following line'
  Declare Unicode Function GetShortPathName Lib "kernel32.dll" Alias "GetShortPathNameW" (ByVal longPath As String, <MarshalAs(UnmanagedType.LPTStr)> ByVal ShortPath As System.Text.StringBuilder, <MarshalAs(UnmanagedType.U4)> ByVal bufferSize As Integer) As Integer

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dtb As DataTable = GetAllData("C:JunkABC Test bank Trail Feb 2014 $ 18083.65.csv")
    DataGridView1.DataSource = dtb
  End Sub

  Public Function GetAllData(ByVal FileName As String) As DataTable
    Try
      Dim dt As New DataTable
      Dim strcnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Path.GetDirectoryName(FileName) & "';Extended Properties=""text;HDR=No;FMT=Delimited"""
      Using con As New OleDbConnection With {.ConnectionString = strcnn}
        con.Open()
        Dim SQLAdapter As OleDbDataAdapter = New OleDbDataAdapter()
        If InStr(FileName, ".") <> InStrRev(FileName, ".") Then   'Add these lines'
          Dim sb As New System.Text.StringBuilder(256)            'Add these lines'
          Call GetShortPathName(FileName, sb, 256)                'Add these lines'
          FileName = sb.ToString                                  'Add these lines'
        End If                                                    'Add these lines'
        Dim strcmd As String = "SELECT * FROM [" + Path.GetFileName(FileName) + "]"
        Dim cmd As OleDbCommand = New OleDbCommand(strcmd, con)
        SQLAdapter.SelectCommand = cmd
        SQLAdapter.Fill(dt)
      End Using
      Return dt
    Catch
      Return Nothing
    End Try
  End Function
End Class