Using Excel as Data Source for Crystal Reports

[moodyb]

I’m using two excel (xls) spreadsheets as a data source but for some reason the data in some, not all, columns is not pulling into Crystal. I see the column/table name in Crystal, right click, browse data, and no data is shown. If I open the spreadsheet, look at that particular column, I see several cells with values in that column. I’ve tried to format the data type in excel to numbers, or text, but nothing seems to enable me to pull the data into Crystal. Crystal has incorrectly “guessed” the data as string data. The data type in excel is number data, only numbers are in this column, no text. Any help is appreciated.

 

[lbass]

You could try the text to column method. Please see thread767-1652932: Field contains text or number.

-LB

 

[moodyb]

I was told to create new ODBC connections using the Microsoft Excel Driver (xls, xlsx, xlsm.....) [not the Microsoft do Excel Driver] and was able to view data in formulas as planned.

 

[NickieP]

When Excel is used as a table in CR, CR looks at first 10 or so rows on a column, if they are numbers in a text field (but later rows are text) it will decide all of the column is numeric even if the column format is set as text - hence text fields don't show in CR.
Test this by changing the sort order on the Excel sheet so alpha numeric text records are at the top, and numbers as text are after them.
Your CR should work.
To fix this so sort order doesn't matter:
Change the Excel column's format to text, then do "text to columns" on the same column, outputting as text. This will resolve this issue for CR.
It is the 'text to columns' after the changing the format that fixes it, not the changing of format.