Importing comma delimited file

[Elmo Watson]

I've been asked to develop a semi-automated type situation where we have a
database table (sql server) and periodically, there will be a comma
delimited file from which we need to import the data, replacing the old.

I naurally know that we can use [truncate tablename] to kill the other data,
but does anyone have any examples of importing a comma delimited file into
SQL Server with ASP?

 

[Bob Barrows [MVP]]

I've been asked to develop a semi-automated type situation where we
have a database table (sql server) and periodically, there will be a
comma delimited file from which we need to import the data, replacing
the old.

I naurally know that we can use [truncate tablename] to kill the
other data, but does anyone have any examples of importing a comma
delimited file into SQL Server with ASP?

Don't do it wit ASP. Use SQL Server's builtin import program: DTS. You can
schedule a DTS package using SQL Agent. A DTS package can check a directory
for the existence of a file, import the file if it's found and delete it.
There is no need to get ASP involved with this process.

See www.sqldts.com for pointers.

HTH,
Bob Barrows

 

[Bullschmidt]

You can connect to a csv file (which can be opened in Excel and which an
Excel file can be converted into) in good form just as you can to a
regular database.

The .csv file needs to be uploaded to the server.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within
this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForTextFi
les

And based on the above link realize that the actual filename does NOT go
in the connection string - rather it goes in the SQL statement
(definitely a little tricky).

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

<<
I've been asked to develop a semi-automated type situation where we have
a
database table (sql server) and periodically, there will be a comma
delimited file from which we need to import the data, replacing the old.

I naurally know that we can use [truncate tablename] to kill the other
data,
but does anyone have any examples of importing a comma delimited file
into
SQL Server with ASP?

 

[Mark J. McGinty]

I've been asked to develop a semi-automated type situation where we
have a database table (sql server) and periodically, there will be a
comma delimited file from which we need to import the data, replacing
the old.

I naurally know that we can use [truncate tablename] to kill the
other data, but does anyone have any examples of importing a comma
delimited file into SQL Server with ASP?

Don't do it wit ASP. Use SQL Server's builtin import program: DTS. You can
schedule a DTS package using SQL Agent. A DTS package can check a
directory for the existence of a file, import the file if it's found and
delete it. There is no need to get ASP involved with this process.

I totally agree ASP is the wrong place to do this. Here's an alternative to
DTS packages that I've found to be a little more flexible:

Create a subdirectory for misc CSV files -- I intuitively called mine C:\CSV
Create a DSN using the Text driver and the C:\CSV directory
Create a Linked Server in Enterprise Manager, based on the DSN -- I called
mine CSV (catchy names, yeah?)
The linked server lists any files that are in that directory as tables

To access any of the files as a table, the SQL, let's say for a file called
newdata.csv, is:

SELECT * FROM CSV.[c:\csv]..[newdata.csv] newdata

(Note the 4-part name.) You can then join/update/insert using the data in
the text file directly, as if it were a table, without constructing DTS
packages, creating temp tables, etc.

Here's the kinky part: before this will work you need to call
sp_serveroption to set the 'data access' option. The error message that's
returned if you don't almost seems too dumb to be real, something like,
"Server 'ServerName' is not set for data access." The first time I saw that
I thought to myself, "well then what damn good is it?" But it's just
bizarre semantics, apparently not to be taken litteraly.

-Mark


btw, Bob (hope you're well, btw) I recently bought a code signing cert, and
as such I now have a signed version of my HighResTimer object. I also
marked it safe for scripting to make it more usable in client-side script,
and I removed the early interfaces, leaving only one, both-threaded object
interface for it to support. I find I actually use it from time to time (no
pun) it takes a whole bunch of the crap-shoot... In any case I'd be
interested to know whether or not you'd had occasion to use it. If you'd
care to discuss further, let's start a new thread.