funky CSV output -- defaults to tab delimited

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

funky CSV output -- defaults to tab delimited

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-16 : 12:40:01

As noted earlier, I'm outputting the results of an SPROC via ADODB recordset to a CSV file on web server and then response.redirecting to the newly-created CSV. Works fine on my machine, but one user is getting tab-delimited output rather than CSV, even though the code says:

whichname="/temp/new.csv" 'at the top of the ASP page, and...

Response.redirect ("http://server/temp/new.csv")

'at the end of the ASP script

Is there an IE setting or an Excel setting I'm missing? If checked his file associations, which are fine for Excel/CSV and his browser settings are identical to mine. He is using Excel 2000 and I have 2002, so there is at least one difference.

Also, if I choose to save the file on his machine rather than open it in the browser window, I get the normal prompt for CSV and the file loads fine in Excel after the save.

??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-16 : 13:09:42

Can you post the ADO code that writes the string/file? If you're using GetString, and you don't specify a column terminator, it will default to a tab, not a comma. The file extension has no bearing on the actual contents.

In fact, if you wanted to, you can output a tab-delimited file with an .XLS extension. Excel will automatically open and convert this file without invoking the import text wizard. It might do this with CSV files too, depending on which version of Excel you have, but I know it works with pretty much every Excel version.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-16 : 13:15:13

Thanks, Rob. Here's the code (from learnasp.com with minor additions by me), which you helped with last week...

Re: how I output it, I'm just looking for output that will look the same on each machine, regardless of Excel version. The earliest version of Excel I have to deal with is 97.

=====================================================

<%

'Collect the needed form fields

strTableChoice = cStr(Request.Form("TableChoice"))
strBeginRange = cStr(Request.Form("BeginRange"))
strEndRange = cStr(Request.Form("EndRange"))

whichname="/temp/temp.csv"
myDSN="dbtest"
mySQL="sp_central_results_range '" & strTableChoice &"','" & strBeginRange &"', '" & strEndRange &"'"
showblank=""
shownull="<null>"
linestart=chr(34)
lineend=chr(34) & vbCRLF 'This is changed from the original
delimiter=chr(34) & "," & chr(34)
delimitersub=""

whichFN=server.mappath(whichname)

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)

' this code detects if data is empty
If rstemp.eof then
response.write "No data to convert for SQL statement "
response.write mySQL & " "
connection.close
set connection=nothing
response.end
end if

data=linestart 'starts the data value with a delimiter
for each whatever in rstemp.fields 'for each field in the recordset
data=data & whatever.name & delimiter 'add the field name and delimit it
next

'trim the last delimiter and add the lineend
data=Left(data, Len(data)-Len(delimiter)) & lineend

'convert the recordset to a string using the delimiter, lineend, and shownull replacements

'data=data & rstemp.GetString(,, delimiter, lineend & linestart, shownull)
data=data & linestart & rstemp.GetString(,, delimiter, lineend & linestart, shownull)

rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing

Set fstemp = server.CreateObject("Scripting.FileSystemObject")
Set filetemp = fstemp.CreateTextFile(whichFN, true)
' true = file can be over-written if it exists
' false = file CANNOT be over-written if it exists

filetemp.Write(Left(data, Len(data)-Len(linestart))) 'trims the last linestart value and writes the file

' response.write thisline & " "

filetemp.Close
set filetemp=nothing
set fstemp=nothing

If err.number=0 then
response.write "File was converted sucessfully! "
response.write "Converted file is at <a href='"
response.write whichname & "'>" & whichname & "</a>"
else
response.write "VBScript Errors Occured! "
response.write "Error Number=#" & err.number & " "
response.write "Error Desc. =" & err.description & " "
response.write "Help Path =" & err.helppath & " "
response.write "Native Error=" & err.nativeerror & " "
response.write "Error Source =" & err.source & " "
response.write "SQL State=#" & err.sqlstate & " "
end if

Response.redirect ("http://dbserver/temp/temp.csv")

%>

====================================================

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-16 : 13:43:28

Let me ask this: CAN you provide a tab-delimited file with an XLS extension, or does it have to be CSV in both content and name?

If you CAN use tab instead, then you can also ditch the quotation marks and simplify the code a little bit:

linestart="" 'empty string, basically you don't need the linestart at all anymore
lineend=vbCRLF 'This is changed from the original
delimiter=chr(9) 'tab character
delimitersub=""

And these lines can change too:

data=data & rstemp.GetString(,, delimiter, lineend, shownull) 'or even:
'data=data & rstemp.GetString(,,,, shownull)
...
filetemp.Write(data) 'writes the file

The only drawback to this is if you have numeric strings, things like zip codes and such that need to preserve leading zeros, they will auto-convert to numeric when Excel opens a tabbed file. If you need to maintain that then stick with the CSV code you have now.

As far as this one guy getting tabs instead of commas, that is REALLY weird. Are you both using the same version of IE? Does Excel open in IE when the redirect happens? Are you both using the same OS too? I have a feeling that the browser may have an association that differs on the two machines (it may not be the same as the OS file association) One thing to try is putting a Response.ContentType in the code (if you haven't already) and trying changing its value and seeing what happens on both machines. If it's set to text/csv, try using the x-application:Excel setting (whatever the exact value is), and vice versa. See what happens if you specify text/html too. And also do a view source on both machines and compare them. If they're the same, then I think it's a browser problem and Excel might be converted the CSV to an HTML type table to simulate the spreadsheet. That's just a WAG though.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-01-16 : 14:52:08

Thanks, Rob. Excel is opening within the browser, as it should. Everything looks normal until the data is written. The format looks like this on his machine:

2.1,"1433","5","1","1","4/9/1996","1","0","1","1","1","0","1","1","1","1","1","1","1"

Funny thing: the code calls for a CSV, which causes the above mess on his machine. I can get the same mess on my machine if I replace the CSV file extension with XLS in the code!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources