I am trying to write a script which, when run, would create a .csv file that can be opened by Excel. One of the requirements for this script is that it needs to be able to allow cells to have a soft return to separate different values. For example, if we want to show multiple attributes and their values in a cell, we'd want the cell to display:
Attribute1: AttrValue So, how would I write the CSV file through DXL so that it will display like this in Excel?
Chris chrscote - Wed Oct 17 10:05:13 EDT 2018 |
Re: Including soft line breaks in a CSV file using DXL
CSV with line breaks looks like this attrname1;attrname2;attrname3;attrname4 "attr1 req 1";attr2 req 1;"attr3 with break req 1";"attr4 req 1" attr1 req 2;attr2 req 2;attr3 req 2;"attt4 req 4"
|
Re: Including soft line breaks in a CSV file using DXL And just be sure for each cell value (which will be separated by commas) you (a) escape any double quote characters within the value (i.e. replace one double quote with two consecutive double quotes), and then (b) put quotes around each value (which you then separate with commas)
So maybe:
string escapeStringCSV(string input)
{
int i
int ilen=length(input)
string tmp
string output=""
for (i=0; i<ilen; i++)
{
tmp=input[i:i]
if (tmp!="\"") { output=output tmp }
else { output=output "\"\"" }
}
return output
}
Buffer objectAttributes2Buffer(Skip &objects,Skip &attributes)
{
if (null objects || null attributes) return null
Buffer b=create,line=create
Object o
string attr,attrVal
int attrCount=0
//Build heading line:
for attr in attributes do
{
attrCount++
attr=escapeStringCSV(attr)
attr="\"" attr "\""
if (attrCount>1) b+=","
b+=attr
}
b+="\n"
//Gather desired object data:
for o in objects do
{
line=""
for attr in attributes do
{
attrVal=probeAttr_(o,attr)""
if (null attrVal)
{
if (stringOf(line)=="") line=","
else line=line ",,"
}
else
{
attrVal=escapeStringCSV(attrVal)
attrVal="\"" attrVal "\""
if (stringOf(line)=="") line=attrVal
else line=line "," attrVal
}
}
line=line "\n"
b+=line
}
delete line
return(b)
}
|
Re: Including soft line breaks in a CSV file using DXL Mike.Scharnow - Wed Oct 17 10:44:31 EDT 2018
CSV with line breaks looks like this attrname1;attrname2;attrname3;attrname4 "attr1 req 1";attr2 req 1;"attr3 with break req 1";"attr4 req 1" attr1 req 2;attr2 req 2;attr3 req 2;"attt4 req 4"
First, I assume that the semi-colons in your code should be commas since it's a comma separated values file. Second, when I saved the code above and opened it in Excel, I am getting an extra empty line between each row of data (see attached image). I did expand the columns so that each entry is displayed on a single line with the obvious exception of the multi-line cell. I've also attached the CSV file so that you can see that I didn't change anything from the code above. Attachments CSVOutput.PNG NewCSVTest.csv |
Re: Including soft line breaks in a CSV file using DXL chrscote - Wed Oct 17 11:11:02 EDT 2018 First, I assume that the semi-colons in your code should be commas since it's a comma separated values file. Second, when I saved the code above and opened it in Excel, I am getting an extra empty line between each row of data (see attached image). I did expand the columns so that each entry is displayed on a single line with the obvious exception of the multi-line cell. I've also attached the CSV file so that you can see that I didn't change anything from the code above. Chris, The CSV separator is language dependent. Yes, sorry, in the USA, the separator is a comma, in Germany it is the semicolon (the reason being that number separator in Germany is a comma, in the USA it is a decimal point). (just in case you have to transfer your CSV file to another country...)
Double quote characters are more or less optional. When you do not know whether you have a carriage Return in your text, you need to surround text by duoble quotes. When you have number values or date values, you should not use them.
When you look at the csv file that you sent, you see that your lines are separated with 0D 0A 0A ((Carriage Return+Line Feed = windows new line, (Line Feed=Unix new line), I'm not sure how you created this But the code that Colin sent should work correctly |
