4

I have a Excel table with 47 columns and 14K rows. I import this data to SQL Server with OPENROWSET:

INSERT INTO dbo.my_table
SELECT * FROM OPENROWSET
(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=Yes;Database=C:\ExcelFile.xlsx',
    'SELECT * FROM [Sheet1$]'
);

However, only 5138 rows were imported. After some time the number of imported rows decreased down to 5052, i.e. every time - different number of rows. However, when I use Tasks -> Import Data..., then all rows are successfully imported. What's the reason of such behavior?

I'm using SQL Server 2017 14.0.3356.2.

JohnyL
  • 6,894
  • 3
  • 22
  • 41
  • Is there, by any chance, a gap in the data in your spread sheet (i.e. a blank row)? – Thom A Oct 01 '20 at 09:15
  • @Larnu No. There are no gaps. As I noted, `Import Data` worked just fine. It means `OPENROWSET` should work just fine, too. – JohnyL Oct 01 '20 at 09:47
  • @JohnyL If you can add sample data, it would be easier to understand your problem. – droebi Oct 13 '20 at 07:48
  • @droebi The data is sensitive, but I will try to replace it with dummy values. – JohnyL Oct 13 '20 at 09:25
  • You may want to add IMEX=1 to your connection string. IMEX=1 allows data to be read with mixed types (text) any other value of IMEX will read the first 8 rows to determine the type of the column, then any rows which don't match that type are ignored. See https://jingyangli.wordpress.com/2009/02/13/imex1-revisit-and-typeguessrows-setting-change-to-0-watch-for-performance/ – Steve Ford Oct 15 '20 at 10:57
  • @JohnyL have you already created sample data that can be used for testing? – droebi Oct 16 '20 at 08:32
  • 1
    @SteveFord SteveC's answer did the trick - the problem was with syntax, although one more thing had to be done (see my comment). Anyway, thanks for answer! – JohnyL Oct 16 '20 at 10:03
  • Great! Thank you, glad to be of help :) – SteveC Oct 16 '20 at 15:20

1 Answers1

1

The syntax of the SQL Server code differs slightly from the example code in the Docs. To exactly conform to the syntax of the code in the Docs it should look something like this

INSERT INTO dbo.my_table
SELECT * FROM OPENROWSET
(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=Yes;Database=C:\ExcelFile.xlsx',
    [Sheet1$]
);

The name of the sheet [Sheet1$] is no longer inside quotes and the SQL-like code ("SELECT * FROM ") was removed.

The other possible issue could be with specifying HDR=Yes. Do each of the columns for which there is/are any rows have a header without spaces and/or unusual formatting? It's something to consider if needed.

SteveC
  • 5,955
  • 2
  • 11
  • 24
  • Thanks for reply! I will give it a try! – JohnyL Oct 13 '20 at 13:49
  • 1
    After some tests your code did work, but in order to work, I had to set `AllowInProcess=1` and `DynamicParameters=1` for `Microsoft.ACE.OLEDB.12.0` provider. Only after that *all* rows were imported successfully! Thanks a lot! :) – JohnyL Oct 16 '20 at 09:59
  • I guess, you need to mention setting those parameters in your answer. Thanks! – JohnyL Oct 16 '20 at 10:15