Changing an Excel Number field to a String

Viscount · Oct 27, 2004

Help... I am about to start to pull my hair out. (I must warn you that there is not too much left to pull out..!)

I have got a list of numbers in excel and I want to link that field to a table in a SQL2000 Database.

My problem is that the Database is classified as a string but when I try to link it with the excel Crystal assumes that the excel field is a number and will not allow me to link to it.

I have even tried to change the excel sheet by formatting the cells and entering some text into the field, but I still get a Number field and the test field with the letters in it comes up as 0.

I am using CR 9.

Any help will be greately received..!

Cheers

Vis.

Katy44 · Oct 27, 2004

I remember we had a similar problem...I don't know if this will help, but from what I remember, Crystal decides on the field type based on a single field in that column. So even though you have changed some of the fields, it may not be enough. I assume the field it chooses is the first in the column.
Sorry if this is no help at all, I know I'm vague but I can't really remember the details.

vidru · Oct 27, 2004

I have that problem when I try to import Excel data into SQL Server all the time. Even if you explicitly set the format of the column to text, it's still considered a number.

The workarounds I've found are:
1) create a fake record with 'zzzzzzzzz' in the field, then reformat the column as text.
2) don't use Excel - import the spreadsheet into Access, check the data type there, then use that as your data source. I always have good results this way.

-dave

synapsevampire · Oct 27, 2004

Katy: The problem is with Excel, not Crystal.

Dave pretty much nailed this, but I would add that you suggest to people that they NOT use Excel as a datasource.

I often use Dave's #1 method, although another approach is to select a row of data from within the spreadsheet that has proper data types and make it the first row, then explicitly select the proper data types, and then Excel will properly report it's data types.

-k

Viscount · Oct 28, 2004

Thanks for your help - i ended up using Dave's #2 approach and setting the field types to what I wanted them to be.

Cheers

Vis

jcoleman · Nov 9, 2004

vamp,

What if you have a mixture of numbers and strings within the column and you need to display both? I have formatted the column as text, but when there are numbers in the first few rows I get only the numbers in Crystal. If I sort the excel data with the first rows as text, I only get the text fields on my report.

I seen another post that u replied to back in 2002 with crystal 8.0. I wonder why they havn't add any functionality to Crystal to let us decide what the datasource format should be?

Any ways, any ideas for mix formats coming out of excel?

jc

willyd61 · Jun 23, 2005

That is crazy, There has to be a patch or workaround. I hate access, and I have spreadsheets the change to often. Until I came across this issue with linking to my oracle table threw the zipcode field.

Does anyone know of a way around this and still link to the excel file? I don't want to input the stuff into another database.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Changing an Excel Number field to a String

Viscount

IS-IT--Management

Katy44

Technical User

vidru

Programmer

synapsevampire

Programmer

Viscount

IS-IT--Management

jcoleman

MIS

willyd61

MIS

Similar threads

Part and Inventory Search

Sponsor