Указать неверно указанный тип данных для ms excel

Обновлено: 21.11.2024

В модели данных с каждым столбцом связан тип данных, указывающий тип данных, которые может содержать столбец: целые числа, десятичные числа, текст, денежные данные, даты и время и т. д. Тип данных также определяет, какие операции вы можете выполнять со столбцом и сколько памяти требуется для хранения значений в столбце.

Если вы используете надстройку Power Pivot, вы можете изменить тип данных столбца. Вам может понадобиться сделать это, если столбец даты был импортирован как строка, но вам нужно, чтобы это было что-то еще. Дополнительные сведения см. в разделе Установка типа данных столбца в Power Pivot.

В этой статье

Сводка типов данных

В следующей таблице перечислены типы данных, поддерживаемые в модели данных. При импорте данных или использовании значения в формуле, даже если исходный источник данных содержит другой тип данных, данные преобразуются в один из этих типов данных. Значения, получаемые из формул, также используют эти типы данных.

Тип данных в Excel

Тип данных в DAX

64-битное (восемь байтов) целое число 1, 2

Числа без десятичных разрядов. Целые числа могут быть положительными или отрицательными числами, но должны быть целыми числами от -9 223 372 036 854 775 808 (-2^63) до 9 223 372 036 854 775 807 (2^63-1).

64-битное (восемь байтов) действительное число 1, 2

Вещественные числа — это числа, которые могут содержать десятичные разряды. Действительные числа охватывают широкий диапазон значений:

Отрицательные значения от -1,79E +308 до -2,23E -308

Положительные значения от 2,23E -308 до 1,79E + 308

Однако количество значащих цифр ограничено 15 десятичными цифрами.

Значение True или False.

Строка данных символов Unicode. Могут быть строками, числами или датами, представленными в текстовом формате.

Максимальная длина строки — 268 435 456 символов Юникода (256 мегасимволов) или 536 870 912 байт.

Даты и время в принятом формате даты и времени.

Действительные даты – все даты после 1 января 1900 года.

Тип данных "валюта" допускает значения от -922 337 203 685 477,5808 до 922 337 203 685 477,5807 с четырьмя десятичными цифрами фиксированной точности.

Пробел — это тип данных в DAX, который представляет и заменяет пустые значения SQL. Вы можете создать пробел с помощью функции ПУСТО и проверить наличие пробелов с помощью логической функции ЕПУСТО.

1 Формулы DAX не поддерживают типы данных меньшего размера, чем перечисленные в таблице.

2 Если вы попытаетесь импортировать данные с очень большими числовыми значениями, импорт может завершиться ошибкой со следующей ошибкой:

Ошибка базы данных в памяти: столбец ' ' таблицы '' содержит значение '1,7976931348623157e+308', которое не поддерживается. Операция отменена.

Эта ошибка возникает из-за того, что Power Pivot использует это значение для представления нулей. Значения в следующем списке являются синонимами нулевого значения:

Удалите значение из своих данных и повторите попытку импорта.

Тип данных таблицы

DAX использует табличный тип данных во многих функциях, таких как агрегирование и вычисления логики операций со временем. Некоторые функции требуют ссылки на таблицу; другие функции возвращают таблицу, которую затем можно использовать в качестве входных данных для других функций. В некоторых функциях, которым требуется таблица в качестве входных данных, вы можете указать выражение, результатом которого будет таблица; для некоторых функций требуется ссылка на базовую таблицу. Сведения о требованиях конкретных функций см. в справочнике по функциям DAX.

Неявное и явное преобразование типов данных в формулах DAX

Каждая функция DAX имеет особые требования к типам данных, которые используются в качестве входных и выходных данных. Например, некоторым функциям требуются целые числа для одних аргументов и даты для других; для других функций требуется текст или таблицы.

Если данные в столбце, указанном вами в качестве аргумента, несовместимы с типом данных, требуемым функцией, DAX во многих случаях вернет ошибку. Однако везде, где это возможно, DAX попытается неявно преобразовать данные в требуемый тип данных. Например:

