Can I control formatting of csv output?

Reporting
1

I am attempting to provide data for a customer’s homegrown inventory system by writing data to .CSV files. I am only able to write .CSV from a custom report by using Scheduled Activities. The customer already has a system in place to ingest .CSV data from other sources, so I need to match their format if possible. When Web Reports writes out a report to .CSV file via a scheduled activity, the output does not look like the text that appears in the preview pane. It adds “< br / >” and replaces double quotes with & quot;. When I select an out-of-box report and click “export to CSV”, I do not get these results. The output is not exactly what I need, but it is closer than what I get using a custom report and a scheduled activity. Here is the actual report code. What do I need to change to make the .CSV output look like the preview pane output?

"Name"|"ResourceID"|"MAC Address"|"IP Address"< br />
<?relevance
("%22" &
  name of it & "%22|%22" &
  id of it as string & "%22|%22" &
  concatenation "," of (values of it) of property results whose (name of property of it is contained by set of ("MAC Addresses - Windows"; "MAC Addresses - Unix"; "MAC Addresses - Mac OS X")) of it & "%22|%22" &
  concatenation ", " of (it as string) of ip addresses of it & "%22%0d%0a"
)
of bes computers
?>

This is an example of what that code produces in the preview pane:

"Name"|"ID"|"MACAddress"|"IPAddress"
"computer1"|"123456"|"aa:bb:33:55, dd:gr:55:6e"|"10.10.10.10, 192.168.2.3"
"computer2"|"654321"|"cc:dd:55:xx"|"10.10.10.12"
"computer3"|"567890"|"ff:gg:11:ss"|"10.10.10.15"

…

This is an example of what I get in a .CSV file produced from a scheduled activity:

"Name"|"ID"|"MACAddress"|"IPAddress"
& quot;computer1& quot;|& quot;123456& quot;|& quot;aa:bb:33:55, dd:gr:55:6e& quot;|& quot;10.10.10.10, 192.168.2.3& quot;
< br />& quot;computer2& quot;|& quot;654321& quot;|& quot;cc:dd:55:xx& quot;|& quot;10.10.10.12& quot;
< br />& quot;computer3& quot;|& quot;567890& quot;|& quot;ff:gg:11:ss& quot;|& quot;10.10.10.15& quot;

…

Notice that the header line prints, then the first line of output where double quotes are replaced by “& quot;”. Then all subsequent lines of output start with < br /> and all double quotes have been replaced by & quot;

Thanks!
Mark

EDIT: Attempted to clarify my question and I had to add spaces in the html character tags as they were displaying as html here on the forum…

2

Anyone? I tried to reword my explanation/question above.

3

The HTML tags I think are showing up as you are requesting an HTML relevance evaluation through JS and thus it will try and sanitize the output for HTML

I’m definitely no expert in creating Web Reports reports but it looks like other than the formatting you are getting the right values. Perhaps someone can speak up as to how to get quotes and newlines in a report?

5

@mlong, this works for me, so you can give it a try.

"Name"|"ResourceID"|"MAC Address"|"IP Address"
<?relevance
(html it) of concatenation of ( "%22" &
  name of it & "%22|%22" &
  id of it as string & "%22|%22" &
  concatenation "," of (values of it) of property results whose (name of property of it is contained by set of ("MAC Addresses - Windows"; "MAC Addresses - Unix"; "MAC Addresses - Mac OS X")) of it & "%22|%22" &
  concatenation ", " of (it as string) of ip addresses of it & "%22%0d%0a"
)
of bes computers
?>

I noticed that your first line of hard coded headers works fine.
Also knowing that a custom report is nothing but an HTML output, I did 2 things to ensure the output is completely HTML.

  • Firstly, the concatenation is used to ensure that you have only one row. Otherwise, Web Reports is trying to delineate your multiple rows with the <br/> tag. Since you are returning multiple rows, you must need help to separate them with a CRLF.
  • Secondly, the &quot; is of course to ensure that your Non-HTML is escaped correctly. So I just use the HTML inspector to convert everything to HTML.
2 Likes
6

Thanks! That’s what I needed. The .CSV output is now exactly what I was looking for. The preview pane does not show it the same way, but that doesn’t matter as long as the scheduled activity generates the required output.