Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I'm trying to copy data from Excel into a new table in SQL Server.

The SQL code I'm using works in Server Manager, copying data into a newly created table & does seem to work here, except the new table doesn't appear in my Database and running the code again seems to produce an "Table already exists" error.

Any ideas about what I'm doing wrong?

Code as follows:

 try
        {
            string sqlConnectionString = string.Format("Data Source=XXXX\\SQLEXPRESS;Initial Catalog=DbName;" + "Integrated Security=SSPI; User Instance=False");
            string excelConnectionString = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\\user\\ExcelFileName.xlsx; Extended Properties ='Excel 12.0 xml; HDR=YES;IMEX=1'");
            using (SqlConnection SQLconnection =
                new SqlConnection(sqlConnectionString))
            using (OleDbConnection connection =
                new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                ("SELECT * INTO dbo.newTable FROM [SheetName$];", connection);

                connection.Open();
                using (OleDbDataReader dr = command.ExecuteReader())

                using (SqlBulkCopy bulkcopy =
                    new SqlBulkCopy(SQLconnection))
                {
                    bulkcopy.DestinationTableName = "dbo.newTable";
                    bulkcopy.WriteToServer(dr);
                    Console.WriteLine("The data has been exported from Excel to SQL.");
                    Console.ReadLine();
                }
                connection.Close();
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.ReadLine();
        }
share|improve this question
 
What if you don't use dbo. as a prefix for table name? –  Yuriy Galanter Sep 16 '13 at 14:40
 
If I do that, the code runs OK without a 'Table already exists' error message but I still can't see the new table in SQLDb using SQL Server management studio. –  trymriverman Sep 17 '13 at 10:09
 
Which user are you logging in as to SQL Server Management Studio? I bet it is bound to a differeny schema. Can you use same credentials as you use in connecting in code? –  Yuriy Galanter Sep 17 '13 at 13:49
 
It must be something like that but what exactly, isn't obvious - when I create a table using SQL Server management studio, some C# code I wrote, using the same sqlConnection, successfully deletes it from my SQLDb. I've even added 'User Id & Password to the connection string but still the same error 'Table X already exists' but it doesn't appear in the Db. Queries using TempDb, Master etc also don't recognise the table as existing. –  trymriverman Sep 19 '13 at 12:48
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.