Hi all - I use the OPENROWSET function for SSMS to pull from an Excel XLSX file.
SELECT * OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','EXCEL 12.0;DATABASE=G:\TEMP\FILE.XLSX;HDR=YES;IMEX=1','SELECT * FROM [SHEET$] ')
One of the columns is a 10-13 digit account number field. Excel has this stored as numeric. When I pull this field into SQL, it defaults to exponential notation:
When I try and convert it through SQL syntax, there's a data loss, and the account number field usually ends with several zeros.
As a band-aid solution, I'll create a new column in Excel and convert it to text using this function: TEXT(B2,"0")
My question - Is there any way I seamlessly do the data conversion in SQL ?