开发者

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

开发者 https://www.devze.com 2023-03-11 04:07 出处:网络
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.

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?


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

0

精彩评论

暂无评论...
验证码 换一张
取 消