5

Is there any way to quickly import data from a .csv or tab delineated .txt into a SQL Compact Edition 3.5 Table?

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

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

I use visual studio 2010 and I have SQL Server Management Studio installed.

Any help will be appreciated!

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
Tony Wu
  • 329
  • 1
  • 6
  • 15
  • possible duplicate of [Bulk insert from DataTable to SQLCE DataSource](http://stackoverflow.com/questions/5961535/bulk-insert-from-datatable-to-sqlce-datasource) – Ken White Jun 15 '11 at 01:58

5 Answers5

5

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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
2

Maybe simpler is better. Nothing easier than your own code which can be expanded very easily. You could even have it build the table dynamically if you wanted but probably not necessary.

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: Read Csv using LINQ

Palle Due
  • 5,929
  • 4
  • 17
  • 32
phillip
  • 2,618
  • 19
  • 22
1

If you have Microsoft Access, you can import .csv or delimited .txt data as a new table then upsize to SQL database. Also you can create a linked table and copy-paste the data (say from Microsoft Excel) and it tends to be efficient and reliable (throwing errors into a separate table that you can review).

mellamokb
  • 56,094
  • 12
  • 110
  • 136
1

There is no straight way of doing it, you will have to read each line in the file and insert one by one to SQLCE. There's some posts on it before, folks using C# program to read the file to a DataTable. If you know C# its fairly simple to setup and run.

Bulk insert from DataTable to SQLCE DataSource

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

Oops just noticed that c# is one of the tags in your question :D

Community
  • 1
  • 1
hyperkittie
  • 641
  • 6
  • 16
1

My tool of choice when dealing with csv is powershell since it has an import-csv command built in. It's fantastic glue for this kind of stuff.

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

To run the script from the link (note: powershell gives you tab completion so you can use it to help with file paths while you are typing):

  1. Create a file named something like Import-File.ps1 and copy the contents of the script from the link into it.
  2. Start powershell
  3. Type 'set-executionpolicy remotesigned' (note: this is loosening security of your system just a tad; but the default settings won't let you run any scripts)
  4. Navigate to the directory with both your script and import file
  5. Type '.\import-file.ps1 .\importfile.csv'
  6. Hit enter; voila, you should have an insert sql script in the same directoy (i.e., 'importfile.sql' in our case)

Finally, since you can instantiate .net objects from within powershell, you could alter the script and do a number of things like insert data directly into a database.

Jason
  • 4,897
  • 2
  • 33
  • 40