Импорт файла CSV в SQL Server

Я ищу помощь для импорта .csv файл в SQL Server с помощью BULK INSERT и у меня есть несколько основных вопросов.

Вопросы:

  1. Данные файла CSV могут иметь , (запятая) между (Пример: описание), так как я могу сделать импорт обработки этих данных?

  2. Если клиент создает CSV из Excel, то данные с запятой заключаются в "" (двойные кавычки) [как в примере ниже], так как импорт может справиться с этим?

  3. Как мы отслеживаем, если в некоторых строках есть неверные данные, которые пропускает импорт? (при импорте пропускаются строки, которые нельзя импортировать)

Вот пример CSV с заголовком:

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

И оператор SQL для импорта:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
Источник

17 ответов

Решение

На основе импорта SQL Server CSV

1) Данные файла CSV могут иметь , (запятая) между (Пример: описание), так как я могу сделать импорт обработки этих данных?

Решение

Если вы используете , (запятая) в качестве разделителя, то нет никакого способа разграничить запятую как терминатор поля и запятую в ваших данных. Я бы использовал другой FIELDTERMINATOR лайк ||, Код будет выглядеть так, и он будет отлично обрабатывать запятую и косую черту.

2) Если клиент создает CSV из Excel, то данные, которые имеют запятую, заключены в " ... " (двойные кавычки) [как в примере ниже], так как импорт может справиться с этим?

Решение

Если вы используете BULK-вставку, то невозможно обработать двойные кавычки, данные будут вставлены с двойными кавычками в строки. вставив данные в таблицу, вы можете заменить эти двойные кавычки на '".

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) Как мы отслеживаем, если в некоторых строках есть неверные данные, которые пропускает импорт? (при импорте пропускаются строки, которые нельзя импортировать)?

Решение

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

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )
Источник

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

• Войдите в свою базу данных с помощью SQL Server Management Studio.

• Щелкните правой кнопкой мыши по вашей базе данных и выберите Tasks -> Import Data...

• Нажмите Next > кнопка

• Для источника данных выберите Flat File Source, Затем используйте кнопку Обзор, чтобы выбрать файл CSV. Потратьте некоторое время на настройку того, как вы хотите импортировать данные, прежде чем нажать на Next > кнопка.

• В качестве пункта назначения выберите правильного поставщика базы данных (например, для SQL Server 2012 вы можете использовать собственный клиент SQL Server 11.0). Введите имя сервера. Проверить Use SQL Server Authentication переключатель. Введите имя пользователя, пароль и базу данных, прежде чем нажать на Next > кнопка.

• В окне Выбор исходных таблиц и представлений вы можете редактировать сопоставления, прежде чем нажимать Next > кнопка.

• Проверить Run immediately установите флажок и нажмите на Next > кнопка.

• Нажми на Finish кнопка для запуска пакета.

Вышеуказанное было найдено на этом сайте (я использовал его и протестировал):

Источник

2) Если клиент создает CSV из Excel, тогда данные, которые запятые, заключены в " ... " (двойные кавычки) [как в примере ниже], так как импорт может справиться с этим?

Вы должны использовать опции FORMAT = 'CSV', FIELDQUOTE = '"':

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
Источник

Лучший, самый быстрый и простой способ решить проблему с запятой в данных - это использовать Excel для сохранения файла, разделенного запятыми, после того, как в Windows в качестве разделителя списка задано что-то отличное от запятой (например, канал). Затем он сгенерирует для вас отдельный файл (или любой другой), который вы затем сможете импортировать. Это описано здесь.

Источник

Поскольку они не используют мастер импорта SQL, шаги будут следующими:

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

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

Источник плоского файла

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

  1. Нажмите Далее и выберите в последнем варианте, который

