How to parse comma-delimited file with ASP

Referencing Older Message

[ganker]

I am referencing an old post but need some help implementing the code referenced. old post is:
[webmasterworld.com...]

I have a client who wants to send data in a CSV file and I would like to insert this to a database.

a sample from the CSV...
loadnumber,OriginCity,OriginState,DestCity,DestState,LoadDate,DeliverDate,Product,Equipment,Miles
185378,SPIRO,OK,POTTSVILLE,AR,6/10/2003 0:00,6/10/2003 0:00,FEEDPHOS,PNE,101.6
185574,WICHITA,KS,SINCLAIR,WY,6/12/2003 0:00,6/14/2003 0:00,CAT,PNE,752.9

This is what code I have so far....(I think the const might be is the problem, I used the column number)

<%
const LoadNumber = 1
const OriginCity = 2
const OriginState = 3
const DestCity = 4
const DestState = 5
const LoadDate = 6
const DeliverDate = 7
const Product = 8
const Equipment = 9
const Miles = 10
%>

<%
dim sPath,objFso
strPath = Server.MapPath("/broker/loads.csv")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTxtStream = objFso.OpenTextFile( strPath )
%>

<%
dim myArr
myArr=split(objTxtStream.ReadAll,",")
%>

<%
Dim oConn, dbPath
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "DRIVER=SQL Server; SERVER=XXXXXX; UID=XXXX; PWD=XXXX; Network Library=XXXXX"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM BrokerLoad"
rs.Open sql, oConn

%>
<%
dim strSQL
strSQL="INSERT INTO BrokerLoad (loadnumber,OriginCity,OriginState,DestCity,DestState,LoadDate,DeliverDate,Product,Equipment,Miles) Values ('" & myArr(loadnumber) & "','" & myArr(OriginCity) & "','" & myArr(OriginState) & "','" & myArr(DestCity) & "','" & myArr(DestState) & "','" & myArr(LoadDate) & "','" & myArr(Product) & "','" & myArr(Equipment) & "','" & myArr(Miles) & "')"
%>

<%
dim objConn
on error resume next
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Execute ( strSQL )
%>

<%
if err.number <> 0 then
Response.Write "Eeek!"
else
Response.Write "Cool bananas"
end if
%>

Your time and help is appreciated!

[musicales]

Hi ganker

Firstly, I can't see the use of the lines
set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM BrokerLoad"
rs.Open sql, oConn

you've opened the rs but not used it later.

Presumably you want to loop through a whole load on inserts,
so you need to loop though the myArr using a for next
as you have 10 fields to retreive in any one insert you need to do a new insert every 10 items in the array I would have thought. I would probably do for i=1 to ubound(myArr) step 10
and then get the fields using i+1, i+2 etc.

You may need to set a high scripttimeout as you'll be doing a lot of inserts.

[ganker]

Hello Musicales, Thanks for the reply. I understand what you are telling me although I am not sure how to script the loop. Could you elaborate a some more about how to loop through the inserts i=1, i+2 etc.

Thanks!

[musicales]

OK something like

for i=0 to ubound(myArr) step 10

strSQL="INSERT INTO BrokerLoad (loadnumber,OriginCity,OriginState,DestCity,DestState,LoadDate,DeliverDate,Product,Equipment,Miles) Values ('" & myArr(i) & "','" & myArr(i+1) & "','" & myArr(i+2) & "','" & myArr(i+3) & "','" & myArr(i+4) & "','" & myArr(i+5) & "','" & myArr(i+6) & "','" & myArr(i+7) & "','" & myArr(i+8) & "','" & myArr(i+9) & "')"

objConn.Execute ( strSQL )

next

this is only advisable if you are certain of the contents of the text file. If you need to validate if you'd need to do that before you build the sql obviously.

eg

if isnull(myarr(i+1)) then myarr(i+1)=0 or whatever

[cococure]

Using SQL Server 2k, you can create a DTS transaction to do a bulk insert, especially if there are hundreds or thousands of records. It can be a one time thing or you can create packages that perform multiple steps and you can even schedule them.

The DTS wizard in Enterprise Manager is pretty easy to figure out. Books Online help a lot too.

HTH