3

I'm using VBScript to parse CSV data and display it on a table. I've got the CSV set up and when I open it in Sublime Text, the headers and content are on different lines (there's only one row of content besides the header). It shows two lines in Sublime Text.

The headers don't have a comma at the end of the row so it doesn't break it up and just basically appends the first entry of the second line on to the last entry of the headers line because it's not technically divided by a comma.

The code I'm using is:

<html>
<head>
</head>
<body>
<%@language="vbscript"%>
<table border="1">
<%
dim csv_to_read, fso, act
csv_to_read="sample.csv"
set fso = createobject("scripting.filesystemobject")
set act = fso.opentextfile(server.mappath(csv_to_read))
'Read the first line of the csv, typically these are colum headings
Response.Write "<tr><th>" & replace(act.readline,",","</th><th>") & "</th></tr>" & vbCrLf
'Read the rest of the csv
Response.Write "<tr><td>" & replace(replace(act.readall,vbCrLf,"</td></tr>"&vbCrLf&"<tr><td>"),",","</td><td>") & "</td></tr>"
%>
<caption>Total Number of Records: <%=act.Line-1%></caption>
</table>
</body>
</html>

Why is it not recognizing the line break?

Thanks for your help!

InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28
MillerMedia
  • 3,651
  • 17
  • 71
  • 150

2 Answers2

2

I think your problem is caused by the file not having a EOL marker of vbCrLf (but crLf probably). Evidence:

s = "1,2,3" & vbLf & "4,5,6"
h = "<tr><td>" & replace(replace(s,vbCrLf,"</td></tr>" & vbCrLf & "<tr><td>"),",","</td><td>") & "</td></tr>"
WScript.Echo "vbLf"
WScript.Echo h
s = "1,2,3" & vbCrLf & "4,5,6"
h = "<tr><td>" & replace(replace(s,vbCrLf,"</td></tr>" & vbCrLf & "<tr><td>"),",","</td><td>") & "</td></tr>"
WScript.Echo "vbCrLf"
WScript.Echo h

output:

cscript 23072652.vbs
vbLf
<tr><td>1</td><td>2</td><td>3
4</td><td>5</td><td>6</td></tr>
vbCrLf
<tr><td>1</td><td>2</td><td>3</td></tr>
<tr><td>4</td><td>5</td><td>6</td></tr>

(I hope that the output will prove to the looping advocates that the double replace produces valid HTML)

Update wrt comment:

This version of the script:

s = "1,2,3" & vbCrLf & "4,5,6"
WScript.Echo ".ReadAll() (faked):"
WScript.Echo s

' h = "<tr><td>" & replace(replace(s,vbCrLf,"</td></tr>" & vbCrLf & "<tr><td>"),",","</td><td>") & "</td></tr>"
' step by step

h = replace(s,vbCrLf,"</td></tr>" & vbCrLf & "<tr><td>")
WScript.Echo "step 1 - EOL handling:"
WScript.Echo h
h = replace(h,",","</td><td>")
WScript.Echo "step 2 - comma handling:"
WScript.Echo h
h = "<tr><td>" & h & "</td></tr>"
WScript.Echo "step 3 - head & tail I:"
WScript.Echo h
h = "<!DOCTYPE html><html><head><title>replace demo</title></head><body><table>" & vbCrLf & h & vbCrLf & "</table></body></html>"
WScript.Echo "step 4 - head & tail II:"
WScript.Echo h

output:

cscript 23072652.vbs
.ReadAll() (faked):
1,2,3
4,5,6
step 1 - EOL handling:
1,2,3</td></tr>
<tr><td>4,5,6
step 2 - comma handling:
1</td><td>2</td><td>3</td></tr>
<tr><td>4</td><td>5</td><td>6
step 3 - head & tail I:
<tr><td>1</td><td>2</td><td>3</td></tr>
<tr><td>4</td><td>5</td><td>6</td></tr>
step 4 - head & tail II:
<!DOCTYPE html><html><head><title>replace demo</title></head><body><table>
<tr><td>1</td><td>2</td><td>3</td></tr>
<tr><td>4</td><td>5</td><td>6</td></tr>
</table></body></html>

should make visible how the double replace works and that all parts are important.

Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • I am with you just could you explain why on earth would you put this s,vbCrLf,"" & vbCrLf & "" instead of just that : s,vbCrLf,""? – All Blond Apr 15 '14 at 13:41
  • Just for visual appearances??? In reality that is not needed.Just additional junk in string. – All Blond Apr 15 '14 at 16:53
  • @AllBlond - you are right, the line breaks are not needed for HTML. But I wouldn't call them 'junk'. MxmastaMills may have included them for visual inspection (before asking a validator); I thought them important, because onskee doubted the validity of the fragment. Let's say: don't put them in production code. – Ekkehard.Horner Apr 15 '14 at 17:30
1

Just a quick glance and it looks like you're writing out malformed HTML.

Response.Write "<tr><td>" & replace(replace(act.readall,vbCrLf,"</td></tr>"&vbCrLf&"<tr><td>"),",","</td><td>") & "</td></tr>"

I read the inner replace as replace every carriage return with a closing table cell/table row The outer replace is replacing the commas with a closing cell/opening cell You're then appending another closing cell/closing row.

In my opinion, looping through rows, whether or not is more performance efficient is easier to read and debug down the road. If this applies to your situation please consider something like these:

Reading csv file in classic asp. Problem: column values are truncated up to 300 characters

How to read CSV files line by line in VBScript

Community
  • 1
  • 1
InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28