Не все строки импортируются из файла Excel с помощью OPENROWSET в SQL Server

#sql-server #openrowset #aceoledb

#sql-сервер #openrowset #aceoledb

Вопрос:

У меня есть таблица Excel с 47 столбцами и 14 тыс. строк. Я импортирую эти данные на SQL Server с помощью 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$]'
);

Однако было импортировано только 5138 строк. Через некоторое время количество импортируемых строк уменьшилось до 5052, т.Е. Каждый раз — разное количество строк.
Однако, когда я использую Tasks -> Import Data... , все строки успешно импортируются.
В чем причина такого поведения?

Я использую SQL Server 2017 14.0.3356.2.

1. Есть ли, случайно, пробел в данных в вашей электронной таблице (т.Е. Пустая строка)?

2. @Larnu Нет. Пробелов нет. Как я уже отмечал, Import Data все работало просто отлично. Это означает OPENROWSET , что они тоже должны работать нормально.

3. @JohnyL Если вы сможете добавить примеры данных, вам будет легче понять вашу проблему.

4. @droebi Данные конфиденциальны, но я постараюсь заменить их фиктивными значениями.

5. @SteveFord Ответ Стивека сделал свое дело — проблема была с синтаксисом, хотя нужно было сделать еще одну вещь (см. Мой комментарий). В любом случае, спасибо за ответ!

Ответ №1:

Синтаксис кода SQL Server немного отличается от примера кода в Документах. Чтобы точно соответствовать синтаксису кода в документах, он должен выглядеть примерно так

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

Имя листа [Sheet1 $] больше не заключено в кавычки, а SQL-подобный код («SELECT * FROM «) был удален.

Другая возможная проблема может быть связана с указанием HDR=Yes . Есть ли у каждого из столбцов, для которых есть / есть какие-либо строки, заголовок без пробелов и / или необычного форматирования? При необходимости это следует учитывать.

1. Спасибо за ответ! Я попробую!

2. После некоторых тестов ваш код действительно работал, но для работы мне пришлось установить AllowInProcess=1 и DynamicParameters=1 для Microsoft.ACE.OLEDB.12.0 поставщика. Только после этого все строки были успешно импортированы! Большое спасибо! 🙂

3. Я думаю, вам нужно упомянуть о настройке этих параметров в вашем ответе. Спасибо! 😉