Клиент SQL

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

  1. Мы можем определить идентификатор таблицы в CSV (рекомендуется, чтобы столбцы CSV назывались так же, как поля в таблице). В опции Edit Mappings мы можем видеть предварительный просмотр каждой таблицы со столбцом электронной таблицы, если мы хотим, чтобы мастер вставил id по умолчанию, мы оставляем эту опцию не отмеченной.

Включить вставку идентификатора

(обычно не начиная с 1), вместо этого, если у нас есть столбец с идентификатором в CSV, мы выбираем включение вставки идентификатора, следующий шаг - завершить работу мастера, мы можем просмотреть изменения здесь.

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

Эта ссылка имеет изображения.

Источник

Импортируйте файл CSV в базу данных с помощью SQL Server Management Studio.

  1. Сначала создайте таблицу в своей базе данных, в которую вы будете импортировать файл CSV.
  2. Войдите в свою базу данных с помощью SQL Server Management Studio.
  3. Щелкните правой кнопкой мыши базу данных и выберите Задачи -> Импорт данных...
  4. Нажмите кнопку Далее>.
  5. В качестве источника данных выберите "Источник плоских файлов". Затем используйте кнопку Обзор, чтобы выбрать файл CSV. Потратьте некоторое время на настройку импорта данных, прежде чем нажимать кнопку Далее>.
  6. В качестве пункта назначения выберите правильного поставщика базы данных (например, для SQL Server 2012 вы можете использовать собственный клиент SQL Server 11.0). Введите имя сервера; Установите флажок Использовать проверку подлинности SQL Server, введите имя пользователя, пароль и базу данных, прежде чем нажимать кнопку Далее>.
  7. В окне "Выбор исходных таблиц и представлений" вы можете редактировать сопоставления, прежде чем нажимать кнопку "Далее>".
  8. Установите флажок Выполнить немедленно и нажмите кнопку Далее>.
  9. Нажмите кнопку Готово, чтобы запустить пакет.

Ссылка

Источник

Во-первых, вам нужно импортировать файл CSV в таблицу данных

Затем вы можете вставить объемные строки с помощью SQLBulkCopy

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}
Источник

Вот как бы я решил это:

  1. Просто сохраните файл CSV в виде листа XLS в Excel (При этом вам не придется беспокоиться о разделителях. Формат электронной таблицы Excel будет считан как таблица и импортирован непосредственно в таблицу SQL).

  2. Импортируйте файл, используя SSIS

  3. Напишите пользовательский сценарий в диспетчере импорта, чтобы пропустить / изменить данные, которые вы ищете.(Или запустить мастер-сценарий для проверки данных, которые вы хотите удалить)

Удачи.

Источник

Если кто-то хочет импортировать csv с помощью powershell

      ## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input SQL Server Variables and CSV path
$csvPath = "D:\Orders.csv"
$csvDelimiter = ","
$serverName = "DESKTOP-DOG5T0Q\SQLEXPRESS"
$databaseName = "OrderDetails"
$tableSchema = "dbo"
$tableName = "Orders"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -header "Id","Country","Price","OrderQuantity" -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

Источник: Импорт csv на сервер SQL (с запросом ИЛИ без запроса с использованием SSMS)

Источник

Все ответы здесь отлично работают, если ваши данные "чистые" (нет нарушений ограничений данных и т. Д.) И у вас есть доступ к размещению файла на сервере. Некоторые из представленных здесь ответов останавливаются на первой ошибке (нарушение PK, ошибка потери данных и т. Д.) И дают вам по одной ошибке за раз при использовании SSMS, встроенной в задачу импорта. Если вы хотите собрать все ошибки сразу (если вы хотите сказать человеку, который дал вам файл.csv, чтобы он очистил свои данные), я рекомендую в качестве ответа следующее. Этот ответ также дает вам полную гибкость, поскольку вы сами "пишете" SQL.