Вы можете ввести дату в виде строки, и DAX проанализирует строку и попытается преобразовать ее в один из форматов даты и времени Windows.

Можно сложить ИСТИНА + 1 и получить результат 2, потому что ИСТИНА неявно преобразуется в число 1 и выполняется операция 1+1.

Если вы добавляете значения в два столбца, и одно значение представлено в виде текста ("12"), а другое - в виде числа (12), DAX неявно преобразует строку в число, а затем выполняет сложение для числовой результат. Следующее выражение возвращает 44: = "22" + 22

Если вы попытаетесь соединить два числа, Excel представит их в виде строк, а затем соединит. Следующее выражение возвращает "1234": = 12 и 34

В следующей таблице приведены неявные преобразования типов данных, выполняемые в формулах.Excel по возможности выполняет неявные преобразования в соответствии с требованиями указанной операции.

Таблица неявных преобразований данных

Тип выполняемого преобразования определяется оператором, который приводит требуемые значения перед выполнением запрошенной операции. В этих таблицах перечислены операторы и показано преобразование, которое выполняется для каждого типа данных в столбце, когда он связан с типом данных в пересекающейся строке.

Примечание. Текстовые типы данных не включены в эти таблицы. Когда число представлено в текстовом формате, в некоторых случаях Power Pivot попытается определить тип числа и представить его в виде числа.

Примечание. Это расширенная тема проверки данных. Общие сведения о проверке данных и о том, как проверить ячейку или диапазон, см. в статье Добавление проверки данных в ячейку или диапазон.

Вы можете использовать проверку данных, чтобы ограничить тип данных или значений, которые пользователи вводят в ячейки. Например, вы можете использовать проверку данных для вычисления максимально допустимого значения в ячейке на основе значения в другом месте рабочей книги. В следующем примере пользователь ввел abc , что не является допустимым значением в этой ячейке.

Когда полезна проверка данных?

Проверка данных незаменима, если вы хотите поделиться книгой с другими и хотите, чтобы введенные данные были точными и непротиворечивыми. Помимо прочего, вы можете использовать проверку данных для следующего:

Ограничить ввод предопределенными элементами в списке. Например, вы можете ограничить выбор отдела пользователя бухгалтерией, расчетом заработной платы, отделом кадров и многими другими.

Ограничить числа за пределами указанного диапазона. Например, вы можете указать максимальный процентный ввод для ежегодного повышения заслуг сотрудника, скажем, 3%, или разрешить только целое число от 1 до 100.

Ограничить даты вне определенного периода времени. Например, в запросе на отгул сотрудника вы можете запретить кому-либо выбирать дату до сегодняшней даты.

Ограничить время за пределами определенного временного промежутка. Например, вы можете указать расписание встречи с 8:00 до 17:00.

Ограничьте количество текстовых символов. Например, вы можете ограничить разрешенный текст в ячейке до 10 или менее символов.

Проверка данных на основе формул или значений в других ячейках. Например, вы можете использовать проверку данных, чтобы установить максимальный предел для комиссий и бонусов на основе общей прогнозируемой суммы заработной платы. Если пользователи вводят сумму, превышающую лимит, они увидят сообщение об ошибке.

Ввод проверки данных и сообщения об ошибках

Вы можете выбрать отображение входного сообщения, когда пользователь выбирает ячейку. Входные сообщения обычно используются, чтобы предложить пользователям рекомендации относительно типа данных, которые вы хотите ввести в ячейку. Этот тип сообщения появляется рядом с ячейкой. Вы можете переместить это сообщение, если хотите, и оно останется видимым, пока вы не переместитесь в другую ячейку или не нажмете клавишу ESC.

Вы настраиваете входное сообщение на второй вкладке проверки данных.

После того, как ваши пользователи привыкнут к вашему входному сообщению, вы можете снять флажок «Показывать входное сообщение, когда выбрана ячейка».

Вы также можете отображать предупреждение об ошибке, которое появляется только после того, как пользователи вводят неверные данные.

Вы можете выбрать один из трех типов предупреждений об ошибках:

Запретить пользователям вводить неверные данные в ячейку.

