StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImporting Excel to SQL Server CE
text
Body
copied!<p>I have a table in excel format (2007 but I can save as anything below that, naturally), and I have a SQL Server 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.</p> <p>I tried <a href="http://sourceforge.net/projects/sqlceviewer/" rel="nofollow noreferrer">this utility</a> to no avail, I also tried this <a href="https://stackoverflow.com/questions/338776/creating-a-sql-table-from-a-xls-excel-file/343345#343345">SQL script</a> but it won't work since <code>BULK INSERT</code> is not supported in SQL Server CE. I also found this <a href="http://support.microsoft.com/kb/321686" rel="nofollow noreferrer">Microsoft tutorial</a> but I am basically clueless when it comes to SQL, providers and the like...</p> <p>Thanks !</p> <p><strong>Edit:</strong> I came up with the following code (via <a href="https://stackoverflow.com/questions/15828/reading-excel-files-from-c">this post</a>)</p> <pre><code> 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(); </code></pre> <p>But SaveChanges() crashes with "Server-generated keys and server-generated values are not supported by SQL Server Compact". I tried the <a href="https://stackoverflow.com/questions/648449/server-generated-keys-and-server-generated-values-are-not-supported-by-sql-server/648507#648507">workaround</a> of changing all Id types to GUID(=uniqueidentifier) but I still get the message </p> <p><strong>EDIT2:</strong> I changed the StoreGeneratedPattern property to "None" and now it works. An interesting thing to note is that the <code>.SDF</code> file is copied to the runtime directory, and it is that <em>copy</em> that is altered, so you won't see the changes in the <code>.SDF</code> linked in your project - you'll have to copy it manually</p> <p>My question still remains - is there a SQL Server CE equivalent to the following ?</p> <pre><code>BULK INSERT YourDestinationTable FROM 'D:\YourFile.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO </code></pre> <p>Or even better - something like Microsoft's SQL Server Import and Export Wizard that can export to SQL Server CE?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload