Excel database - all columns available to CR, except 1 1

[Deb100]

Hi everyone

I am using Crystal Reports XI, and my database is an Excel spreadsheet.

I am able to pick up data from all columns, except one. In Excel it contains the numerical value of 'Interest Received'. However, CR is identifying it as a string data type, instead of a number, and when I 'Browse' in the Field Explorer no data is displayed in the box. I can Browse other columns, both string and number and view their contents.

I have tried 'Verifying' the database and that does not make any difference.

I would appreciate any ideas you may have.

Regards

Debbie

 

[synapsevampire]

This is fairly common.

One thing that usually resolves is to insert a faux row at the top of the spreadsheet to identify the data types, or sort the data so that the first row definitely contains a numeric, and make sure that none of the rows are NOT numerics, that is they do not contain non-numeric data. For instance a $ symbol means it is NOT a numeric, etc.

Also you don't state what connectivity you are using, you have the option of Database files, or you can set up an ODBC connection, if one is causing problems, try the other.

You might also have difficulties if the numeric is too large.

-k

 

[Deb100]

Hi synapsevampire

Adding a zero in the first row, as you suggested, has resolved this problem, however, it still sees it as a string so I cannot sum it. I think I can use ToNumber to read it as a number.

many thanks for your help.

Regards

Debbie

 

[synapsevampire]

Right, using val or tonumber should resolve that, however it is odd that it still considers it a string, there may be spaces in the column somewhere, which means it is a string.

Excel is a horrible data source.

-k

 

[Deb100]

Tell me about it! I will have a look for spaces.

Thanks

Debbie