Sql Server 2005 Openrowset Insert Into Excel - Text To Number Formatting

So it's not a huge deal, but with Google not returning anything more recent (mostly 2007 or so) about it possibly being an Excel driver issue...I came here to ask the question as w

Solution 1:

Have you tried adding IMEX=1 to your connection string? It should force the driver to give you everything in the columns as text regardless of their interpretted format.

There's a full example of a connection string using it here.

Solution 2:

The only way I know of is to create a dummy row of data in the Excel sheet you are writing to. Put fake data in this row in the same format that you want the data to export in. You can hide this row if required.

This should solve both problems, however you have to deal with the dummy row, which can be annoying if you are using the data for a pivot table