Предупреждающее сообщение об остановке имеет два варианта: повторить попытку или отменить.

Предупреждать пользователей о том, что введенные ими данные недействительны, не запрещая им их вводить.

При появлении предупреждающего сообщения пользователи могут нажать Да, чтобы принять недопустимую запись, Нет, чтобы изменить недопустимую запись, или Отмена, чтобы удалить недопустимую запись.

Информировать пользователей о том, что введенные ими данные недействительны, не препятствуя их вводу. Этот тип оповещения об ошибке является наиболее гибким.

При появлении информационного предупреждения пользователи могут нажать кнопку "ОК", чтобы принять недопустимое значение, или "Отмена", чтобы отклонить его.

Советы по работе с проверкой данных

Используйте эти советы и рекомендации по работе с проверкой данных в Excel.

Примечание. Если вы хотите использовать проверку данных с книгами в службах Excel или Excel Web App, вам необходимо сначала создать проверку данных в версии Excel для настольных ПК.

Ширина раскрывающегося списка определяется шириной ячейки, в которой выполняется проверка данных. Возможно, вам придется настроить ширину этой ячейки, чтобы предотвратить усечение ширины допустимых записей, которые шире, чем ширина раскрывающегося списка.

Если вы планируете защитить лист или книгу, защитите их после того, как закончите указывать все параметры проверки.Убедитесь, что вы разблокировали все проверенные ячейки, прежде чем защищать лист. В противном случае пользователи не смогут вводить какие-либо данные в ячейки. См. раздел Защита листа.

Если вы планируете предоставить общий доступ к книге, сделайте это только после того, как закончите указывать параметры проверки и защиты данных. После предоставления общего доступа к книге вы не сможете изменить параметры проверки, пока не прекратите общий доступ.

Вы можете применить проверку данных к ячейкам, в которые уже введены данные. Однако Excel не уведомляет вас автоматически о том, что существующие ячейки содержат недопустимые данные. В этом случае вы можете выделить недопустимые данные, указав Excel обвести их на листе. Как только вы определили неверные данные, вы можете снова скрыть круги. Если вы исправите неверную запись, кружок автоматически исчезнет.

Чтобы применить круги, выберите ячейки, которые вы хотите оценить, и перейдите в раздел Данные > Работа с данными > Проверка данных > Обвести неверные данные.

Чтобы быстро удалить проверку данных для ячейки, выберите ее, а затем выберите Данные > Работа с данными > Проверка данных > Настройки > Очистить все.

Чтобы найти на листе ячейки с проверкой данных, на вкладке "Главная" в группе "Редактирование" нажмите "Найти и выбрать", а затем нажмите "Проверка данных". Найдя ячейки с проверкой данных, вы можете изменить, скопировать или удалить настройки проверки.

При создании раскрывающегося списка можно использовать команду «Определить имя» (вкладка «Формулы», группа «Определенные имена»), чтобы определить имя для диапазона, содержащего список. После создания списка на другом листе вы можете скрыть лист, содержащий этот список, а затем защитить книгу, чтобы пользователи не имели доступа к списку.

Если вы измените настройки проверки для ячейки, вы сможете автоматически применить эти изменения ко всем другим ячейкам с такими же настройками. Для этого на вкладке Настройки установите флажок Применить эти изменения ко всем другим ячейкам с такими же настройками.

Если проверка данных не работает, убедитесь, что:

Пользователи не копируют и не заполняют данные. Проверка данных предназначена для отображения сообщений и предотвращения неверных записей только тогда, когда пользователи вводят данные непосредственно в ячейку. При копировании или заполнении данных сообщения не появляются. Чтобы пользователи не могли копировать и заполнять данные путем перетаскивания ячеек, выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» > снимите флажок «Включить дескриптор заполнения и перетаскивание ячеек», а затем защитите лист.

Ручной пересчет отключен. Если ручной пересчет включен, необработанные ячейки могут помешать правильной проверке данных. Чтобы отключить ручной перерасчет, перейдите на вкладку "Формулы" > группа "Расчет" > "Параметры расчета" > нажмите "Автоматически".

