CSV to SQL Best Practice??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an web app where users need to be able to upload data from a .csv file into a SQL database. What is the best practice for doing this?

My application is a .net web app written in C#, with a SQL Server 2000 database. The web server and database servers are located on different boxes. For security reasons the SQL box does not have FTP or any way for a web user to upload files to it.

From the SQL perspective, it seem like using the bcp Utility or BULK INSERT would be the method of choice, but don't you need to have the data file on the SQL box to do that?
 
Are the web server and DB server on the same network? Or could you map a
virtual directory over the place on the web server where the CSV will be
sitting? You could hit it from there and use BULK INsert.... IMHO, bulk
insert is a great way to load large datafiles and is tailor made for this
type of task. Similarly, you provided you have the permissions and
visibility, you could use a DTS package and schedule things at regular
intervals.

I'd probably go with the bulk insert though.

HTH,

Bill
Dan said:
I have an web app where users need to be able to upload data from a .csv
Click to expand...
file into a SQL database. What is the best practice for doing this?
My application is a .net web app written in C#, with a SQL Server 2000
Click to expand...
database. The web server and database servers are located on different
boxes. For security reasons the SQL box does not have FTP or any way for a
web user to upload files to it.
From the SQL perspective, it seem like using the bcp Utility or BULK
Click to expand...
INSERT would be the method of choice, but don't you need to have the data
file on the SQL box to do that?
 
Hello Dan


You can specify a network share as the source of a data file. So your web
app can store the file locally on a shared drive location and then you can
issue your bulk insert to SQL-Server using a UNC path.



--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

Dan said:
I have an web app where users need to be able to upload data from a .csv
Click to expand...
file into a SQL database. What is the best practice for doing this?
My application is a .net web app written in C#, with a SQL Server 2000
Click to expand...
database. The web server and database servers are located on different
boxes. For security reasons the SQL box does not have FTP or any way for a
web user to upload files to it.
From the SQL perspective, it seem like using the bcp Utility or BULK
Click to expand...
INSERT would be the method of choice, but don't you need to have the data
file on the SQL box to do that?
 
Dan said:
I have an web app where users need to be able to upload data from a .csv
Click to expand...
file into a SQL database. What is the best practice for doing this?
My application is a .net web app written in C#, with a SQL Server 2000
Click to expand...
database. The web server and database servers are located on different
boxes. For security reasons the SQL box does not have FTP or any way for a
web user to upload files to it.
From the SQL perspective, it seem like using the bcp Utility or BULK
Click to expand...
INSERT would be the method of choice, but don't you need to have the data
file on the SQL box to do that?

As others have pointed out you can use windows file shares instead of FTP.
Or you could enable FTP.
In either case you will have some challenges.

If you expect the SQL Server to read the file from the remote drive you will
have to make sure that SQL runs under an account that has rights to do this.

If you want to push the file to the SQL Server, then your web app will have
to run under an account with rights to do that.

In either case, you will have to connect to SQL with an account which has
rights to BULK INSERT.

A fast alternative to BULK INSERT is the linked server functionality (Also
OPENROWSET for ad-hoc connections).
A linked table insert is nearly as fast as a bulk insert, but you still need
to get the file somewhere SQL can see it.

These requirements will stop you dead in many scenarios. In that case, you
will have to load the file locally and use INSERT's to load it. You can get
around parsing the file yourself by opening and ADO.NET connection against
it using either the text ODBC driver, or Jet. THen you can fill a
datatable, and update a SQLDataAdapter.

If your CSV is under a few thousand rows, then this should be Ok.

David
 
Back
Top