report field won't display characters properly 2

[briny]

Hi,

This may seem like a really basic question but I have created an inventory count report from a single excel spreadsheet that shows item id, description and grouped by location. The item id column contains items with both purely numbers and items with mixed alphanumeric characters. Depending on which type of data is in the first cell in the spreadsheet, the report will only populate that field type in the report. It will print the description with a blank field beside it where item id should be. I have tried creating a new report using just the item id and description and it does the same thing. I've tried using different versions of crystal (XI and 2008) with the same result. It doesn't do it with the original spreadsheet I used to create the report, but the updated spreadsheet does it consistently. I can't find any difference in the formatting of the item id field on the 2 spreadsheets. Any help or advice would be greatly appreciated.

Thanks.

 

[jdemmi]

I cannot replicate your issue, I am using O2K3 and O2K7 and CR 11.

But here's a few things I thought of....

1. You do realize that CR is treating the first row as column headers (field names(

2. In Excel -- right click the column and select FORMAT CELLS. Since these cells are not numeric, choose TEXT. The open the CR and do her verify DB....now check your results.

3. In CR set the option for displayin the field type in the field explorer...this would look like FIELNAME1(STRING 255). This should help point you (us) in the right direction.

-- Jason
"It's Just Ones and Zeros

 

[briny]

Hi jdemmi,

Thanks for responding, answers to your questions below:

1) Yes and it is picking up the field names as desired

2) Using O2K7, format for entire column is text and verify DB is successful

3) CR11 identifying the data as item ID:Number. I tried removing the database from the report and creating a new connection and re-adding the database but it still identifies it as number. Originally the cells were formatted as general and I changed it to text some time early in my troubleshooting process.

Thanks,

B

 

[jdemmi]

Well that is definitely the issue (until CR sees it as a string all of your non-numeric values are toast)....now how to fix it?

Can you create a new Excel file with just the column headers....making sure to setup the datatypes in excel based on what you need.

Save it (new file name would be a good idea)

Connect to this new Excel file via CR and see what the datatype(s) display as.

If it's "good" then try copying/pasting all of the data from the "old" excel file to the "new" one....

Make sense?



-- Jason
"It's Just Ones and Zeros

 

[briny]

Hi Jason,

Thanks for the suggestion. I have tried this method without success but I was able to resolve the issue. Even though the cell format was set to text, it wasn't really text within the cells. By using the text to columns wizard in excel (suggested by someone here in my company) I was able to convert the cell contents to text. Once that was done then crystal is seeing the contents as a string and is displaying the contents properly in the report.

It wasn't really my crystal skills that were lacking, it was my excel skills

Thank you for your suggestions and have a great day.

Brian

 

[lbass]

Brian, thanks (*) for finding the solution in Excel--I did recreate your issue, and found that the usual formatting to text did nothing, so your solution was helpful.

-LB