Ячейки, на которые ссылаются формулы, верны. Если ячейка, на которую указывает ссылка, изменяется таким образом, что формула в проверенной ячейке вычисляет недопустимый результат, сообщение проверки для ячейки не отображается.

Таблица Excel может быть связана с сайтом SharePoint. Вы не можете добавить проверку данных в таблицу Excel, связанную с сайтом SharePoint. Чтобы добавить проверку данных, необходимо отменить связь таблицы Excel или преобразовать таблицу Excel в диапазон.

Возможно, в данный момент вы вводите данные. Команда «Проверка данных» недоступна, пока вы вводите данные в ячейку. Чтобы завершить ввод данных, нажмите Enter или ESC для выхода.

Рабочий лист может быть защищен или предоставлен для общего доступа. Вы не можете изменить настройки проверки данных, если ваша книга является общедоступной или защищенной. Сначала вам нужно отменить общий доступ к книге или снять защиту с нее.

Как обновить или удалить проверку данных в унаследованной книге

Если вы унаследовали рабочую книгу с проверкой данных, вы можете изменить или удалить ее, если рабочая таблица не защищена. Если он защищен паролем, которого вы не знаете, вам следует попытаться связаться с предыдущим владельцем, чтобы помочь вам снять защиту с рабочего листа, поскольку Excel не может восстановить неизвестные или утерянные пароли. Вы также можете скопировать данные на другой лист, а затем удалить проверку данных.

Если вы видите предупреждение о проверке данных при попытке ввести или изменить данные в ячейке и не знаете, что можно вводить, обратитесь к владельцу книги.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Используйте проверку данных, чтобы ограничить тип данных или значений, которые пользователи вводят в ячейку. Одним из наиболее распространенных способов проверки данных является создание раскрывающегося списка.

Попробуйте!

Выберите ячейки, для которых вы хотите создать правило.

Выберите Данные > Проверка данных.

На вкладке "Настройки" в разделе "Разрешить" выберите один из следующих вариантов:

Целое число — чтобы разрешить ячейке принимать только целые числа.

Десятичный: чтобы разрешить ячейке принимать только десятичные числа.

Список — чтобы выбрать данные из раскрывающегося списка.

Дата — чтобы ячейка принимала только дату.

Время – чтобы ячейка принимала только время.

Длина текста — ограничение длины текста.

Пользовательский — для пользовательской формулы.

В разделе "Данные" выберите условие.

Установите другие обязательные значения в зависимости от того, что вы выбрали для Разрешить и Данные.

Выберите вкладку «Входящее сообщение» и настройте сообщение, которое пользователи увидят при вводе данных.

Установите флажок Показывать входное сообщение, когда выбрана ячейка, чтобы отображать сообщение, когда пользователь выбирает или наводит курсор на выбранную ячейку (ячейки).

Перейдите на вкладку "Предупреждение об ошибке", чтобы настроить сообщение об ошибке и выбрать стиль.

Теперь, если пользователь попытается ввести недопустимое значение, появится предупреждение об ошибке с настроенным вами сообщением.

Скачать наши примеры

Если вы создаете таблицу, которая требует от пользователей ввода данных, вы можете ограничить ввод определенным диапазоном дат или чисел или убедиться, что вводятся только положительные целые числа. Excel может ограничивать ввод данных определенными ячейками с помощью проверки данных, предлагать пользователям вводить действительные данные при выборе ячейки и отображать сообщение об ошибке, когда пользователь вводит недопустимые данные.

Ограничить ввод данных

Выберите ячейки, в которых вы хотите ограничить ввод данных.

На вкладке "Данные" нажмите "Проверка данных" > "Проверка данных".

Примечание. Если команда проверки недоступна, лист может быть защищен или книга может использоваться совместно. Вы не можете изменить параметры проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. в статье Защита книги.

В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните ограничивающие критерии и значения.

Примечание. Поля, в которые вы вводите предельные значения, будут помечены на основе выбранных вами данных и ограничивающих критериев. Например, если вы выберете «Дата» в качестве типа данных, вы сможете ввести предельные значения в поля минимального и максимального значений с пометками «Дата начала» и «Дата окончания».