Примечание. Я предполагаю, что вы используете ОС Windows и имеете доступ к Excel и SSMS. Если нет, я уверен, что вы можете настроить этот ответ в соответствии со своими потребностями.

  1. В Excel откройте файл.csv. В пустом столбце вы напишите формулу, которая построит индивидуальныйINSERTзаявления вроде =CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO") где A1 - это ячейка, которая содержит данные имени, а A2 - данные фамилии, например.

    • CHAR(10) добавляет символ новой строки к окончательному результату и GO позволит нам запустить это INSERT и переходите к следующему, даже если есть какие-либо ошибки.

  2. Выделите ячейку своим =CONCATENATION() формула

  3. Shift + End, чтобы выделить тот же столбец в остальных строках

  4. На ленте> Главная> Редактирование> Заливка> Щелкните вниз

    • Это применяет формулу на всем протяжении листа, поэтому вам не нужно копировать-вставлять, перетаскивать и т. Д. Потенциально вниз по тысячам строк вручную.

  5. Ctrl + C, чтобы скопировать сформулированный SQL INSERT заявления

  6. Вставить в SSMS

  7. Вы заметите, что Excel, возможно, неожиданно, добавил двойные кавычки вокруг каждого из ваших INSERT а также GOкоманды. Это "особенность" (?) Копирования многострочных значений из Excel. Вы можете просто найти и заменить"INSERT а также GO" с участием INSERT а также GO соответственно, чтобы очистить это.

  8. Наконец, вы готовы запустить процесс импорта

  9. После завершения процесса проверьте окно сообщений на наличие ошибок. Вы можете выбрать все содержимое (Ctrl + A) и скопировать в Excel и использовать фильтр столбца, чтобы удалить все успешные сообщения, и у вас останутся все ошибки.

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

Источник

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

Я пытался сделать это, сначала экспортируя данные с сервера наCSV/txtа затем импортировать его в мою локальную таблицу.

Оба решения: с записью запроса на импорт CSVили использование мастера импорта данных SSMS всегда приводило к ошибкам (ошибки были очень общими, что говорило о проблеме синтаксического анализа). И хотя я ничего особенного не делал, просто экспорт вCSVа затем пытаюсь импортировать CSV к местным DB, ошибки были всегда.

Я пытался посмотреть на раздел сопоставления и предварительный просмотр данных, но всегда был большой беспорядок. И я знаю, что основная проблема исходила от одного изtable столбцы, которые содержали JSON а также SQL синтаксический анализатор трактовал это неправильно.

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

Что я сделал, так это то, что я использовал мастер экспорта на внешнем сервере.

Вот шаги, чтобы повторить тот же процесс:
1) Щелкните правой кнопкой мыши базу данных и выберитеTasks -> Export Data...

2) Когда откроется Мастер, выберите Далее и вместо "Источник данных:" выберите "Собственный клиент SQL Server".

В случае внешнего сервера вам, скорее всего, придется выбрать "Использовать аутентификацию SQL Server" для "Режим аутентификации:".

3) После нажатия кнопки " Далее" вы должны выбрать размещение.
Для этого снова выберите "Собственный клиент SQL Server".
На этот раз вы можете предоставить свой локальный (или какой-либо другой внешнийDB) DB.

4) После нажатия кнопки Далее у вас есть два варианта: скопировать всю таблицу из одной. DBдругому или запишите запрос, чтобы указать точные данные для копирования. В моем случае мне нужна была не вся таблица (она была слишком большой), а только ее часть, поэтому я выбрал "Написать запрос, чтобы указать данные для передачи".

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

5) И, наконец, необходимо указать целевую таблицу, в которой будут выбираться данные.

Предлагаю оставить это как [dbo].[Query] или какой-то обычай Table имя на случай, если у вас возникнут ошибки при экспорте данных или если вы не уверены в данных и хотите продолжить их анализ, прежде чем переходить к нужной таблице.

А теперь перейдите прямо к концу мастера, нажав кнопки " Далее" / "Готово".

Источник

Возможно, это не совсем то, о чем вы спрашиваете, но другой вариант — использовать подключаемый модуль CSV Lint для Notepad++ .

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

