Read CSV to SQL

[Dave Karmens]

Does anyone have an example of how to read a .csv file into a SQL
database with an ASP?

[Bob Barrows]

Dave Karmens wrote:

> Does anyone have an example of how to read a .csv file into a SQL
> database with an ASP?

You would be better off using DTS to do this (bcp if you have a 6.5 server).
Alternatively, with SQL 7 and above, there is a T-SQL command called "Bulk
Insert" which can be called from asp just as any other query would be
called. You should be able to find a lot of information about this command
in SQL Books Online (BOL).

I'm aware that a Google search will yield examples of using ADO or FSO to
open a text file and loop through its contents writing the data to a
database, but those solutions are not likely to perform as well as DTS or
Bulk Insert.

Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Dave Karmens]

Aaaaah... ok, I'll take a look around. Thanks Bob!

Bob Barrows wrote:

> Dave Karmens wrote:
>

>>Does anyone have an example of how to read a .csv file into a SQL
>>database with an ASP?

>
>
> You would be better off using DTS to do this (bcp if you have a 6.5 server).
> Alternatively, with SQL 7 and above, there is a T-SQL command called "Bulk
> Insert" which can be called from asp just as any other query would be
> called. You should be able to find a lot of information about this command
> in SQL Books Online (BOL).
>
> I'm aware that a Google search will yield examples of using ADO or FSO to
> open a text file and loop through its contents writing the data to a
> database, but those solutions are not likely to perform as well as DTS or
> Bulk Insert.
>
> 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:
[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#O[/url]
LEDBProviderForMicrosoftJetText

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
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!