Exporting data to a csv file with ASP

Moe_213
Moe_213 used Ask the Experts™
on
I need to simply export all data from a database to a csv file. The only problem i'm having is i don't know how to handle special characters that are contained within some of the fields.

Specifically, but not limited to, commas. When a field contains commas the csv file gets distored because extra fields are inserted.

I've included an abreviated version of my code. Its really simple and generates a file named output.csv. What i have done is added extra quotes around one of the field names (ProductName) which often contains commas. But it seems like a bandaid fix.

I'm wondering, is there something i should do to replace the commas that would still allow the csv file to be used by others (this csv file will have to be imported into another database so i can't manipulate the data too much). Is there a way to use ASCII characters or something - i'm really not sure!

Thanks very much again,
set rsp = Server.CreateObject("ADODB.Recordset")
	rsp.Open "SELECT * FROM ProductOrders", connectionSQL, adOpenForwardOnly, adLockOptimistic
 
	WHILE NOT rsp.EOF
		response.write(rsp("OrderNumber")&","&rsp("SessionId")&","&rsp("ConfirmationCode")&","&rsp("Date")&","&formatcurrency(rsp("Price"))&","""&rsp("ProductName")&""","&rsp("Tracking_Number")&","&rsp("Permit_Status")&"" & vbCrLf)
		rsp.MoveNext
	WEND
 
Response.addHeader "content-type","text/plain"
Response.addHeader "content-disposition","filename=Output.csv"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think the solution you are using is already a good solution considering the possibility of csv.

Consider also that the separator in a csv depends on the windows regional settings on your pc.
In some locale is the semi-colon  (in some language you use comma as the decimal point)

The point in this is that you can use any separator you like provided that you set the same list separator in your regional options.

Author

Commented:
Thanks. I essentially left the export process alone but used some regular expressions to eliminate any potential character problems before it gets into the database. I think i was over thinking this one... Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial