How to parse comma-delimited file with ASP

[kevinj]

Is anyone familiar with a way to parse a comma-delimited file using ASP? I want to take the information and set variables equal to each of the comma-delimited fields and write them to a database. The file is sent from my credit card processor by HTML post.

Thanks.

[BlobFisk]

Is something like this what you were looking for? This code writes to a text file, but it's a variation on a theme.

<%@ Language=VBScript %>
<% option explicit %>
<%
Dim PageName
Dim name
Dim company
Dim course
Dim datetime
KioskName= "This Page"
course="Some Course"
datetime= FormatDateTime(now(), VBGeneralDate)
'get the registration details from the form
name = Request.Form("name")
company = Request.Form("company")
Dim fs
Dim ts
set fs = Server.CreateObject("Scripting.FileSystemObject")
set ts = fs.OpenTextFile("d:\inetpub\wwwroot\your\path\logfile.txt", 8)
ts.WriteLine PageName & "," & name & "," & company & "," & course & "," & datetime & Chr(13)
ts.Close
set ts = Nothing
set fs = Nothing
%>

[korkus2000]

If Bob's example isn't what you need you can try this one.

<%
'Open Database for whatever one you are using
'This should split the list into an array
Dim strListForCC
Dim aCCList
Dim iLoop
strListForCC = Request("FeildNameOfPostWithCommaList")
aCCList = split(strListForCC, ",")
'This should trim the white space out of your array
For iLoop = LBound(aCCList) to UBound(aCCList)
aCCList(iLoop) = Trim(aCCList(iLoop))
Next
'Loops through your array for output
For iLoop = LBound(aCCList) to UBound(aCCList)
'Use your database entry code here instead of response.write
Response.Write aCCList(iLoop) & "<br />"
Next
%>

[kevinj]

Here is a sample of the file that I get returned. I just need to parse it so I can take the data and write it to a database. Will the code you've shown me do that for me?

1,1,1,This transaction has been approved.,000000,P,0,133059663221367,Conference Preregistration,60.00,CC,auth_capture,,Kevin,Smith,,123 Park Place,Las Vegas,Nevada,00100,United States,1231231234,,[e-mail],,,,,,,,,,,,,,08406632FCBE92B3D338123A08801B09,,,,,,Submit

[aspdaddy]

thanx korkus!

Hi Kevinj,
You can use the vbScript split function to create an array from conmma delimited string. eg :

myArr=split(objTextStream.ReadAll,",")

[kevinj]

Thanks for your help. It appears that I have to write a script to include the parsing code you suggested. Any thoughts where I can go to find a simple example of a script like this?

[aspdaddy]

Hi Kev,
you could try aspin.com, though I think the script taht you need is quite custom.

I'll see if I can help out. I'd begin buy defining the required fields as constants, using the index positions as they appear in the output:

<%
const CC_NUMBER=8
const DESCRIPTION=9 
' etc..

Then define the filepath to your csv file , create filesystem & textstream objects and open your file:

dim sPath,objFso
strPath = Server.MapPath("/secure/myFile.dat")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTxtStream = objFso.OpenTextFile( strPath )

Next part is to read the data from the file into a string - may as well split that string to an array at the same time!:

dim myArr
myArr=split(objTxtStream.ReadAll,",") 

Next...build an SQL insert statement from the values in the array - using the consts for readabilty:

dim strSQL
strSQL="INSERT INTO [myTable] (fldCC_NUMBER, fldDESCRIPTION) Values ('" & myArr(CC_NUMBER) & "','" & myArr(DESCRIPTION) & "'"

Finally, create the ADO connection and bang the record into the database!

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

May wanna check the result also..

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

One final thing I'll leave for you - just put a loop around the script to process all files in the folder.

Hope this helps.

[kevinj]

Thanks very much for that info. I'm not sure how I would get the path to the csv file. They had me specify a return URL for the response to be sent to. Would the path be the path for that page that I specified and could the page just be a .asp page that has the code you wrote? I realize this may be too much to ask, but I truly appreciate your help on this. I'm working towards a rapidly approaching deadline.

[aspdaddy]

They had me specify a return URL for the response to be sent to

Are you sure its a actual file that the data is stored in, it sounds like they pass control to a confirmation page, and pass the data in the response, like WorldPay callback ?

If this is the case you just need to add the code to your successfull confirmation page,

[kevinj]

Thanks. This is what they say in their developer's guide:

The return page can’t be just a web page unless the server on which it resides can handle a POST to that URL.
It is intended that the URL specified in x_ADC_URL will be a script or something else that can interactively parse the information that is POSTed to it. If a static response is desired for every transaction, that URL can be a plain HTML page, but the merchant's web server will need to be configure to allow the POST method to plain HTML pages

I need to use the data so it looks like it needs to be a script. Any thoughts?

[aspdaddy]

Yep, Its not a file that is created, its just callback. You will need to have a single asp page to do the processing , then write some confirmation to the screen.

[kevinj]

What code would I use to get the string? Request.form wouldn't work in this situation.

Kevin