Exporting to CSV issue

[Joey Martin]

I am using a SQL 2000 server and am trying to export results from a
query to a text file. The code is working, expect my CONTACTNAME field
has a comma, and my export is splitting that into 2 columns when I only
want it to be one.

Here is my code:
set conn = CreateObject("ADODB.Connection")
conn.open DSNtemp
set rs = conn.execute("select
contactname,contactnumber,contactnumber1,contactemail,address,city,state
,zip,type,sqft,0,bedrooms,baths,lotsize,acreage,garage,comments,price,0,
0,CASE MAINPHOTO WHEN 0 THEN 0 ELSE 1 END [MAINPHOTO],county from
propertydata where active='Y'")
csvText = rs.getString(2,,",",VBCrLf)
rs.close: set rs = nothing
conn.close: set conn = nothing

set fso = CreateObject("Scripting.FileSystemObject")
set fs = fso.CreateTextFile(Server.MapPath("csv.csv"), true)
fs.writeline(csvText)
fs.close: set fs = nothing
set fso = nothing


Again, this works, but it splits CONTACTNAME into 2 columns. The same
probably in the DESCRIPTION field. How do get it to IGNORE the columns
in those fields?

Thanks!

 

[Bob Lehmann]

Either don't allow commas in the data, or switch to a different delimiter.

Bob Lehmann

 

[Bullschmidt]

GetString() saves some lines of code but is not as flexible for example
if you want to add some extra characters in certain places. And here
are some related links:

http://www.aspmessageboard.com/forum/moderatedadvanced.asp?M=353414&F=31
&P=1

http://www.codingforums.com/showthread.php?p=212430#post212430post212430

http://www.asp101.com/puzzles/puzzle5_s7.asp

http://www.asp101.com/puzzles/puzzle5_s1.asp

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 am using a SQL 2000 server and am trying to export results from a
query to a text file. The code is working, expect my CONTACTNAME field
has a comma, and my export is splitting that into 2 columns when I only
want it to be one.

Here is my code:
set conn = CreateObject("ADODB.Connection")
conn.open DSNtemp
set rs = conn.execute("select
contactname,contactnumber,contactnumber1,contactemail,address,city,state
,zip,type,sqft,0,bedrooms,baths,lotsize,acreage,garage,comments,price,0,
0,CASE MAINPHOTO WHEN 0 THEN 0 ELSE 1 END [MAINPHOTO],county from
propertydata where active='Y'")
csvText = rs.getString(2,,",",VBCrLf)
rs.close: set rs = nothing
conn.close: set conn = nothing

set fso = CreateObject("Scripting.FileSystemObject")
set fs = fso.CreateTextFile(Server.MapPath("csv.csv"), true)
fs.writeline(csvText)
fs.close: set fs = nothing
set fso = nothing


Again, this works, but it splits CONTACTNAME into 2 columns. The same
probably in the DESCRIPTION field. How do get it to IGNORE the columns
in those fields?

 

[Mike]

The issue you are having is with the CSV format instead of the ASP
code. If a column value has a comma in it, you will need to place the
entire column value in quotes. Pretty simply VBScript will do it.

As said above, you will need to recode to loop through the records,
writing each column to the file. If there is a column value that
contains a ",", you will need to surround that whole value with quotes.

Mike Biang
Systems Developer
Cramer Development
ASP/ASP.NET/PHP