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
精彩评论