troubleshooting Question

issue with open row set accessing excel

Avatar of johnnyg123
johnnyg123Flag for United States of America asked on
Microsoft SQL Server
6 Comments1 Solution46 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
johnnyg123

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros