Geek Answers Handbook

.CSV to SQL CE table?

Is there a way to quickly import data from .csv or tab, designated as .txt, into a SQL Compact Edition 3.5 table?

I have a large amount of data that is impractical for manual input.

I know that I can use the BULK INSERT function if I want to import into a SQL Server database, but this method does not work in SQL CE.

I am using Visual Studio 2010 and I have SQL Server Management Studio installed.

Any help would be appreciated!

+4
source share
5 answers

You can use my VS add-in, which generates INSERT statements based on the CSV file: http://sqlcetoolbox.codeplex.com

+4
source

Maybe simpler the better. There is nothing easier than your own code, which can be easily deployed. You could even do it dynamically if you want, but not necessarily.

var stuff = from l in File.ReadLines(filename) let x = l.Split(new [] {',', ' '}, StringSplitOptions.RemoveEmptyEntries) .Skip(1) .Select(s => int.Parse(s)) select new { Sum = x.Sum(), Average = x.Average() };

see: reading CSV using LINQ

+2
source

If you have Microsoft Access, you can import CSV data or .txt delimited data as a new table, and then expand to an SQL database. You can also create a linked table and copy the data (for example, from Microsoft Excel), and it tends to be efficient and reliable (throwing errors into a separate table that you can view).

+1
source

There is no direct way to do this, you will need to read each line in the file and paste it one by one into SQLCE. There are some messages on it before, people use the C # program to read a file in a DataTable. If you know C #, it's pretty simple to configure and run it.

Bulk insert from DataTable to SQLCE DataSource

http://ruudvanderlinden.com/2010/10/13/bulk-insert-into-sql-ce-in-c/

Just noticed that C # is one of the tags in your question: D

+1
source

My csv selection tool is powershell, as it has a built-in import-csv command. This is a fantastic glue for this kind of thing.

Here's a link in which the developer imports csv and converts it into an insert script. http://allen-mack.blogspot.com/2008/02/powershell-convert-csv-to-sql-insert.html

Running the script from a link (note: powershell gives you tab completion so you can use it to help with file paths during input):

  • Create a file called Import-File.ps1 and copy the contents of the script from the link into it.
  • Run powershell
  • Enter "set-executionpolicy remotesigned" (note: this weakens the security of your system only a little, but the default settings will not allow you to run any scripts)
  • Go to the directory with your script and import the file
  • Enter '. \ Import-file.ps1. \ Importfile.csv'
  • Press Enter; voila, you must have a nested sql script in the same directoy (i.e., importfile.sql) in our case)

Finally, since you can create .net objects from within powershell, you can modify the script and do a few things, such as inserting data directly into the database.

+1
source

More articles:


All Articles