Рубрика: Статья

Импорт 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/ – онлайн декодер
Ошибка Could not obtain exclusive lock on database ‘model’

Ошибка Could not obtain exclusive lock on database ‘model’

При создании базы данных ошибка — «Could not obtain exclusive lock on database ‘model’. Retry the operation later.«

Exclusive lok

Справка Microsoft Docs:

База данных model используется в качестве шаблона для всех баз данных, созданных для экземпляра SQL Server

При выполнении инструкции CREATE DATABASE первая часть базы данных создается путем копирования в нее содержимого базы данных model . Оставшаяся часть новой базы данных заполняется пустыми страницами.

Ссылка

 

В данном случае кто-то уже блокирует базу, поэтому и возникает ошибка.

Увидеть кто блокирует базу поможет следующий скрипт. Есть кто-то уже использует базу model запрос вернёт Session Id. Данную сессию необходимо разъединить.

IF EXISTS (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE resource_database_id = DB_ID('model')
        )
BEGIN
    PRINT 'Model Database in use!!'
 
    SELECT *
    FROM sys.dm_exec_sessions
    WHERE session_id IN (
            SELECT request_session_id
            FROM sys.dm_tran_locks
            WHERE resource_database_id = DB_ID('model')
            )
END
ELSE
    PRINT 'Model Database not in used.'

После поисков в интернете, следующий скрипт удаляет Все соединение с базой данных. Таким образом блокировка model снимется.

USE [master]
GO

 DECLARE @kill varchar(max) = '';
 SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;'
 FROM master..sysprocesses 

EXEC (@kill);

 

После данной процедуры ошибка при создании БД прошла.

 

Перевод секунд в часы SQL

Перевод секунд в часы SQL

Классическая задача по программированию. Дано количество секунд, необходимо перевести их в часы в формате ЧЧ:ММ:СС.

Справка

  • 1 час = 60 минут = 3600 секунд
  • 1 день = 24 часа = 1440 минут = 86400 секунд

Находим количество часов как деление секунд на количество секунд в одном часе. Количество минут будет остача от деление на 3600 поделить на 60.

Количество секунд это остача от деления на 60.

Дано 10 000 секунд.

Часы: 10 000 / 3600 = 2 часа

Минуты: 10 000 % 3600 / 60 = 2800 / 60 = 46 минут

Секунды: 10 000 % 60 = 40 секунд

10 000 секунд = 2:46:40 часа.

В MS SQL запишем данный алгоритм:

DECLARE @Seconds int = 10000
SELECT [Hours]   = @Seconds / 3600
      ,[Minutes] = @Seconds % 3600 /60
      ,[Seconds] = @Seconds % 60

 

SQL
Результат запроса

Как видим 2 часа, 46 минут и 40 секунд. А что если нам необходимо выводить «0» перед числом, чтобы получилось 02:46:40?

Самое просто это сравнение полученного числа с 10. Если число меньше 10 добавляем 0 перед цифрой. Для проверки используем логическую функцию IIF

DECLARE @Seconds	int	= 10000
SELECT	 [Hours]	= @Seconds / 3600
    ,[Minutes]	= @Seconds % 3600 /60
    ,[Seconds]	= @Seconds % 60
SELECT	[HH:MM:SS]	=
   IIF(@Seconds / 3600 < 10, ''0'', '''')		+ CONVERT(nvarchar, @Seconds / 3600)		-- Часы
  + '':'' +
    IIF(@Seconds % 3600 /60 < 10, ''0'', '''')	+ CONVERT(nvarchar, @Seconds % 3600 /60)	-- Минуты
  + '':'' +
    IIF(@Seconds % 60 < 10,	''0'', '''')		+ CONVERT(nvarchar, @Seconds % 60)			-- Секунды

Результат:

SQL Result Pane
Результат запроса

Удобно использовать этот алгоритм в виде функции.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SecondsToHour] (@Seconds int)
RETURNS nvarchar(max) 
AS
BEGIN
 DECLARE @result nvarchar(max)
 SET @result = 
  IIF(@Seconds / 3600 < 10, ''0'', '''') + CONVERT(nvarchar, @Seconds / 3600) -- Часы\
   + '':'' +
   IIF(@Seconds % 3600 /60 < 10, ''0'', '''') + CONVERT(nvarchar, @Seconds % 3600 /60) -- Минуты
   + '':'' +
   IIF(@Seconds % 60 < 10, ''0'', '''') + CONVERT(nvarchar, @Seconds % 60) -- Секунды
   return @result;
  END

 

 

SELECT [dbo].[SecondsToHour] ( 10000 )

 

Результат запроса

В MS SQL перевести секунды в часы можно воспользовавшись функцией DATEADD().\r\n

SELECT DATEADD(ss, @Seconds, ''00:00'')

 

1900-01-01 02:46:40.000

Установить формат ЧЧ:ММ:СС можно сделав конвертацию в 108 стиль.\r\n

SELECT CONVERT(nvarchar, DATEADD(ss, @Seconds, ''00:00''), 108)\r\n	02:46:40

Данный способ возвращает только 24 часа. От 00:00:00 до 23:59:59. При количестве секунд 86400 и более, счётчик дней увеличивается.

SELECT DATEADD(ss, 86405, ''00:00'')\r\n  1900-01-02 00:00:05.000