Сценарий SQL будет содержать INSERTоператоры для каждой строки csv пакетами по 1000 записей, а также настроить любые значения даты и времени и десятичные числа. Плагин автоматически определяет типы данных в csv и включает CREATE TABLEчасть с правильными типами данных для каждого столбца.

Источник

Как было сказано выше, вам нужно добавить FORMAT а также FIELDQUOTE варианты объемной вставки .CSVданные в SQL Server. В вашем случае оператор SQL будет выглядеть так:

      BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '""',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)

Хотя BULK INSERTв SSMS отлично подходит для одноразового задания импорта, в зависимости от вашего варианта использования вам могут понадобиться некоторые другие параметры внутри SSMS или с использованием сторонних разработчиков. Вот подробное руководство, описывающее различные варианты импорта файлов CSV в SQL Server, включая способы автоматизации (я имею в виду расписание) процесса и указания FTP или хранилища файлов для расположения CSV.

Источник

Импортируйте файл в Excel, сначала открыв Excel, затем перейдите в DATA, импортируйте из TXT-файла, выберите расширение csv, в котором будут сохранены 0 префиксных значений, и сохраните этот столбец как TEXT, потому что в противном случае Excel отбросит ведущий 0 (НЕ дважды открыть в Excel, если у вас есть числовые данные в поле, начинающемся с 0 [ноль]). Затем просто сохраните как текстовый файл с разделителями табуляции. Когда вы импортируете в Excel, вы получаете возможность сохранить как GENERAL, TEXT и т. Д. Выберите TEXT, чтобы кавычки в середине строки в поле, таком как YourCompany,LLC, также сохранялись...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

Я бы хотел использовать функциональность FORMAT и Fieldquote, но это не поддерживается в моей версии SSMS

Источник

Как было указано выше, вам необходимо добавить параметры FORMAT и FIELDQUOTE для массовой вставки данных .CSV в SQL Server. Для вашего случая оператор SQL будет выглядеть так:

      BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '""',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)

Хотя в SSMS отлично подходит для одноразового задания импорта, в зависимости от вашего варианта использования вам могут понадобиться некоторые другие параметры внутри SSMS или с использованием сторонних поставщиков. Вот подробное руководство, описывающее различные варианты импорта файлов CSV в SQL Server, включая способы автоматизации (я имею в виду расписание) процесса и указания FTP или файловых хранилищ для местоположения CSV.

Источник

Может быть SSMS: как импортировать (копировать / вставить) данные из Excel может помочь (если вы не хотите использоватьBULK INSERT или нет на это разрешений).

Источник

Я знаю, что есть принятый ответ, но все же я хочу поделиться своим сценарием, который, возможно, поможет кому-то решить их проблемуИНСТРУМЕНТЫ

  • ASP.NET
  • ПЕРВЫЙ ПОДХОД К КОДУ EF
  • SSMS
  • EXCEL

СЦЕНАРИЙ Я загружал набор данных в формате CSV, который позже должен был отображаться в представлении. Я пытался использовать массовую загрузку, но мне не удалось загрузить какBULK LOAD использовал

FIELDTERMINATOR = ','

и ячейка Excel также использовала , однако я также не мог использовать Flat file source прямо потому, что я использовал Code-First Approach и делаю это только в модели в SSMS DB, а не в модели, свойства которой мне пришлось использовать позже.

РЕШЕНИЕ

  1. Я использовал источник плоского файла и сделал таблицу БД из файла CSV (щелкните правой кнопкой мыши DB в SSMS -> Импортировать плоский файл -> выберите путь CSV и выполните все настройки, как указано)
  2. Созданный класс модели в Visual Studio (вы ДОЛЖНЫ СОХРАНИТЬ все типы данных и имена такими же, как у файла CSV, загруженного в sql)
  3. использовать Add-Migration в консоли пакета NuGet
  4. Обновить БД
Источник
Другие вопросы по тегам