none
Help Import Data into SQL using Excel Spreadsheet RRS feed

  • Question

  • I'm getting an error when I try to import my spreadsheet into SQL. I'm not sure what the issue is currently. 

    private bool Import_Data_Into_SQL(string filepath, string SheetName)
            {
                try
                {
                    //declare variables - edit these based on your particular situation 
                    string ssqltable = SheetName;
    
                    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
                    string exceldataquery = "select * from ["+ SheetName + "$]";
                    try
                    {
                        //create our connection strings 
                        string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);
                        string sqlconnectionstring = Properties.Settings.Default.SQL_Connection;
    
                        //series of commands to bulk copy data from the excel file into our sql table 
                        OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                        OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
                        oledbconn.Open();
                        OleDbDataReader dr = oledbcmd.ExecuteReader();
                        SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
                        bulkcopy.DestinationTableName = ssqltable;
    
                        while (dr.Read())
                        {
                            bulkcopy.WriteToServer(dr);
                        }
    
                        dr.Close();
                        oledbconn.Close();
                    }
                    catch (Exception ex)
                    {
                        //handle exception 
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

    Error:

    External table is not in the expected format.

    Line:

    string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);

    Friday, March 16, 2018 4:05 PM

Answers

  • I was able to resolve the issue. You need full name to insert:

    private bool Import_Data_Into_SQL(string filepath, string SheetName)
            {
                try
                {
                    //declare variables - edit these based on your particular situation 
                    string ssqltable = SheetName;
    
                    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
                    string exceldataquery = "select * from [" + SheetName + "$]";
    
                    //create our connection strings 
                    string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + filepath + " '; Extended Properties=Excel 8.0;";
                    //string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);
                    string sqlconnectionstring = Properties.Settings.Default.SQL_Connection;
    
                    //series of commands to bulk copy data from the excel file into our sql table 
                    OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                    OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
                    oledbconn.Open();
                    OleDbDataReader dr = oledbcmd.ExecuteReader();
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
                    bulkcopy.DestinationTableName = "Test_Table.dbo." + ssqltable;
                    //bulkcopy.DestinationTableName = ssqltable;
    
                    while (dr.Read())
                    {
                        bulkcopy.WriteToServer(dr);
                    }
    
                    dr.Close();
                    oledbconn.Close();
    
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

    Corrected line of code:

     bulkcopy.DestinationTableName = "Test_Table.dbo." + ssqltable;

    • Marked as answer by old_School Friday, March 16, 2018 4:40 PM
    Friday, March 16, 2018 4:40 PM

All replies

  • I got that part fixed but now I'm having another issue or rather another error. Its giving me a access error. Here is new code:

    private bool Import_Data_Into_SQL(string filepath, string SheetName)
            {
                try
                {
                    //declare variables - edit these based on your particular situation 
                    string ssqltable = SheetName;
    
                    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
                    string exceldataquery = "select * from [" + SheetName + "$]";
    
                    //create our connection strings 
                    string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + filepath + " '; Extended Properties=Excel 8.0;";
                    //string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);
                    string sqlconnectionstring = Properties.Settings.Default.SQL_Connection;
    
                    //series of commands to bulk copy data from the excel file into our sql table 
                    OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                    OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
                    oledbconn.Open();
                    OleDbDataReader dr = oledbcmd.ExecuteReader();
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
                    bulkcopy.DestinationTableName = ssqltable;
    
                    while (dr.Read())
                    {
                        bulkcopy.WriteToServer(dr);
                    }
    
                    dr.Close();
                    oledbconn.Close();
    
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }


    Corrected line:

    string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + filepath + " '; Extended Properties=Excel 8.0;";
                    //string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);


    New Error:

    {"Cannot access destination table 'Table_1'."}

    Database Structure:

    Database Name: Test_Table

    Table Names: Table_1, Table_2 and Table_3

    Not sure why its giving that access error.

    • Edited by old_School Friday, March 16, 2018 4:18 PM
    Friday, March 16, 2018 4:14 PM
  • I was able to resolve the issue. You need full name to insert:

    private bool Import_Data_Into_SQL(string filepath, string SheetName)
            {
                try
                {
                    //declare variables - edit these based on your particular situation 
                    string ssqltable = SheetName;
    
                    // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
                    string exceldataquery = "select * from [" + SheetName + "$]";
    
                    //create our connection strings 
                    string excelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + filepath + " '; Extended Properties=Excel 8.0;";
                    //string excelconnectionstring = string.Format(@"provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";Extended Properties=Excel 8.0", filepath);
                    string sqlconnectionstring = Properties.Settings.Default.SQL_Connection;
    
                    //series of commands to bulk copy data from the excel file into our sql table 
                    OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                    OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
                    oledbconn.Open();
                    OleDbDataReader dr = oledbcmd.ExecuteReader();
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
                    bulkcopy.DestinationTableName = "Test_Table.dbo." + ssqltable;
                    //bulkcopy.DestinationTableName = ssqltable;
    
                    while (dr.Read())
                    {
                        bulkcopy.WriteToServer(dr);
                    }
    
                    dr.Close();
                    oledbconn.Close();
    
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

    Corrected line of code:

     bulkcopy.DestinationTableName = "Test_Table.dbo." + ssqltable;

    • Marked as answer by old_School Friday, March 16, 2018 4:40 PM
    Friday, March 16, 2018 4:40 PM