I am using a openrowset to read an excel spreadsheet in sql 2014
SELECT LICENSE
,LAST
,FIRST
,MI
,DESIGNATION
,BIRTHDATE
,ADDRESS1
,ADDRESS2
,CITY
,STATE
,ZIP
,TELEPHONE
-- ,ORIGINALISSUEDATE
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=\\testserver\c$\IDPRUpload\IDPRUpload.xlsx', 'select * from [IDPR$]')
all is working well except one of the columns in the excel spreadsheet has a mix of dates and phone numbers (if I don't include this column in select it works fine)
so it is treating everything as a date and thus when gets to value that is a phone number it is blowing up with error converting data type
I thought using imex=1 would prevent it from trying to identify column as date but still same error
I need to be able to return all the values as varchar to identify some input errors
Is there something else I need to do?