Need more robust "Delimiter" and "Separator" options for .CSV files - Community

Guest

April 21, 2009 11:05 AM

Accepted Answer

Some of our business partners need information sent to them in a comma-separated format (.CSV). Specifically, they want all the fields separated by commas and each of the individual fields enclosed in double-quotes ( " ). Crystal 10 would do that more or less by default, but Crystal 11 takes the Microsoft Excel approach, which is to only enclose fields in quotes that are not null (i.e. empty) and that are not numeric.

For example, given Name, Age, Sex, Religion, Title, the Crystal 10 default is:

"Smith, John","35","M","","Engineer"

and the Crystal 11 default is: "Smith, John",35,"M",,"Engineer"

A work-around can be used in Crystal 11 by setting up a formula, possibly named "all_fields," that looks like the following:

‘"’ & {name} & ‘","’ & {age} & '","' & {sex} & '","' & {religion} & '","' & {title} & '"'

Then, when it’s time to export the record, use the following options:

File > Export > Export Report > Separated Values (CSV) > Delimiter (blank / null) and Separator (blank / null) > Save as type "Separated values (*.csv)"

This will result in all the fields being comma-delimited and quoted.

Here’s the problem: Report Commander has no options to set the "Delimiter" and the "Separator" fields to null. Are there any plans to allow this in a coming release? Note that .TXT is not an option for exporting long comma-delimited records which have numerous fields because the records will be truncated even when using the maximum, landscape version of 11" x 17" paper.

This topic has an accepted answer. Jump to it.

Bill Staff (599 posts)

April 21, 2009 11:56 AM

Accepted Answer

Report Commander has options to control this, but they don't appear in the Builder or the documentation. You can use the "-csvchar" and "-csvdelimiter" options to set the separator (comma by default) and text delimiter (double quote by default). You can use

-csvdelimiter:{none}

to get rid of the quotes. Unfortunately I don't think you can get rid of the separator--the csvchar option doesn't use the "{none}" syntax.

We have a new version in the works and can add the option to it. We can probably get you a build with this feature within a few days. Please send me a private message or open a support case so I can get it to you.

Bill Staff (599 posts)

April 22, 2009 05:47 AM

Accepted Answer

It just occurred to me that you could use

-csvdelimiter:{none} -csvchar:" "

(that's a space between the double quotes). That sould give you what you want.

Tim (1 post)

April 29, 2009 12:21 PM

Accepted Answer

Fellas, you have to stop hiding the good stuff!

I used the following parameters, but it was the 'csvdelimiter' option that did the trick. The 'csvchar' option was irrelevant because I strung everything together using the "all_fields" work-around that I referred to when I opened this thread on 04/21/2009. (By the way, setting 'csvchar' to a blank isn't desirable because the fields end up being separated by blank spaces, which makes it impossible to know where one field stops and another starts. A {none} option would be much more useful and also compatible with the Crystal option.)

-exportformat=csv -csvstyle=noheader -csvchar="," -csvdelimiter={none}

But, something really puzzles me which you may be able to answer. For this question, I'll call my report 'EmployeeList.rpt'. It's designed to hide the detail lines and print a 'Group Footer' at employee number breaks. When I run 'EmployeeList.rpt' in Crystal 10.2 and export the file as a "Separated Values (CSV)" file, I get the same records as I see in the Crystal "Preview" screen. However, when I run 'EmployeeList.rpt' in Crystal 11.0 and export the file, I get the detail lines instead of the 'Group Footer' lines. From what I can find on various WebSites, that's just the way life is with Crystal 11, and you have to learn to live with it. However, when I use Report Commander to generate the report, it works like Crystal 10.2 and not like Crystal 11.0, even though it's my understanding that your product is supposed to mimic Crystal 11. What's the explanation? More importantly, is it safe to assume that Report Commander will continue to handle files that way in future releases?

Bill Staff (599 posts)

April 29, 2009 01:31 PM

Accepted Answer

Report Commander 1.2 uses the Crystal Reports 11 runtime. This version of the runtime did not allow us to control the header and footer exporting options like you can in the designer, so we worked around it by exporting to Excel (for which the runtime did give the option) and then converting the Excel format to CSV ourselves.

So that's probably why you're getting a different result--we're not using a direct export to CSV.

Starting with Crystal Reports 11R2, the runtime exposes the options to control header and footer formatting, so we have eliminated the clunky export-and-convert workaround and are now exporting directly using the CR runtime. You would need to test out the beta (which is now using the CR2008 runtime) and see if it gives you the results you want. Contact me directly for the download link if you want to try it now, or you can wait until it's publicly available.