Не могу загрузить файл sql
В этом разделе показано, как подключаться к источникам данных "Неструктурированный файл" (текстовый файл) со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server. Для неструктурированных файлов эти две страницы мастера содержат разные наборы параметров, поэтому в этом разделе источник "Неструктурированный файл" и назначение "Неструктурированный файл" рассматриваются отдельно.
массовая загрузка файлов в таблицу FileTable
FileTable ведет себя как обычная таблица для массовых операций с указанными ниже квалификациями.
Таблица FileTable имеет системные ограничения, гарантирующие целостность пространства имен файлов и каталогов. Эти ограничения должны быть проверены на массовых данных, загружаемых в FileTable. Так как часть операций массовой вставки разрешает игнорировать табличные ограничения, следующие меры применяются принудительно.
В настоящее время операции массовой загрузки в таблицу FileTable, принудительно применяющие ограничения, можно выполнять, как с любой другой таблицей. В эту категорию входят следующие операции:
bcp с предложением CHECK_CONSTRAINTS;
BULK INSERT с предложением CHECK_CONSTRAINTS;
INSERT INTO . SELECT * FROM OPENROWSET(BULK . ) без предложения IGNORE_CONSTRAINTS.
Операции массовой загрузки, не применяющие принудительно ограничения, завершаются неуспешно, если системные ограничения для таблицы FileTable не были отключены. В эту категорию входят следующие операции:
bcp без предложения CHECK_CONSTRAINTS;
BULK INSERT без предложения CHECK_CONSTRAINTS;
INSERT INTO . SELECT * FROM OPENROWSET(BULK . ) с предложением IGNORE_CONSTRAINTS.
Загрузка файла в БД
- varbinary(max) - SINGLE_BLOB (рекомендуется для чтения XML-файлов)
- varchar(max) - SINGLE_CLOB (используется для считывания ASCII файлов. Использует кодировку текущей базы данных)
- nvarchar(max) - SINGLE_NCLOB (используется для считывания данных в кодировке юникод)
Результат первого запроса некорректно разобрал русские символы, а второй вообще свалился с ошибкой ("SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode.").
Откроем блокнот и поменяем кодировку файла. Один файл сохраним в кодировки ANSI, второй сохраним в кодировке "Юникод Big Endian" -- UTF16-BE, именно такую кодировку поддерживает MS SQL Server. После загрузки файлов, получим корректные результаты.
загрузить или перенести файлы в таблицу FileTable
Выбор метода загрузки или переноса файлов в таблицу FileTable зависит от того, где хранятся файлы в настоящее время.
Затем нужно обновить существующую таблицу метаданных, чтобы они указывали на новое расположение файлов.
Как выполнить загрузку файлов в таблицу FileTable
Ниже перечислены методы, которые можно использовать для загрузки файлов в таблицу FileTable.
Перетаскивание файлов из исходной папки в новую папку FileTable в проводнике Windows.
Применение параметров командной строки, таких как MOVE, COPY, XCOPY или ROBOCOPY, из командной строки или пакетного файла или скрипта.
Шаг 6. Результаты
На этой странице показан результат импорта. Если на ней есть зеленая галочка, импорт завершен успешно. В противном случае проверьте конфигурацию и входной файл на наличие ошибок.
Указываемые параметры (страница Столбцы – "Формат" = "Без выравнивания по правому краю")
Шрифт
Выбор шрифта для предварительного просмотра данных.
Столбцы источника данных
Настройте ширину строк, перемещая красный вертикальный маркер, а также ширину столбцов, щелкнув линейку в верхней части окна предварительного просмотра
Разделитель строк
Выберите из списка доступных разделителей строк или введите текст разделителя.
Значение | Описание |
---|---|
Строки разделяются сочетанием символов возврата каретки и перевода строки. | |
Строки разделяются символом возврата каретки. | |
Строки разделяются символом перевода строки. | |
Точка с запятой | Строки разделяются точкой с запятой. |
Двоеточие | Строки разделяются двоеточием. |
Запятая | Строки разделяются запятой. |
Табуляция | Строки разделяются символом табуляции. |
Вертикальная черта | Строки разделяются вертикальной чертой. |
Сбросить столбцы
Восстановление исходных столбцов.
На странице Дополнительно отображаются подробные сведения о каждом столбце в источнике данных, включая тип данных и размер. На следующем снимке экрана показана страница Дополнительно с первым столбцом из неструктурированного файла с разделителями.
Обратите внимание, что на этом снимке экрана столбец идентификатор, содержащий числа, изначально имеет тип данных "Строка".
Почему нужно использовать этот мастер?
Этот мастер создан на основе интеллектуальной платформы Program Synthesis using Examples (PROSE) и позволяет улучшить текущий процесс импорта. Для пользователей, которые не обладают глубокими знаниями в предметной области, импорт данных часто представляет собой трудную и утомительную задачу, чреватую ошибками. При использовании мастера достаточно указать входной файл и уникальное имя таблицы, и платформа PROSE сделает все остальное.
PROSE анализирует шаблоны данных во входном файле и определяет имена столбцов, типы, разделители и т. д. Платформа запоминает структуру файла и выполняет все действия по обработке данных.
Подробные сведения о том, как улучшен пользовательский интерфейс мастера импорта неструктурированных файлов, см. в этом видео.
Задаваемые параметры (страница Выбор назначения)
Имя файла
Введите путь и имя для неструктурированного файла.
Обзор
Найдите неструктурированный файл.
Локаль
Укажите языковой стандарт, чтобы предоставить определяемые языком правила для сортировки данных и формата даты и времени.
Юникод
Укажите, использует ли файл Юникод. При использовании Юникода невозможно указать кодовую страницу.
Кодовая страница
Укажите кодовую страницу для текста не в Юникоде.
Формат
Укажите, используется ли в файле форматирование с разделителями, с полями фиксированного размера или форматирование с неровным правым краем.
Значение | Описание |
---|---|
С разделителями | Столбцы разделяются разделителями. Укажите разделитель на странице Столбцы. |
Фиксированная ширина | Столбцы имеют фиксированную ширину. |
Переменная ширина | В файлах с текстом без выключки вправо каждый столбец имеет фиксированную ширину, за исключением последнего столбца, разделенного разделителем строк. |
Ограничитель текста
Укажите ограничитель текста, который следует использовать (если он есть). Например, можно указать, что текстовые поля должны быть заключены в кавычки. (Это свойство применяется только к файлам с разделителями.)
После выбора ограничителя текста невозможно повторно выбрать параметр None. Тип None предназначен для отмены выбора ограничителя текста.
Мастер импорта неструктурированных файлов позволяет легко скопировать данные из неструктурированного файла (CSV-файл, TXT-файл) в новую таблицу в вашей базе данных. Мастер импорта неструктурированных файлов поддерживает файлы форматирования с разделителями-запятыми и с фиксированной шириной. В этом обзоре описано, почему нужно использовать этот мастер, как его найти, а также приведен простой пример.
Пример. Перенос файлов из файловой системы в таблицу FileTable
В этом сценарии файлы хранятся в файловой системе, а в SQL Server имеется таблица метаданных, содержащая указатели на эти файлы. Необходимо переместить файлы в таблицу FileTable, затем заменить исходный путь UNC для каждого файла в метаданных на путь UNC таблицы FileTable. Функция GetPathLocator (Transact-SQL) помогает добиться этой цели.
Например, предположим, что в базе данных имеется таблица PhotoMetadata, содержащая данные о фотографиях. В этой таблице также имеется столбец UNCPath типа varchar(512), содержащий фактический UNC-путь к JPG-файлу.
Чтобы перенести файлы изображений из файловой системы в таблицу FileTable, нужно выполнить указанные ниже действия.
Создайте новую таблицу FileTable для хранения файлов. В этом примере используется имя таблицы dbo.PhotoTable, но не показан код для создания самой таблицы.
Для копирования JPG-файлов с их структурой каталогов в корневой каталог таблицы FileTable можно использовать программу xcopy или аналогичное средство.
Исправьте метаданные в таблице PhotoMetadata с помощью кода, похожего на следующий:
Вопрос
Задача: создать таблицы в tempdb и загрузить в них текстовые файлы.
Несколько дней извращался с BULK INSERT и его параметрами - ничего не вышло.
Несколько часов извращался с OPENROWSET и его параметрами - ничего не вышло.
Вот так выглядит содержимое файла:
"10000R" "NTCRBALN" "0100" "" "" "" 100100 "" "" "" ? "" ?
"10000R" "SHRDBALN" "0100" "" "" "" 100100 "" "" "" ? "" ?
"10000R" "TPCRBALN" "E999" "" "" "" 100100 "" "" "" ? "" ?
"10000r" "tpcrbaln" "f100" "" "" "" 100100 "" "" "" ? "" ?
"10000r" "tpcrlp00" "f100" "" "" "" 100100 "" "" "" ? "" ?
"10000R" "UPCRBALN" "0100" "" "" "" 100100 "" "" "" ? "" ?
"10000R" "UPCRBALN" "A100" "" "" "" 100100 "" "" "" ? "" ?
Таких файлов несколько. В других вместо пустых кавычек стоят разные значения, в некоторых файлах в некоторых строках вместо вопросительных знаков стоит дата в таком формате:
"f300" "" "" "" 100100 "" "" "ddemido2" 04/03/2015 22:11:17.037+01:00 "ddemido2" 04/03/2015 22:11:17.037+01:00
"0100" "" "" "" 100100 "" "" "opercron" 13/10/2014 22:36:44.658+02:00 "opercron" 13/10/2014 22:36:44.658+02:00
Очень неприятный момент - в некоторых столбцах строковых значений встречаются пробелы.
Так же к каждому файлу есть "макет" (Schema.ini) содержащий список нужных столбцов, например макет к файлу выше выглядит так:
[%ИмяФайлаСхемы%]
ColNameHeader=False
Format=Delimited( )
Col1=asc_acc Text Width 8
Col2=asc_sub Text Width 8
Col3=asc_cc Text Width 4
Col4=asc_desc Text Width 24
Col5=asc_user1 Text Width 8
Col6=asc_user2 Text Width 8
Col7=asc_fpos Long
Col8=asc__qadc01 Text Width 8
В этих макетах нет информации про последние (ненужные) 5 столбцов файла.
Подскажите, как мне загрузить такие файлы в таблицы? Уже столько времени безрезультатно убил.
Задаваемые параметры (страница Дополнительно)
Задать параметры каждого столбца
Выберите столбец в левой панели для просмотра его свойств в правой панели. В следующей таблице приведены описания свойств столбцов. Некоторые перечисленные свойства можно настраивать только для определенных форматов неструктурированного файла и столбцов с данными определенных типов.
. Столбцы разделяются парой символов возврата каретки и перевода строки.
. Столбцы разделяются символом возврата каретки.
. Столбцы разделяются символом перевода строки.
Точка с запятой . Столбцы разделяются символом точки с запятой.
Двоеточие . Столбцы разделяются символом двоеточия.
Запятая . Столбцы разделяются символом запятой.
Табуляция . Столбцы разделяются символом табуляции.
Создать
Добавьте новый столбец, нажав кнопку Создать. По умолчанию, кнопка Создать добавляет новый столбец в конец списка. Эта кнопка также имеет следующие параметры, доступные в раскрывающемся списке.
Значение | Описание |
---|---|
Добавить столбец | Добавить новый столбец в конец списка. |
Вставить до | Вставить новый столбец перед выделенным столбцом. |
Вставить после | Вставить новый столбец после выделенного столбца. |
Удаление
Выберите столбец и затем удалите его, нажав кнопку Удалить.
Предложить типы.
Используйте диалоговое окно Предлагаемые типы столбцов , чтобы оценить образец данных в файле и получить предложения для типа данных и длины каждого столбца.
Щелкните Предложить типы. , чтобы открыть окно Предположение типов столбцов.
После того как вы выберете параметры в диалоговом окне Предлагаемые типы столбцов и нажмете кнопку ОК, мастер может изменить типы данных в некоторых столбцах.
На следующем снимке экрана после нажатия вами кнопки Предложить типы мастер определил, что столбец id в источнике данных фактически представляет собой число, а не текстовую строку, и изменит тип данных со строки на целое число.
На странице Предварительный просмотр убедитесь, что список столбцов и образец данных соответствуют вашим ожиданиям.
Устранение неполадок
Описывает процедуру загрузки или переноса файлов в таблицы FileTable.
Шаг 2. Указание входного файла
Имя новой таблицы должно быть уникальным. В противном случае вы не сможете продолжить работу мастера.
Подключение к источнику "Неструктурированный файл"
Параметры для источников данных типа "Неструктурированный файл" занимают четыре страницы. Это довольно много. Однако вам не придется тратить свое время, изучая их все. Ниже перечислены задачи, которые следует принять во внимание.
Page | Рекомендация | Тип |
---|---|---|
Обязательно обновите параметры в разделе Формат. | Рекомендуемая | |
Не забудьте установить разделители столбцов и строк (для файла с разделителями) или пометить столбцы (для файла с полями фиксированного размера). | Рекомендуемая | |
При необходимости проверьте типы данных и другие свойства, по умолчанию назначенные для столбцов. | Необязательно | |
При необходимости просмотрите образец данных, используя заданные вами параметры. | Необязательно |
На странице Общие найдите и выберите файл, а затем проверьте параметры в разделе Формат.
Шаг 4. Изменение столбцов
Мастер определяет имена столбцов, типы данных и т. д. Здесь можно изменить поля, если они определены неверно (например, указать тип данных с плавающей точкой вместо целочисленного типа).
Столбцы, в которых обнаруживаются пустые значения, будут иметь флажок "Разрешить значения NULL". Однако если в столбце предполагается наличие значений NULL, а флажок "Разрешить значения NULL" не установлен, здесь можно обновить определение таблицы, чтобы разрешить значения NULL в одном или во всех столбцах.
Когда все будет готово, нажмите кнопку "Далее".
Шаг 5. Сводка
Это страница сводки, на которой отображается текущая конфигурация. Если возникли проблемы, можно вернуться к предыдущим страницам мастера. В противном случае нажмите кнопку "Готово", чтобы начать импорт.
Альтернатива для импорта простого текста
Если вы хотите импортировать текстовый файл в SQL Server и не нуждаетесь во всех параметрах конфигурации, доступных в мастере экспорта и импорта, рекомендуется использовать мастер импорта неструктурированных файлов в SQL Server Management Studio (SSMS). Дополнительные сведения см. в следующих статьях:
Шаг 1. Открытие мастера и страница "Приступая к работе"
Откройте мастер, как описано здесь.
Первая страница мастера — это страница приветствия. Если вы не хотите, чтобы она открывалась снова, установите флажок Больше не показывать это окно.
Указываемые параметры (страница Столбцы – "Формат" = "Фиксированная ширина")
Шрифт
Выбор шрифта для предварительного просмотра данных.
Столбцы источника данных
Настройте ширину строк, перемещая красный вертикальный маркер, а также ширину столбцов, щелкнув линейку в верхней части окна предварительного просмотра
Ширина строки
Задайте длину строки перед добавлением разделителей для отдельных столбцов. Или переместите красный вертикальный маркер в окне предварительного просмотра, чтобы отметить конец строки. Значение ширины строки автоматически обновляется.
Сбросить столбцы
Восстановление исходных столбцов.
На странице Столбцы проверьте список обнаруженных мастером столбцов и разделителей. На следующем снимке экрана страница показана так, как если бы вы выбрали для неструктурированного файла формат Без выравнивания по правому краю.
В файлах с текстом без выключки вправо каждый столбец имеет фиксированную ширину, за исключением последнего столбца, разделенного разделителем строк.
Задаваемые параметры (страница Общие)
Имя файла
Введите путь и имя для неструктурированного файла.
Обзор
Найдите неструктурированный файл.
Локаль
Укажите языковой стандарт, чтобы предоставить определяемые языком правила для сортировки данных и формата даты и времени.
Юникод
Укажите, использует ли файл Юникод. При использовании Юникода невозможно указать кодовую страницу.
Кодовая страница
Укажите кодовую страницу для текста не в Юникоде.
Формат
Укажите, используется ли в файле форматирование с разделителями, с полями фиксированного размера или форматирование с неровным правым краем.
Значение | Описание |
---|---|
С разделителями | Столбцы разделяются разделителями. Укажите разделитель на странице Столбцы. |
Фиксированная ширина | Столбцы имеют фиксированную ширину. |
Переменная ширина | В файлах с текстом без выключки вправо каждый столбец имеет фиксированную ширину, за исключением последнего столбца, разделенного разделителем строк. |
Ограничитель текста
Укажите ограничитель текста, который следует использовать (если он есть). Например, можно указать, что текстовые поля должны быть заключены в кавычки. (Это свойство применяется только к файлам с разделителями.)
После выбора ограничителя текста невозможно повторно выбрать параметр None. Тип None предназначен для отмены выбора ограничителя текста.
Разделитель строки заголовка
Выберите разделитель из списка разделителей строк заголовка или введите текст разделителя.
Значение | Описание |
---|---|
В качестве разделителей для строки заголовка используются сочетания символов возврата каретки и перевода строки. | |
В качестве разделителей для строки заголовка используются символы возврата каретки. | |
В качестве разделителей для строки заголовка используются символы перевода строки. | |
Точка с запятой | В качестве разделителя для строки заголовка используется точка с запятой. |
Двоеточие | В качестве разделителя для строки заголовка используется двоеточие. |
Запятая | В качестве разделителя для строки заголовка используется запятая. |
Табуляция | В качестве разделителя для строки заголовка используется символ табуляции. |
Вертикальная черта | В качестве разделителя для строки заголовка используется вертикальная черта. |
Пропускаемые строки заголовка
Укажите число строк с начала файла, которые нужно пропустить (если такие есть).
Имена столбцов в первой строке данных
Укажите, содержит ли первая строка (после всех пропущенных) имена столбцов.
На странице Столбцы проверьте список обнаруженных мастером столбцов и разделителей. На следующем снимке экрана страница показана так, как если бы вы выбрали для неструктурированного файла формат С разделителями.
Получение информации о файлах в директории
Получить всё содержимое файловой директории можно используя недокументированную команду master.sys.xp_dirtree , которая принимает следующие параметры
- directory - имя исследуемой директории;
- depth - определяет сколько уровней вложенных директорий необходимо отобразить. Параметр по умолчанию 0 отображает все директории;
- file - определяет отображать ли файлы в каждой директории. По умолчанию 0 не отображает файлы.
Следующая команда возвращает содержимое директории \\Share как файлы, так и вложенные директории. Значение 1 в колонке depth значит, что файл находится в указанной директории
Ниже приводится код загрузки содержимого директории, отфильтрованного по расширению png во временную таблицу userLogo
Результаты выполнения приведенного выше скрипта
Предварительные требования
Эта функция доступна в SQL Server Management Studio (SSMS) 17.3 и более поздних версий. Убедитесь, что вы используете последнюю версию. Ее можно найти здесь.
Как выполнить массовую загрузку файлов в таблицу FileTable
Для массовой загрузки файлов в таблицу FileTable можно использовать различные способы.
bcp
Вызвать с предложением CHECK_CONSTRAINTS .
Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
BULK INSERT
Вызвать с предложением CHECK_CONSTRAINTS .
Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
INSERT INTO . SELECT * FROM OPENROWSET(BULK . )
Вызвать с предложением IGNORE_CONSTRAINTS .
Отключить пространство имен FileTable и выполнить вызов без предложения IGNORE_CONSTRAINTS . Затем снова включить пространство имен FileTable.
Сведения об отключении ограничений FileTable см. в разделе Управление таблицами FileTable.
Учебник
При выполнении действий, описанных в этом учебнике, вы можете использовать свой собственный неструктурированный файл. Если у вас нет собственного файла, можете скопировать следующий CSV-файл из Excel. Назовите этот файл example.csv и сохраните его в формате CSV в удобном месте, например на рабочем столе.
Шаг 3. Просмотр данных
Мастер открывает окно предварительного просмотра для первых 50 строк данных. Если в данных есть ошибки, нажмите кнопку "Отмена". В противном случае перейдите к следующей странице.
Как отключить ограничения FileTable для массовой загрузки
Для массовой загрузки файлов в таблицу FileTable без издержек по применению определенных в системе ограничений, можно временно отключить ограничения. Дополнительные сведения см. в статье Управление таблицами FileTable.
Необходимо загрузить все файлы, определенного формата из заданной директории в таблицу базы. Рекурсивный обход директории делать не нужно. Вместе с файлом необходимо сохранить некоторые метаданные файла из файловой системы.
Лучший отвечающий
Подключение к назначению "Неструктурированный файл"
Для назначения неструктурированного файла доступна всего одна страница с параметрами, как показано на следующем снимке экрана. Найдите и выберите файл, а затем проверьте параметры в разделе Формат.
Права доступа
Для операции загрузки файла в базу данных, необходимо обладать правами на уровне сервера bulkadmin, а также иметь доступ на чтение к директории из которой производится чтение данных.
Приносим извинения. Запрошенное содержимое было удалено. Вы будете автоматически перенаправлены через 1 секунду.
Указываемые параметры (страница Столбцы – "Формат" = "С разделителями")
Разделитель строк
Выберите из списка доступных разделителей строк или введите текст разделителя.
Значение | Описание |
---|---|
Строки разделяются сочетанием символов возврата каретки и перевода строки. | |
Строки разделяются символом возврата каретки. | |
Строки разделяются символом перевода строки. | |
Точка с запятой | Строки разделяются точкой с запятой. |
Двоеточие | Строки разделяются двоеточием. |
Запятая | Строки разделяются запятой. |
Табуляция | Строки разделяются символом табуляции. |
Вертикальная черта | Строки разделяются вертикальной чертой. |
Разделитель столбцов
Выберите из списка доступных разделителей столбцов или введите текст разделителя.
Значение | Описание |
---|---|
Столбцы разделяются парой символов возврата каретки и перевода строки. | |
Столбцы разделяются символом возврата каретки. | |
Столбцы разделяются символом перевода строки. | |
Точка с запятой | Столбцы разделяются символом точки с запятой. |
Двоеточие | Столбцы разделяются символом двоеточия. |
Запятая | Столбцы разделяются символом запятой. |
Табуляция | Столбцы разделяются символом табуляции. |
Вертикальная черта | Столбцы разделяются символом вертикальной черты. |
Предварительный просмотр строк
Просмотр образца данных в неструктурированном файле, разделенном на столбцы и строки с помощью выбранных параметров.
Обновить
Просмотрите результаты изменения разделителей, нажав кнопку Обновить. Эта кнопка становится видимой только после изменения других параметров соединения.
Сбросить столбцы
Восстановление исходных столбцов.
На странице Столбцы проверьте список обнаруженных мастером столбцов и разделителей. На следующем снимке экрана страница показана так, как если бы вы выбрали для неструктурированного файла формат Фиксированная ширина.
Приступая к работе
Чтобы открыть мастер импорта неструктурированных файлов, выполните следующие действия.
- Откройте SQL Server Management Studio.
- Подключитесь к экземпляру ядра СУБД SQL Server или к узлу localhost.
- Разверните узел Базы данных, щелкните правой кнопкой мыши базу данных ("test" в примере ниже), выберите Задачи, а затем — Импорт неструктурированного файла над пунктом меню "Импорт данных".
Дополнительные сведения о различных функциях мастера см. в следующем руководстве:
Задаваемые параметры (страница Предварительный просмотр)
Количество пропускаемых строк данных
Укажите, сколько строк необходимо пропустить в начале неструктурированного файла.
Предварительный просмотр строк
Просмотр данных выборки в неструктурированном файле, разделенном на столбцы и строки согласно выбранным параметрам.
Обновить
Просмотрите эффект от изменения числа пропускаемых строк, нажав кнопку Обновить. Эта кнопка становится видимой только после изменения других параметров соединения.
Дополнительные сведения о странице Предварительный просмотр см. на следующей странице справочника по службам Integration Services: Редактор диспетчера соединений с неструктурированными файлами (страница "Предварительный просмотр").
Читайте также: