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

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

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

Решение

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

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

Решение

If you're using BULK insert then there is no way to handle double quotes, data will be inserted with double quotes into rows. after inserting the data into table you could replace those double quotes with ''.

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

3) How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)?

Решение

Для обработки строк, которые не загружены в таблицу из-за недопустимых данных или формата, можно использовать Свойство 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, чтобы запустить пакет.

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

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 установлено значение, отличное от запятой (например, вертикальная черта). Затем это сгенерирует для вас файл, разделенный каналом (или чем-то еще), который вы затем сможете импортировать. Это описано здесь.

Сначала вам нужно импортировать файл 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. Напишите собственный сценарий в диспетчере импорта, чтобы исключить / изменить данные, которые вы ищете (или запустите основной сценарий, чтобы тщательно изучить данные, которые вы хотите удалить).

Удачи.

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

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

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

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

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

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

Клиент SQL

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

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

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

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

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

По этой ссылке есть изображения.

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

Я пытался сделать это, сначала экспортируя данные с сервера на 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 на тот случай, если у вас возникнут ошибки при экспорте данных или если вы не уверены в данных и хотите продолжить их анализ, прежде чем переходить к точной таблице, которую вы хотите.

And now go straight to the end of the Wizard by hitting Next/Finish buttons.

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

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

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

   • CHAR(10) adds a newline character to the final result and GO will allow us to run this INSERT and continue to the next even if there are any errors.

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

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

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

   • This applies the formula all the way down the sheet so you don't have to copy-paste, drag, etc. down potentially thousands of rows by hand

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

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

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

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

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

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

Импортируйте файл в Excel, сначала открыв excel, затем перейдя в DATA, импортируйте из файла TXT, выберите расширение csv, которое сохранит 0 префиксных значений, и сохраните этот столбец как TEXT, потому что в противном случае excel отбросит ведущий 0 (НЕ дважды щелкните для открытия в Excel, если у вас есть числовые данные в поле, начинающемся с 0 [ноль]). Затем просто сохраните как текстовый файл с разделителями табуляции. Когда вы импортируете в Excel, у вас есть возможность сохранить как ОБЩИЙ, ТЕКСТ и т. Д. Выберите ТЕКСТ, чтобы кавычки в середине строки в таком поле, как 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.

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

• 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. Обновить БД

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