OPENROWSET Data conversion

1

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 ?

Thanks!

2

It is not data conversion that is the problem, it is the import itself. Microsoft guesses at what you want and says "oh, you have a number" and proceeds to truncate during import. The guess is based on the first x rows. Because of that I have been known to add a couple of rows of x's to the top of an Excel file.

Finally I got fed up and I wrote a .NET front end so as not to fool with it. SSIS also has it's problems.

I know this does not help but it does show you, you are not alone.

1 Like