Hi,
I'm looking for the best method for importing csv or excel file to SQL Server 2005 using .net MVC.
Thank you.
views:
239answers:
2
+3
A:
There's a really good library called FileHelpers which is a) 100% free, b) fully in C#, and it can easily import any kind of text-based file - comma-separated, tab-separated, fixed width and so on.
You should have no trouble using this to load your CSV file into in-memory objects and storing those in SQL Server using ADO.NET.
In FileHelpers, you first need to have a class that describes your data, e.g. a "Customer" class (or whatever it is you're importing).
Then, you can import a file using code something like this:
FileHelperEngine<Customer> engine = new FileHelperEngine<Customer>();
Customer[] dataLoaded = engine.ReadFile(fileName);
Once you have your array of customers, you can either just iterate through that and save the data (typically inside a transaction) with e.g. a stored procedure or a ad-hoc SQL query:
using(TransactionScope ts = new TransactionScope())
{
foreach(Customer c in dataLoadad)
{
SaveCustomer(c);
}
ts.Complete();
}
or you could convert the customer array to a DataTable and use SqlBulkCopy to bulk insert that into your SQL Server database - there are lots of options!
Marc
UPDATE:
Do you have a [DelimitedRecord] or another of those attributes on your BlackListDevice class?
marc_s
2009-10-13 10:36:57
Hi, thanks for the reply. I had a look at the file helper. I'm using ActiveRecord and NHibernate. I get this error The record class don't have records.on this line FileHelperEngine engine = new FileHelperEngine(typeof(BlackListDevice));Thanks again for your help.
Beaker
2009-10-13 11:55:42
Yeah, my class looks like this [DelimitedRecord(" ")] [ActiveRecord] public class BlackListDevice: Identified { [Property] public virtual string DeviceReference { get; set; } }
Beaker
2009-10-13 13:02:03
And when do you get this error? When compiling? when running? Does your file really contain any data, and does it contain data that matches your record structure?
marc_s
2009-10-13 15:52:36
Is this really advisable if you have say 1,000,000 records or more?
Jon
2009-12-14 19:47:49
No, if you have that many records, I'd probably find a way to use one of the bulk copy methods. But that's rare - typically, you have a couple hundred or couple thousand at most - for those 80-90% cases, FileHelpers works like a charm.
marc_s
2009-12-14 19:58:28
A:
Fixed this by adding a seperate class for file uploads, works like a charm using the FileHelper.
Cheers Marc.
Beaker
2009-10-14 15:03:06