writing csv in vbscript with multiline fields

Question

I have been wrestling with this problem with past 4 hours and to be honest really exhausted... I am trying to write a CSV file through vbscript with multi lines in few of the fields... thought it would be easy peasy ... not so ...My original code is

summary = "This is my brief summary"
description = "Hello" & vbCrLf & "Today, I am going to describe my issue."
reference_id = "0000102203"

Set fso = CreateObject("Scripting.FileSystemObject")
Set csv = fso.CreateTextFile(download_file_name,True)

csv.WriteLine summary & "," description & "," & "Consulting" & "," & reference_id 
csv.close
set csv = Nothing

All I am trying to do is create a csv, and when I use excel to open the file would show up as

This is my brief summary| Hello                                   |Consulting| 0000102203    
                        | Today, I am going to describe my issue. |

And what I am getting with numerous tries of replacing vbCrLf with vbCr,vbLf, chr(10), chr(13), "\n", "\n", "\r","\r" and heaps more

This is my brief summary| Hello
Today, I am going to describe my issue. |Consulting|  0000102203

Is there anyway I can actually solve this problem?

Thanks

Community · Accepted Answer · 2021-10-07 12:12:37Z

There is no actual standard. This RFC came well after CSVs were invented. So you can do what you want, but you'll also need code to read your own standard as other tools won't. Implement CR as a special character (code 222 has historical meaning for me - SurveyCraft) and replace with CR after importing.

From https://www.rfc-editor.org/rfc/rfc4180 (also see https://en.wikipedia.org/wiki/Comma-separated_values)

Definition of the CSV Format

While there are various specifications and implementations for the CSV format (for ex. [4], [5], [6] and [7]), there is no formal
specification in existence, which allows for a wide variety of
interpretations of CSV files. This section documents the format that seems to be followed by most implementations:
Each record is located on a separate line, delimited by a line break (CRLF). For example:
   aaa,bbb,ccc CRLF
   zzz,yyy,xxx CRLF
The last record in the file may or may not have an ending line break. For example:
   aaa,bbb,ccc CRLF
   zzz,yyy,xxx

Pink Ninja · Accepted Answer · 2018-01-11 05:13:51Z

1

OKay, after 4 hours of literally banging my head on the keyboard, I finally figure out how to solve the problem. So if anyone is interested ... Just changed this part of the code

description = "Hello" & vbCrLf & "Today, I am going to describe my issue."    
csv.WriteLine summary & "," description & "," & "Consulting" & "," & reference_id

to

desc1 = "Hello" 
desc2 = "Today, I am going to describe my issue."
csv.WriteLine summary & ",""" desc1 & vbCrLf & desc2 & """," & "Consulting" & "," & reference_id

Ta Da ... worked like a charm ...

answered Jan 11, 2018 at 5:13

Pink Ninja

1572 silver badges14 bronze badges

Collectives™ on Stack Overflow

writing csv in vbscript with multiline fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related