Converting float to varchar

[theomen]

Hi,

I seem to be having one problem after another at the moment

I'm currently having a problem converting a float value to varchar which maybe someone can help me with.

When I use convert(varchar(20),myFloatValue) on a value, it adds "e+007" to the end of the value. For example, its converting 19983000 to 1.9983e+007.

I'm importing the values from an excel spreadsheet into an sql table, then trying to copy the data into another table that will hold data from multiple spreadsheets of different formats. It is the import function that is setting the field as float, in my final "central" table the field is a varchar(20). I can't understand why its being setup as a float in the first place, but its sql server that is doing it and I have no control over it (the table is dropped and re-created for each import).

Any help appreciated.

 

[SQLDenis]

convert(varchar(20),convert(int,myFloatValue))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[theomen]

Ok, seem to have fixed it by converting it to an integer first, then to varchar(20), as follows:

convert(varchar(20),convert(int,myFloatValue))

Is this the best way of doing it, or may this cause problems somewhere down the line?

 

[theomen]

Thanks SQLDenis, just beat me to it.

 

[yelworcm]

I am not sure I understand the problem. If you are manually importing data, you can control how the new import table is created, by using the edit SQL button. Better still would be to import into an existing table. Id this an option for you?

I think the import function looks over the first 20 or so records to see what data it can expect, and defines columns on those records.

 

[theomen]

yelworcm,

The solution I'm developing needs to take data from a number of sources. Each of these share some common fields, and some also have additional fields.

I have written a visual basic application that continuously polls a folder for files, as soon as a file is put into the folder, it determines the fields that are in that spreadsheet from the filename, and then executes a stored procedure that imports the data into a new table, and then, depending on which fields are included, copies the data from this table into the central table.

The code that imports the spreadsheets doesn't allow me to specify field types, its just a simple sql statement that uses the "openrowset" function to copy the data directly from the excel spreadsheet into the sql database.

The other main problem is that the spreadsheets don't always have the same type of data in the same field, so a field that may hold numeric values in one spreadsheet may hold a string value in another (which makes my job interesting).

I got it sorted in the end by converting it first to an int, then to a char. Seems to be working ok (for now), but there are another 135 fields to go through yet.