Whats is the best way of getting a csv file from a directory?

Question

Whats is the best way of getting a csv file from a directory and read the data in the file and insert the data to database?(My prefer language is VB.net, but I dont mind C#)

do you want to read data without storing the file on disk as in most of the cases we save the file on disk just to read the data from file which we can do by stream as well and avoid File IO cost, let me know if you need code to implement it — rajansoft1, Jun 03 '13 at 09:33
Check this link for reading .csv files. http://stackoverflow.com/questions/5282999/c-net-reading-csv-file — vijay, Jun 03 '13 at 09:34
Which database? Oracle for example has external tables that do this. In SQL Server you can use a simple Integration package (SSIS). I should consider to write a custom program only after considering available DB tools. — Andrea Colleoni, Jun 03 '13 at 09:38
Why did you tag the question with asp.net? Is the cvs file uploading from a client? — Emond, Jun 03 '13 at 09:38
rajansoft1: Im not sure if I need to store the file, I just need to know the best way of doing this. Andrea Colleoni: the database is SQL Server — Diana, Jun 03 '13 at 09:48

score 2 · Accepted Answer · answered Jun 03 '13 at 09:34

2

Two ways: Bulk copy. http://msdn.microsoft.com/en-us/library/ms188365.aspx SqlBulkCopy Class: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Both are very fast

answered Jun 03 '13 at 09:34

kostas ch.

1,960
1
17
30

+1 Repeatedly I rediscover this and kick myself that I didn't find it before. – kͩeͣmͮpͥ ͩ Jun 03 '13 at 09:44
@user2290688 : can you explain it a little bit please? – Diana Jun 03 '13 at 10:03
For .net http://www.codeproject.com/search.aspx?q=sqlbulkcopy&x=-1327&y=-147&sbo=kw – kostas ch. Jun 03 '13 at 10:09
BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail FROM 'f:\orders\lineitem.csv' WITH ( FIELDTERMINATOR =' ;', ROWTERMINATOR = ':\n' ); – kostas ch. Jun 03 '13 at 10:10
In sql BULK INSERT you have to have the same number of columns of your file with the table. – kostas ch. Jun 03 '13 at 10:12
You have to do a lilte research on what you need. These 2, on my opoinion are the best practices. – kostas ch. Jun 03 '13 at 10:14
@kostas ch: What about SSIS? It seems like the best route. – Diana Jun 03 '13 at 12:33
It is ok, but on my opinion is more effort. I have not use it much. – kostas ch. Jun 03 '13 at 12:39
**** Final solution **** As @Andrea Colleoni Said the best solution to this is to create a SSIS package. It works so well and you can set up a SQL Agent Job to run the SSIS Package every an hour. – Diana Jun 04 '13 at 13:18

Whats is the best way of getting a csv file from a directory?

1 Answers1