Запрашивать у пользователей допустимые записи

Когда пользователи щелкают ячейку с требованиями к вводу данных, вы можете отобразить сообщение, поясняющее, какие данные являются допустимыми.

Выберите ячейки, в которых вы хотите запрашивать у пользователей ввод допустимых данных.

На вкладке "Данные" нажмите "Проверка данных" > "Проверка данных".

На вкладке "Входящее сообщение" установите флажок "Показывать входное сообщение при выборе ячейки".

В поле Заголовок введите заголовок сообщения.

В поле "Входящее сообщение" введите сообщение, которое вы хотите отобразить.

Отображать сообщение об ошибке при вводе неверных данных

Если у вас есть ограничения на данные и пользователь вводит неверные данные в ячейку, вы можете отобразить сообщение с объяснением ошибки.

Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

На вкладке "Данные" нажмите "Проверка данных" > "Проверка данных".

На вкладке "Предупреждение об ошибке" в поле "Заголовок" введите заголовок сообщения.

В поле Сообщение об ошибке введите сообщение, которое должно отображаться при вводе неверных данных.

Выполните одно из следующих действий:

Во всплывающем меню «Стиль» выберите

Требовать, чтобы пользователи исправили ошибку, прежде чем продолжить

Предупреждать пользователей о том, что данные недействительны, и требовать от них выбора "Да" или "Нет", чтобы указать, хотят ли они продолжить

Предупредить пользователей о том, что данные недействительны, но разрешить им продолжить работу после закрытия предупреждающего сообщения

Добавить проверку данных в ячейку или диапазон

Примечание. Первые два шага в этом разделе предназначены для добавления любого типа проверки данных. Шаги 3–7 предназначены специально для создания раскрывающегося списка.

Выберите одну или несколько ячеек для проверки.

На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".

На вкладке "Настройки" в поле "Разрешить" выберите "Список".

В поле Источник введите значения списка, разделенные запятыми. Например, введите Низкий, Средний, Высокий.

Убедитесь, что установлен флажок в раскрывающемся списке "В ячейке". В противном случае вы не увидите стрелку раскрывающегося списка рядом с ячейкой.

Чтобы указать, как вы хотите обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые значения.

Протестируйте проверку данных, чтобы убедиться, что она работает правильно. Попробуйте ввести в ячейки как действительные, так и недопустимые данные, чтобы убедиться, что ваши настройки работают так, как вы предполагали, и ваши сообщения появляются именно тогда, когда вы ожидаете.

После создания раскрывающегося списка убедитесь, что он работает так, как вам нужно. Например, вы можете проверить, достаточно ли широка ячейка для отображения всех ваших записей.

Удалить проверку данных. Выберите ячейку или ячейки, содержащие проверку, которую вы хотите удалить, затем выберите «Данные» > «Проверка данных» и в диалоговом окне проверки данных нажмите кнопку «Очистить все», затем нажмите «ОК».

В следующей таблице перечислены другие типы проверки данных и показаны способы их добавления в рабочие листы.

Выполните следующие действия:

Ограничить ввод данных целыми числами в определенных пределах.

Выполните шаги 1–2, описанные выше.

В списке разрешенных выберите Весь номер.

В поле "Данные" выберите нужный тип ограничения. Например, чтобы установить верхний и нижний пределы, выберите один из них.

Введите минимальное, максимальное или конкретное допустимое значение.

Вы также можете ввести формулу, которая возвращает числовое значение.

Например, вы проверяете данные в ячейке F1. Чтобы установить минимальный предел вычетов, равный удвоенному числу детей в этой ячейке, выберите значение больше или равно в поле "Данные" и введите формулу =2*F1 в поле "Минимум".

Ограничить ввод данных десятичным числом в определенных пределах.

Выполните шаги 1–2, описанные выше.

В поле "Разрешить" выберите "Десятичный".

Введите минимальное, максимальное или конкретное допустимое значение.

