Импорт csv файла в базу данных MS SQL

Если CSV файлик 500 МБ, его необходимо перенести в таблицу базы данных.

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

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

План

1. Перекодировать исходный файл в UTF-8 кодировку
2. Импорт в базу данных по 50 тыс. записей за одну итерацию

1. Перекодировать файл в другую кодировку

Это можно реализовать через PoweShell.

В коде заменить входной файл и исходящий.

Входной файл - $inFile

Выходной файл - $outFile . Файл будет создан автоматически.

В месте GetEncoding(1251) укажите Вашу исходную кодировку. В данном случае WIN-1251

$inFile  = 'D:\Temp\_2579.csv'

$outFile = 'D:\Temp\_2579_powershell_encoding.csv'


$reader = New-Object IO.StreamReader ($inFile, [Text.Encoding]::GetEncoding(1251))

$writer = New-Object IO.StreamWriter ($outFile, $false, [Text.Encoding]::UTF8)


while ($reader.Peek() -ge 0) {

  $writer.WriteLine($reader.ReadLine())

}


$reader.Close(); $reader.Dispose()

$writer.Close(); $writer.Dispose()

Сохраните результат в формате *.ps1

Запустите powershell с правами администратора.

Перейдите в директорию, где сохранён скрипт.

Пример: cd D:\Temp\

Введите название скрипта с приставкой .\

Получится: .\powerchell_encoding_file.ps1 (можно начать писать название файла и нажать TAB )

Нажать Enter

Всё файл готов. В указанном месте $outFile будет создать файл с кодировкой UTF-8.

1. Запись данных в базу данных

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

Количество и названия колонок должны быть идентичными тем, которые находятся в CSV файле.

Следующим етапом будет создание PoweShell скрипта.

# Database variables

$sqlserver = "SQL"

$database = " Bundle "

$table = " Bundle.dbo.[_import_Utf-8]"

$UserId = "User1"

$Password = "Pass1"


# CSV variables

$csvfile = "D:\Temp\_2579_powershell_encoding.csv"

$csvdelimiter = ";"

$firstRowColumnNames = $true


################### No need to modify anything below ###################

Write-Host "Script started..."

$elapsed = [System.Diagnostics.Stopwatch]::StartNew()

[void][Reflection.Assembly]::LoadWithPartialName("System.Data")

[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")


# 50k worked fastest and kept memory usage to a minimum

$batchsize = 50000


# Build the sqlbulkcopy connection, and set the timeout to infinite

$connectionstring = "Data Source=$sqlserver; User ID = $UserId; Password = $Password; Initial Catalog=$database;"

$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)

$bulkcopy.DestinationTableName = $table

$bulkcopy.bulkcopyTimeout = 0

$bulkcopy.batchsize = $batchsize


# Create the datatable, and autogenerate the columns.

$datatable = New-Object System.Data.DataTable


# Open the text file from disk

$reader = New-Object System.IO.StreamReader($csvfile)

$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)

if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }


foreach ($column in $columns) {

 $null = $datatable.Columns.Add()

}


# Read in the data, line by line

while (($line = $reader.ReadLine()) -ne $null)  {

 $null = $datatable.Rows.Add($line.Split($csvdelimiter))

 $i++; if (($i % $batchsize) -eq 0) {

 $bulkcopy.WriteToServer($datatable)

 Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."

 $datatable.Clear()

 }

}


# Add in all the remaining rows since the last clear

if($datatable.Rows.Count -gt 0) {

 $bulkcopy.WriteToServer($datatable)

 $datatable.Clear()

}


# Clean Up

$reader.Close(); $reader.Dispose()

$bulkcopy.Close(); $bulkcopy.Dispose()

$datatable.Dispose()


Write-Host "Script complete. $i rows have been inserted into the database."

Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"

# Sometimes the Garbage Collector takes too long to clear the huge datatable.

[System.GC]::Collect()

В первом блоке заполняем информацию для подключения к SQL-серверу:

$sqlserver = "SQL"   - Название SQL-сервера

$database = " Bundle "         - База данных

$table = "Bundle.dbo.[_import_Utf-8]"      - Таблица

$UserId = "User1"     - Логин

$Password = "Pass1" - Пароль

В следующем блоке заполняем информацию о CSV-файле

$csvfile = "D:\Temp\_2579_powershell_encoding.csv"     - Расположение файла

$csvdelimiter = ";"    - разделитель

$firstRowColumnNames = $true     - Есть ли название колонок в файле?

Сохраняем в формате *.ps1. Запускаем через PowerShell.

Полезные ресурсы

1. https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/#comment-50531 – страница с powershell скриптом
2. https://stackoverflow.com/questions/41392833/encoding-csv-content-to-utf-8 - перекодировка файла
3. https://2cyr.com/decode/ – онлайн декодер