Converting entire column's datatype from string to float

Question

Converting entire column's datatype from string to float

akmerve

SSC Enthusiast

Points: 194
More actions
March 11, 2010 at 11:36 am

#221718

I am trying to convert one of the fields' data type in the table from delimited text to float, but it fails (see below). For the sake of the argument, I have created a simple table pasted below and save it as .csv and .txt files. As I started importing it, the default data type is in both cases string and changing it to float or any int types does not let the procedure go through, it fails. I know that Excel files work but the files I receive flat files (at this point I do not want make my job doubled.)
Any suggestions would be much appreciated.
Akmerve
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "OrderAmt" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Test Table
----------
CREATE TABLE [dbo].[Orders](
[OrderId] int NOT NULL,
[OrderAmt] float NOT NULL,
[VendorId] varchar (4) NOT NULL,
[OrderDate] SmallDateTime NOT NULL,
) ON 'PRIMARY'
INSERT INTO Orders VALUES (1, '10.08', 'AP12', '2009-10-11 08:00:00');
INSERT INTO Orders VALUES (2, '12.50', 'CA09', '2008-10-21 10:00:00');
INSERT INTO Orders VALUES (3, '100.57', 'KL15', '2009-10-18 12:00:00');
INSERT INTO Orders VALUES (4, '19.99', 'AM11', '2010-01-12 09:00:00');
INSERT INTO Orders VALUES (5, '47.14', 'AP12', '2009-10-12 11:00:00');
INSERT INTO Orders VALUES (6, '57.23', 'FR07', '2010-02-13 10:00:00');
INSERT INTO Orders VALUES (7, '89.15', 'KL15', '2008-05-17 21:00:00');
INSERT INTO Orders VALUES (8, '36.21', 'CA09', '2008-11-13 21:00:00');
INSERT INTO Orders VALUES (9, '118.40', 'KL15', '2009-09-13 21:00:00');
INSERT INTO Orders VALUES (1, '45.78', 'FR07', '2009-10-09 21:00:00');
INSERT INTO Orders VALUES (5, '34.67', 'AP12', '2010-10-18 21:00:00');
INSERT INTO Orders VALUES (8, '88.07', 'FR07', '2009-10-23 21:00:00');

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

shouldn't orderAmt be a money or decimal(19,4) datatype instead of float?

I'm thinking that because it's an approximate value for a float, some value does nto convert well to float, but would be fine for a decimal type.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

akmerve SSC Enthusiast Points: 194 More actions · Answer 2

No, I get the same error message (execution error). After any query you can save the output file as a .txt or .csv, then try to import it. It fails to change the string types to numeric, float or to any numerical types. It seems very simple but I hope someone let me know if there is any manipulation exist.