Вы также можете ввести формулу, которая возвращает числовое значение. Например, чтобы установить максимальный предел для комиссионных и бонусов в размере 6% от зарплаты продавца в ячейке E1, выберите меньше или равно в поле «Данные» и введите формулу =E1*6% в поле «Максимум».< /p>

Примечание. Чтобы разрешить пользователю вводить проценты, например 20 %, выберите «Десятичный» в поле «Разрешить», выберите нужный тип ограничения в поле «Данные», введите минимальное, максимальное или конкретное значение в виде десятичного числа, например .2, а затем отобразите ячейку проверки данных в процентах, выбрав ячейку и нажав Процентный стиль в группе «Числа» на вкладке «Главная».

Ограничить ввод данных датой в диапазоне дат.

Выполните шаги 1–2, описанные выше.

В поле Разрешить выберите Дата.

В поле "Данные" выберите нужный тип ограничения. Например, чтобы разрешить даты после определенного дня, выберите больше.

Введите дату начала, окончания или конкретную дату для разрешения.

Вы также можете ввести формулу, которая возвращает дату. Например, чтобы установить временной интервал между сегодняшней датой и 3 днями от сегодняшней даты, выберите между ними в поле Данные, введите =СЕГОДНЯ() в поле Дата начала и введите =СЕГОДНЯ()+3 в поле Дата окончания.

Ограничить ввод данных временем в пределах временного интервала.

Выполните шаги 1–2, описанные выше.

В поле Разрешить выберите Время.

В поле "Данные" выберите нужный тип ограничения. Например, чтобы разрешить время до определенного времени суток, выберите меньше.

Введите начало, конец или конкретное время для разрешения. Если вы хотите ввести конкретное время, используйте формат времени чч:мм.

Например, предположим, что у вас есть ячейка E2 со временем начала (8:00) и ячейка F2 со временем окончания (17:00), и вы хотите ограничить время встречи между этими временами, затем выберите в поле Данные, введите =E2 в поле Время начала, а затем введите =F2 в поле Время окончания.

Ограничить ввод данных текстом указанной длины.

Выполните шаги 1–2, описанные выше.

В поле "Разрешить" выберите "Длина текста".

В поле "Данные" выберите нужный тип ограничения. Например, чтобы разрешить использование до определенного количества символов, выберите меньше или равно.

В этом случае мы хотим ограничить ввод до 25 символов, поэтому выберите меньше или равно в поле "Данные" и введите 25 в поле "Максимум".

Рассчитать разрешенное на основе содержимого другой ячейки.

Выполните шаги 1–2, описанные выше.

В поле Разрешить выберите нужный тип данных.

В поле Данные выберите нужный тип ограничения.

В поле или полях под полем "Данные" щелкните ячейку, которую вы хотите использовать, чтобы указать, что разрешено.

Например, чтобы разрешить ввод для учетной записи только в том случае, если результат не превышает бюджет в ячейке E1, выберите Разрешить > Целое число, Данные, меньше или равно и Максимум >= =E1.

В следующих примерах используется параметр «Пользовательский», в котором вы пишете формулы для установки своих условий. Вам не нужно беспокоиться о том, что отображается в поле «Данные», так как это отключено с помощью параметра «Пользовательский».

Снимки экрана в этой статье были сделаны в Excel 2016; но функциональность такая же, как и в Excel для Интернета.

Чтобы убедиться, что

Введите эту формулу

Ячейка, содержащая идентификатор продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) символов.

Ячейка с названием продукта (D2) содержит только текст.

Ячейка, содержащая чей-то день рождения (B6), должна быть больше, чем количество лет, указанное в ячейке B4.

Представьте, что ваш клиент предоставляет вам файл Excel в качестве источника SSIS для нового проекта. При загрузке данных вы обнаружите, что тип данных не соответствует ожидаемому, а некоторые строки не заполнены предоставленными данными.

Затем вы пытаетесь изменить тип входных данных в исходном компоненте Excel, но не можете получить желаемый результат. Так в чем проблема?

Что ж, давайте взглянем на ввод.

