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

Импорт 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/ – онлайн декодер

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *