1

I am trying to bulk insert from a csv file to a SQL table. However, one of my columns contains numbers with scientific notation, e.g. 3E-4. Let's name this column Column X.

  • I first tried creating a table with numeric(18,9) type for Column X, but when I try to bulk insert data to the table, I hit a type mismatch error for Column X.

  • As this post suggested, I tried the following alternative solution: I created another table where the columns are of varchar(255) type instead. No problem with the bulk insert in this case. But after the data insert, I tried to alter the column type for Column X from varchar(255) to float. This gives me the error Error converting datatype varchar to float.

I am not sure how to deal with this problem. The Microsoft website suggests the following : "To work around this behavior, use a format file to bulk import scientific notation float data into a decimal column. In the format file, explicitly describe the column as real or float data. "

I have no idea how to create format files. Is this the only way of working around this problem? If so, could anybody please help with the use of a format file, or instead provide an alternative solution?

Thank you!

Community
  • 1
  • 1
Mayou
  • 8,498
  • 16
  • 59
  • 98
  • See if [*this post*](http://stackoverflow.com/questions/7473081/convert-scientific-notation-to-float-when-using-openrowset-to-import-a-csv-file?rq=1) provides any inside for you. – PM 77-1 Oct 01 '13 at 12:42
  • Thank you. I have already referred to that post, and tried to insert data to a `varchar` column, then convert the column type to `float` (as specified in my post). But it didn't work in my case. – Mayou Oct 01 '13 at 12:44

2 Answers2

1

Create a format file like this:

bcp [database].dbo.[table] format nul -c -x -f .\[formatfilename].xml -t, -T

Then change the xsi:type="SQLNUMERIC" to xsi:type="SQLFLT8" and use that formatfile when bulk importing.

Hans Van Slooten
  • 2,257
  • 2
  • 19
  • 18
0

I have encounter the same issue while writing ETL procedure. moving staging data into actual core table and we had all columns on staging table a NVARCHAR solution i did was, before inserting to core i conevrt that column in to appropriate Numeric value. you already have NVARCHR column so what you need is perform Update on same column with Float and Again Numeric as you needed.

DECLARE @t NUMERIC(28,10)
SELECT @t=CONVERT(NUMERIC(28,10),CONVERT(FLOAT,'1.2408E+12'))
SELECT @t

update <tablename> SET <ColumnName> = CONVERT(NUMERIC(28,10),CONVERT(FLOAT,<ColumnName>))

then perofrm ALTER operation. but remeber that if you are going to insert next BCP in same column then better to have another column which store values in right datatype. if you encounter the INSERT fail because of "Error converting to Data Type ...." that means there are some data which can not be converted to Numeric. see the another post on that issue.

Characters to look for as whole value with ISNUMERIC SQL function

Community
  • 1
  • 1
Anup Shah
  • 1,256
  • 10
  • 15