Click here to Skip to main content
14,977,259 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want a simple and organized way to migrate my data from multiple excel sheets to one single SQL compact 3.5 database file
What I did to work this thing out was trying to load the excel sheet into a datagridview and then save it to the sql CE database, but it always gives error and duplicates cells in the database

EDIT:
This is the working code as per Maciej Los Insert data from excel sheet to SQL CE 3.5 database[^] answer guidance

C#
try
            {
                String sheetN = "Sheet1";
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                "E:\\test.xls" +
                                ";Extended Properties='Excel 8.0;HDR=YES;';";

                OleDbConnection EXcon = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + sheetN + "$]", EXcon);
                EXcon.Open();

                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();
                sda.Fill(data);
                dataGridView1.DataSource = data;

                if (data.Rows.Count >= 1)
                {
                    MessageBox.Show("We have Rows");
                }


                string conString = (@"Data Source=E:\\test.sdf;Persist Security Info=True;");
                
                try
                {
                    using (SqlCeConnection con = new SqlCeConnection(conString))
                    {
                        con.Open();


                        SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
                        using (SqlCeBulkCopy bc = new SqlCeBulkCopy(con, options))
                        {
                            bc.DestinationTableName = "Niaba";
                            bc.WriteToServer(data);
                        }
                        con.Close();
                    }
                    MessageBox.Show("All data copied to database successfully");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
Posted
Updated 2-Oct-15 1:57am
v2
Comments
Maciej Los 2-Oct-15 2:20am
   
What's error message? Which line?

1 solution

I'd suggest to use SqlCeBulkCopy class[^] (from codeplex), which provides several options to copy data between databases in a safe way.

How to use it?
1) download and install this nuget packet[^],
2) create new project,
3) create new OleDbConnection[^] to load data from Excel into DataTable[^] object (using OleDbCommand[^] + OleDbDataReader[^]),
4) close OleDbConnection (do NOT delete DataTable object, you'll use it in the next step!),
5) create new SqlCeConnection[^],
6) create new SqlCeBulkCopy class and use WriteToServer method. Pass DataTable object as an input parameter,
7) close SqlCeConnection.

That's all!


For further details, please download the documentation[^].
   
v2
Comments
Sherif Kamel 2-Oct-15 7:52am
   
Thanks for the guide , it's working smoothly as it should now.
I edited the main question with the final code
Maciej Los 2-Oct-15 8:15am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900