OPENROWSET converting phone number in excel sheet to decimal format instead of varchar

Question

One of my field in excel sheet is a phone number and when I bulk import using openrowset and insert it in varchar(50) field it gets converted into decimal format.

This is what I am doing:

CREATE TABLE dbo.TmpDataFileCC
 (
     field_1 VARCHAR(500) NULL
    ,Field_2 VARCHAR(500) NULL
    ,Phone1 VARCHAR(500) NULL 
 )

Insert into TmpDataFileCC(field_1,Field_2,Phone1)
SELECT F1,F2,F3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=J:\UploadToSQL\test_file.xls;HDR=NO', 'SELECT * FROM [sheet1$]')
where F3 is not null

Phone number 3234406751 (in excel file) gets converted into 3.23441e+009 (in TmpDataFileCC_ftb)

I have tried some Cast and convert function but nothing seems to work. Can someone please tell me what can I do to solve it?

score 0 · Answer 1 · answered Jun 08 '11 at 22:15

The driver from what I remember does some funny tricks with the data types. It tries to estimate correct data type from data in first eight or nine rows of the spreadsheet.. You probably would be better off to export data from excel to CSV file and load it with bcp or even openrowset, but using csv driver.

Ah yes: have a look at this article

http://support.microsoft.com/kb/321686

which leads to this link:

http://support.microsoft.com/kb/194124/EN-US

Regards

Piotr

OPENROWSET converting phone number in excel sheet to decimal format instead of varchar

1 Answers1