Exporting more than n 256 columns to XLS

[TomSalvato]

Hi experts,

I'm trying to export a crystal report (using CR2013) using the 'Microsoft Excel (97-2003)' option. Unfortunately, I have a column for every day of the year, and a couple more. The export stops on column 256 (IV). This is because crystal automatically exports to an XLS file, instead of XLSX. I can export bare bones versions of this report using the 'Microsoft Excel Workbook Data-Only' option, but it's just not as clean. Is there any way to get crystal to export to an xlsx using the first option?

Appreciate any help, I've spent a lot of time on this report, and will be really disappointed if I can't get the proper output after all this. -TS

 

[SkipVought]

Hi,

Export from CR as a TEXT file, either COMMA Delimited or TAB delimited. Then you can Import into or Open with a version of Excel that supports >366 columns.

If I got such a TEXT file, I would IMPORT into Excel, specifying the data type of each column rather than OPEN with Excel, which can result in unwanted data conversions in certain columns.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TomSalvato]

Thanks, Skip. I'm really just trying to save the formatting that I'm doing in the report. I lose a little of it when I export using the 'workbook data only' option. Exporting to a CSV or text file would pose the same problem. I think I'm just going to have to deal with it. Curious as to why Crystal still exports to xls instead of xlsx. That really would solve my problem. I might give the Gantt chart another try, though that's a bit of a quagmire. Thanks again, Skip. -TS

 

[SkipVought]

What kind of formatting do you need?

What's with the Gantt chart?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TomSalvato]

Ideally, I'd like a Gantt chart to look like the attached. Two records shown.

All 12 months across the top.

Then one line per record going down (no grouping needed here). With the chart next to each line.

The data consists of the SPECIES_NAME (string), DATE_RANGE (string), START_DATE (datetime), END_DATE(dateime)

I figured there was a way to get the Gantt chart feature to work using those two dateime fields, but I couldn't figure it out and there is little to nothing in the way of posts on the subject that i could find. would love an easy tutorial on the feature, but everyone seems to think it's a lost cause.

 

[SkipVought]

Viewing your jpg I have a question. I only see the DATE_RANGE (string), but not the START_DATE (datetime), END_DATE(datetime).

What's the START_DATE (datetime), END_DATE(datetime) for the Peregrin Falcon row? I see part of February unrestricted???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TomSalvato]

Yes, those two fields are not shown on the pic, the START_DATE for that row is 08/01/2020 12:00:00AM, the END_DATE is 02/28/2020 12:00:00AM. Note how it 'wraps' around the year ... so really, that end date would be 2/28/21 ... but we only care about the day and month. The people loading the data are just assuming 2020 on the conversion.

 

[SkipVought]

Why can't CR do what you need?

So what's your purpose for having this in Excel?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TomSalvato]

It really doesn't have to be in excel if I could get the gantt chart to do what I need. I would wind up exporting it to PDF format.

I'm not sure CR can't do what I need, I just can't seem to find any useful documentation or examples of how to create what I'm looking for. I have no experience with the gantt chart feature.

The excel option was just a workaround that I was putting together. One column for every day of the year in the page header, then one formula field for every day of the year (for each record) that is a simple true/false return.

 

[lbass]

Does your data always start on the first of the month and end on the last day of the month? Why not just use months as columns if that is true, instead of days. Or if always on the first of the month and only sometimes on the end of the month, convert data to percentages of the month.

Also, you realize that an end datetime of 2/28 12 AM means the data only goes through 2/27, right?

-LB

 

[TomSalvato]

Hey lbass. Long time no see. Hope you are well.

No the date ranges refer to a specific time allowed to do certain work activities in areas where there are endangered species present. They start/end at all sorts of points of any given month.

 

[SkipVought]

Here's my Excel solution.

If this is the way you need to go, we need to talk about what data to export in forum68.

It is a Conditional formatting solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Here's my Excel workbook with an additional Conditional Formatting solution.

Method 1 uses 366 columns, one for each day of the year.

Method 2 uses 12 columns, one for each month of the year.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein