none
OLEDB with special characters RRS feed

  • Question

  •  

    hello,

     

    i'm trying to import an excel file and save it as a datatable.

    everything runs ok but when there's a column name (in my excel file) containing the character # or % those characters don't appear in the imported data's columns names.

     

    any suggestions?!

     

    thanks

     

     

    Thursday, February 14, 2008 2:02 PM

All replies

  • Can you use an alias for the column name in your SELECT statement? In general special characters are not supported in column names in more traditional databases.

     

    Thursday, February 14, 2008 3:40 PM
  • i'm working with OleDbDataAdapter to retrieve data from the excel file. i'm in a bit of a problem 'cause i can't change the column's names. it comes from a machine the provides columns' names like : "Sample #". ("Sample #" shows in my application as "Sample ").

     

     

    Any idea ?!?!

     

     

    Thursday, February 14, 2008 4:16 PM
  • Could you post your code? I just tried using a # sign symbol and didn't encounter any problem.

     

    Thursday, February 14, 2008 9:47 PM
  • Please help...

     

    Connection String:

    static string oledbProviderString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes\"";

     

    Importing Code:

    using (OleDbConnection oledbConnection = new OleDbConnection(string.Format(oledbProviderString, excelFilePath)))

    {

    string sqlStatement = "Select * from [" + sheetName + "]";

    //open the connection

    oledbConnection.Open();

     

    //Create an OleDbDataAdapter for our connection

    OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStatement, oledbConnection);

     

    //Create a DataTable for our data

    DataTable table = new DataTable();

     

    adapter.Fill(table);

     

    //close the connection

    oledbConnection.Close();

    }

     

     

    my problem, again, is that the table's columns' names don't contain special characters (like # or %) after i import the data from excel.

    Sunday, February 17, 2008 5:56 AM
  • Did you get a resolution for this issues

     

    I am getting a similar issue where the data which contains Special characters is being retrieved incorecctly

     

    For Eg

    0% is displayed as 0

    75% is displayed as .75

     

    Please let me know

    Wednesday, April 23, 2008 6:48 AM
  • Does this occur when reading from an Excel Worksheet or importing into an Excel Worksheet?
    Wednesday, April 23, 2008 12:15 PM
  • This occuring while importing into excel
    i am for now replacing special charaters with their hexadecimal values
    Tuesday, February 2, 2010 8:14 AM

  • For

     

    i = 0 To dtExport.Columns.Count - 1

    sHeaders(i) = dtExport.Columns(i).ColumnName

    sFileds(i) = ReplaceSpclChars(dtExport.Columns(i).ColumnName)

     

    Next


     

    Private Function ReplaceSpclChars(ByVal fieldName As String) As String

    fieldName = fieldName.Replace(

    " ", "_x0020_")

    fieldName = fieldName.Replace(

    "%", "_x0025_")

    fieldName = fieldName.Replace(

    "#", "_x0023_")

    fieldName = fieldName.Replace(

    "&", "_x0026_")

    fieldName = fieldName.Replace(

    "/", "_x002F_")

    fieldName = fieldName.Replace(

    "?", "_x003F_")

    fieldName = fieldName.Replace(

    "(", "_x0028_")

    fieldName = fieldName.Replace(

    ")", "_x0029_")

     

    Return fieldName

     

    End Function

    Tuesday, February 2, 2010 8:15 AM