Первое, что мы замечаем, это то, что столбец CODE состоит из двух типов данных: числовых и буквенно-цифровых (строки 9–12).

Когда мы пытаемся загрузить это в нашу базу данных, мы замечаем, что строки с 9 по 12 загружаются неправильно и отображаются как пустые значения.

В источнике Excel предполагается, что тип данных столбца CODE является числовым (с плавающей запятой), как показано ниже.

Это происходит, даже если мы устанавливаем фиксированный результат источника как буквенно-цифровой:

Первый вопрос: почему значение дается как число с плавающей запятой, когда входные данные ясно показывают, что входные данные представляют собой комбинацию буквенно-цифровых значений?

Некоторые исследования в MSDN выявили следующую информацию:

Исходный компонент Excel в службах SSIS самостоятельно определяет типы входных данных на основе первых 8 строк файла Excel.
"Отсутствуют значения. Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике, чтобы угадать тип данных каждого столбца. Когда столбец содержит смешанные типы данных, особенно числовые данные, смешанные с текстовыми данными, драйвер принимает решение в пользу основного типа данных и возвращает нулевые значения для ячеек, содержащих данные другого типа. (В случае ничьей числовой тип выигрывает.) Большинство параметров форматирования ячеек на листе Excel, по-видимому, не влияют на определение этого типа данных. Вы можете изменить это поведение драйвера Excel, указав режим импорта. Чтобы указать режим импорта, добавьте IMEX=1 к значению «Расширенные свойства» в строке подключения диспетчера подключений Excel в окне «Свойства».

IMEX установлен на 1, но если мы посмотрим дальше, то обнаружим следующее:

«ПРИМЕЧАНИЕ. Установка IMEX=1 указывает драйверу использовать режим импорта. В этом состоянии параметр реестра ImportMixedTypes=Text будет замечен. Это заставляет смешанные данные преобразовываться в текст. Чтобы это работало надежно, вам также может потребоваться изменить параметр реестра TypeGuessRows=8. Драйвер ISAM по умолчанию просматривает первые восемь строк и по этой выборке определяет тип данных. Если эта выборка из восьми строк полностью числовая, то установка IMEX=1 не приведет к преобразованию типа данных по умолчанию в текстовый; он останется числовым. ”

Кроме того, вам не разрешено изменять параметры реестра...
Но если вы хотите изменить их, вот где их можно найти:

Для 32-разрядных
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel

Для 64-разрядных
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Также для 64-разрядных
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Нам не повезло: первые 8 строк имеют числовой тип, а первое буквенно-цифровое значение находится в 9-й строке. Когда мы возьмем новый файл Excel с буквенно-цифровыми значениями для верхних строк, вы заметите, что тип данных теперь буквенно-цифровой.

И загрузка этого в базу данных дает следующий результат:

Простое решение, доказавшее свою эффективность.

Обратите внимание, что мы форматируем ячейки не как «Общие», а как «Текст».
Вот как это сделать:

<р>1. Выберите столбцы, вызывающие проблему, и нажмите «Форматировать ячейки…».

<р>2. В этом диалоговом окне перейдите на вкладку «Число».

Как видите, для форматирования ячейки задано значение «Общее». Это означает, что Excel решает, какой тип форматирования применяется к каждой отдельной ячейке. Мы устанавливаем это значение на «Текст» вместо «Общие».

<р>3. Ячейка теперь отформатирована как имеющая текстовые значения.

Когда мы вернемся к исходному коду SSIS Excel, мы обнаружим долгожданное изменение в типах входных данных.

И это результат выполнения — пустых значений больше нет для записей с 9 по 12:

Один из самых простых способов избежать этой проблемы — работать с файлами CSV. Файл CSV хранит табличные данные в виде простых текстовых данных и позволяет вам выбрать наилучший тип данных для проекта (или то, что вы ожидаете). Это дает вам больший контроль над данными и потоком данных.

Если простые данные вставляются в SQL из Excel, другим вариантом является использование OpenRowSet, который позволяет читать файл XLS из SQL и вставлять его непосредственно из того же запроса.

Читайте также: