Importing Excel to SQLCE

Question

I have a table in excel format (2007 but I can save as anything below that, naturally), and I have an SQL Compact Edition 3.5 SP1 Database table with corresponding columns. I simply want to populate the SQLCE table with the data from the excel file. The data consists of strings and integers only.

I tried this utility to no avail, I also tried this SQL script but it won't work since BULK INSERT is not supported in SQLCE. I also found this Microsoft tutorial but I am basically clueless when it comes to SQL, providers and the like...

Thanks !

Edit: I came up with the following code (via this post)

        var fileName = string.Format("{0}\\DataValues.xls", Directory.GetCurrentDirectory());
        var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
        var ds = new DataSet();

        adapter.Fill(ds, "anyNameHere");

        DataTable dataTable = ds.Tables["anyNameHere"];

        foreach (DataRow row in dataTable.Rows)
            _entities.MyObjectSet.AddObject(new MyObject()
            {
                Foo= (string)row[1],
                Bar= Convert.ToInt32(row[2]),
            });

        _entities.SaveChanges();

But SaveChanges() crashes with "Server-generated keys and server-generated values are not supported by SQL Server Compact". I tried the workaround of changing all Id types to GUID(=uniqueidentifier) but I still get the message

EDIT2: I changed the StoreGeneratedPattern property to "None" and now it works. An interesting thing to note is that the SDF file is copied to the runtime directory, and it is that copy that is altered, so you won't see the changes in the SDF linked in your project - you'll have to copy it manually

My question still remains - is there an SQLCE equivalent to the following ?

BULK 
INSERT YourDestinationTable
        FROM 'D:\YourFile.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

Or even better - something like Microsoft's SQL Server Import and Export Wizard that can export to SQLCE ?

Answer 1

I would avoid using the EF (at least for this).

You haven't posted your database schema, but have a look at this and this.

Although EF supports it in general, CE does not, so you will need to un-mark it as server-assigned and assign it when the object is created.