Convert string to nvarchar (import excel to mssql)

Question

I have been trying to find a way to convert data type string to nvarchar while I am importing data from an excel spreadsheet. So far I have this piece of code to import the data through C#.

// Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
    string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Folder\\Folder\\Excel_File.xls ;Extended Properties=Excel 8.0";


    // Create Connection to Excel Workbook
    using (OleDbConnection connection =
                 new OleDbConnection(excelConnectionString))
    {
        OleDbCommand command = new OleDbCommand
                ("Select * FROM [Sheet1$]", connection);

        connection.Open();

        // Create DbDataReader to Data Worksheet
        using (DbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = "Data Source=localhost;Initial Catalog=DatabaseName;Integrated Security=True";

            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "Table_Name";
                bulkCopy.WriteToServer(dr);
                MessageBox.Show("Data Exported To Sql Server Successfully");
            }
        }

I have looked for ways to convert from string to nvarchar but the only examples that I have found are only for converting string collected from textboxes or through consoles. I do have one column though in date type but I have compensated for it in MSSQL.

If anyone could give me a sample code of what I should be doing it would be greatly appreciated.

Answer 1

My suspicion is that you are 'tripping' over some hidden characters that Excel is inserting in your text strings. Try logging the imported data to ensure that it's clean.