. change to # when reading data from excell file

Question

Hi,I use the following code to read excel data into a data table , however the column name P.O.BOX has changed to P#O#BOX in the data table.Can anyone shed some light on why this is happening?

Dim FileName As String = "C:\abc.xls"
    Dim conn As Data.OleDb.OleDbConnection = Nothing
    Dim dt As New DataTable

    Try
        Try
            Try
                conn = New Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", FileName))
                conn.Open()
            Catch exOleDB As System.Data.OleDb.OleDbException
                If exOleDB.ErrorCode <> -2147467259 Then Throw exOleDB
                'try to open an Excel 2007 file
                conn = New Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", FileName))
                conn.Open()
            End Try
        Catch ex As Exception
            Throw New Exception("Can't read the import file. Try saving the file as an Excel 97-2003 Workbook (.xls). Also check the file permissions and impersonation settings.", ex)
        End Try
        Using selectedCMD As New Data.OleDb.OleDbCommand("select * from [Users$]", conn)
            Using da As New Data.OleDb.OleDbDataAdapter
                da.SelectCommand = selectedCMD
                da.Fill(dt)
            End Using
        End Using
    Finally
        If conn IsNot Nothing Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
    End Try
    Return dt

Answer 1

You will need to handle the population of your dataset manually instead of using fill(). You can use OpenSchema to fetch the names of the worksheets and columns in Excel to construct a SQL statement that qualifies all the object names.

More details here

Answer 2

The periods "." in P.O.Box are not valid SQL column names so the Data Adapter has made them compliant. You'll have to lookup the why's and what's of it