2

I am have a table, of which one of the columns is of type numeric(18,9). I am trying to bulk insert to this table from a csv file. Note that the columns match between the csv file and the table.

One of the columns in the csv, has some numbers written in the format: 3E-4, 45E-2, etc.(where E is a symbol of power).

When trying to bulk insert the data to the SQL table, I get the error that there is a type mismatch for that specific column. The error is most certainly caused by any number that is formatted like e.g. 5E-4.

N.B: when checked for the column type in excel using type(), all cells return 1 -> this means that the column is of type numeric in the csv file.

Any way to overcome this error?

Thanks,

Mayou
  • 8,498
  • 16
  • 59
  • 98
  • 1
    Here goes another individual's solution to the same problem. They use a two step method - http://stackoverflow.com/a/7473216/1504882 – Elias Sep 30 '13 at 20:12
  • Thanks for the tip. The only thing is that my problem is of larger scale: in other words, I have many columns with this problem. How can I automatically update all columns of the table without having to type the names of each of the columns individually? – Mayou Sep 30 '13 at 20:16

1 Answers1

0

Maybe you can try to do this in a two-step process. First you import the data into some other table where all numeric columns are varchar type and then you convert this into decimal and insert into another table like this. 

select CONVERT(decimal(18,9), CONVERT(float, '3E-4'))
Mike Overton
  • 99
  • 3
  • I tried converting to float first and I still get the same error... This is the query I have tried to execute: `ALTER TABLE ALTER COLUMN FLOAT`. Still getting the same type mismatch error. – Mayou Oct 01 '13 at 12:29