Интеграция импорт и парсинг файла CSV

Процесс импорта и парсинга файла CSV происходит следующим образом.

Загружается файл CSV через ресурс Содержимое файла (fileContent).

Полученный файл передается в виде строки в процедуру парсинга - as_parsingCSV.

Алгоритм парсинга.

1.  Входная строка разбивается на строки – разделителем строки считается CHAR(10).
2.  Первая строка считается заголовком – названиями полей. Кол-во столбцов может быт переменное.
3.  В БД создается временная таблицы с полями из заголовка.
4.  После создания таблицы обрабатываются строки, начиная со 2-й. Строки деляться на столбцы – разделитель передается в параметрах (@divider). И вставляются в таблицу. Тип всех данных будет текстовый - NVARCHAR(MAX).

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

Параметры процедуры as_parsingCSV.

Примечания.

Протестирована работа с разделителями столбцов – запятая и точка с запятой.

Возможные типы ошибки.

'Ошибка! Получена пустая строка.'

'Ошибка! Не найден символ перевода строки CHAR(10).'

'Ошибка! Не найден символ разделения столбцов '

'Ошибка! Не найден символ разделения столбцов - '+ @divider +' после первой строки (заголовок). Возможно нет данных.'

Пример результата работы процедуры.

Пользователь: admin2;
Получено строк: 19;
Разделитель колонок: ,
Удалено пустых строк: 0;
Табл. #CSVLoad_tmp: создана;
Вставлено строк с данными: 18;
Колонки: [QuotaAmount] , [StartDate] , [OwnerName] , [Username] ;
Cтрок с данными в таблице: 18;

CREATE PROCEDURE [dbo].[as_parsingCSV]
				 @strCSV			nvarchar(max) -- -- входной параметр -- строка
				,@divider			nvarchar(12)  = ','		-- разделитель можно указать другой, например ;
			    ,@param_username	nvarchar(256) = NULL	-- v1_4 -- имя пользователя
---------------------------------------------------------------------------------

AS
BEGIN

SET NOCOUNT ON;

DECLARE @result_str nvarchar(max)=N'' -- строка вывод статуса результатов

-- имя пользователя -- если пользователь не указан, берем серверного CURRENT_USER
SET @param_username = ISNULL( @param_username , CURRENT_USER)
SELECT @result_str = @result_str + N'Пользователь: '+ @param_username + '; ' + CHAR(10)
----------------------------------------------------------------------------------------------------

-- проверка на длину строки
DECLARE @lenStr int = 0
SELECT  @lenStr = LEN ( ISNULL( @strCSV , '' ) )

IF @lenStr = 0
BEGIN
	SELECT 0 Result, 'Ошибка! Получена пустая строка.' Msg
	RETURN
END

-- CHARINDEX -- поиск одного символьного выражения внутри второго символьного выражения, возвращая начальную позицию первого выражения, если найдено.
-- проверка наличия символов - перевод строки CHAR(10) / разделитель - @divider
DECLARE @posCHAR10 int = 0
SELECT  @posCHAR10 = ISNULL( CHARINDEX ( CHAR(10) , @strCSV, 1) , 0 )

IF @posCHAR10 = 0
BEGIN
	SELECT 0 Result, 'Ошибка! Не найден символ перевода строки CHAR(10).' Msg
	RETURN
END

DECLARE @posComma int = 0
SELECT  @posComma = ISNULL( CHARINDEX ( @divider , @strCSV, 1) , 0 )

IF @posComma = 0
BEGIN
	SELECT 0 Result, 'Ошибка! Не найден символ разделения столбцов '+ @divider AS Msg
	RETURN
END

-- найти первую запятую во 2й строке
DECLARE @posComma2 int = 0
SELECT  @posComma2 = CHARINDEX ( @divider , @strCSV, @posCHAR10 )

IF @posComma2 = 0
BEGIN
	SELECT 0 Result, 'Ошибка! Не найден символ разделения столбцов - '+ @divider +' после первой строки (заголовок). Возможно нет данных.' AS Msg
	RETURN
END

-- Анализ вх.строки на ошибки закончен -- считаем, что получена правильная строка

----------------------------------------------------------------------------------------------------
-- 1 Деление по строкам -- 1-я строка - заголовок

-- результат сохранить в табличную переменную
DECLARE @T_allStings table ( id int IDENTITY(1,1) NOT NULL
                            ,valueSting nvarchar(max) NULL
							,rowNum int NULL -- номер по порядку -- может не совпадать с id после удаления
                            )

INSERT INTO @T_allStings ( valueSting ) SELECT value FROM STRING_SPLIT( @strCSV, CHAR(10) );


DECLARE @T_allStingsСounter int = 0
SELECT  @T_allStingsСounter = ISNULL ( COUNT( * ) , 0 ) FROM @T_allStings
-- Получено НН строк
SELECT @result_str = @result_str + N'Получено строк: '+CAST( @T_allStingsСounter AS VARCHAR(10) ) + '; ' + CHAR(10)
SELECT @result_str = @result_str + N'Разделитель колонок: '+ @divider + ' '  + CHAR(10)
----------------------------------------------------------------------------------------------------

-- Удалить строки с пустыми значениями LEN( valueSting ) = 0  -- иначе возникает ошибка
DECLARE @valueStingEmptyСounter int = 0
SELECT  @valueStingEmptyСounter = ISNULL ( COUNT( * ) , 0 ) FROM @T_allStings WHERE LEN( valueSting ) = 0


IF @valueStingEmptyСounter > 0
	DELETE  FROM @T_allStings WHERE LEN( valueSting ) = 0

SELECT @result_str = @result_str + N'Удалено пустых строк: '+CAST( @valueStingEmptyСounter AS VARCHAR(10) )  + '; '  + CHAR(10)

-- пересортировать поле - ord -- чтобы после удаления нумерация были по порядку
UPDATE T_allStings SET T_allStings.rowNum = t1.rowNum
FROM (SELECT   id, row_number() over(ORDER BY id ) rowNum
      FROM     @T_allStings
      ) as t1
     , @T_allStings AS T_allStings
WHERE T_allStings.id = t1.id

----------------------------------------------------------------------------------------------------
DECLARE @tmp_valueSting nvarchar(max)
SELECT  @tmp_valueSting = valueSting FROM @T_allStings WHERE rowNum = 1 -- id = 1

-- Заголовки файл - поля таблицы
-- Суть данного метода проста, мы последовательно записываем в переменную значение за значением (то, что есть в переменной + текущее значение), по мере считывания данных из столбца, добавляя между значениями нужный нам разделитель.
-- https://info-comp.ru/obucheniest/706-get-column-values-in-string-sql.html
DECLARE @ALTER_TABLE_AllText NVARCHAR(MAX);


-- Таблица #CSVLoad_tmp создается отдельно, ниже, тут скрипт добавления полей

SET @ALTER_TABLE_AllText ='ALTER TABLE #CSVLoad_tmp ADD '


 --Объявляем переменную для строки - перечень столбцов с типом данных - пока все столбцы NVARCHAR(MAX)
   DECLARE @ALTER_TABLE_СolumnText NVARCHAR(MAX);
   --Формируем строку
   SELECT @ALTER_TABLE_СolumnText = ISNULL( @ALTER_TABLE_СolumnText + N' NVARCHAR(MAX) NULL, ','') + QUOTENAME( t.value ) -- QUOTENAME = []
   FROM (
         SELECT value FROM STRING_SPLIT( @tmp_valueSting, @divider )
         ) as t

SET @ALTER_TABLE_СolumnText = @ALTER_TABLE_СolumnText + N' NVARCHAR(MAX) NULL ' -- дописать тип последнего поля

--  в последнем столбце остался перевод на новую строку - убираем - оказался CHAR(13)
SET @ALTER_TABLE_СolumnText = REPLACE ( @ALTER_TABLE_СolumnText  , CHAR(13) , N'' )

----------------------------------------------------------------------------------------------
-- Если в заголовке есть рус.названия полей - перекодируем function [dbo].[str_cyrillic2Latin]


-- SET @CREATE_TABLE_СolumnText = dbo.str_cyrillic2Latin(lower(ltrim(rtrim( @CREATE_TABLE_СolumnText ))))
-- регистр не меняем
SET @ALTER_TABLE_СolumnText = dbo.str_cyrillic2Latin  (ltrim (rtrim ( @ALTER_TABLE_СolumnText )))

----------------------------------------------------------------------------------------------

-- СТРОКА хранить перечень полей для вставки , разделенные запятой, БЕЗ ТИПОВ
DECLARE @insertHeaderСolumn NVARCHAR(MAX);
SET     @insertHeaderСolumn = REPLACE ( @ALTER_TABLE_СolumnText , N'NVARCHAR(MAX) NULL' , N'' )


-- полная строка ALTER табл.
SET @ALTER_TABLE_AllText = @ALTER_TABLE_AllText + @ALTER_TABLE_СolumnText -- + ')'
-- Смотрим результат -- SELECT @ALTER_TABLE_AllText AS ALTER_TABLE_AllText



-- СОЗДАНИЕ ТАБЛИЦЫ #CSVLoad_tmp
DROP TABLE if exists #CSVLoad_tmp

CREATE TABLE #CSVLoad_tmp ( id int IDENTITY(1,1) NOT NULL , [created] [datetime] DEFAULT GETDATE() )


-- в этом скрипте добавляются ALTER столбцы в таблицу - заголовки из файла
EXEC ( @ALTER_TABLE_AllText )

IF OBJECT_ID(N'tempdb..#CSVLoad_tmp', N'U') IS NOT NULL
	SELECT @result_str = @result_str + N' Табл. #CSVLoad_tmp: создана' + '; '  + CHAR(10)

IF OBJECT_ID(N'tempdb..#CSVLoad_tmp', N'U') IS  NULL
BEGIN
	SELECT @result_str = @result_str + N' Табл #CSVLoad_tmp: НЕ СОЗДАНА !' + '; '  + CHAR(10)
	SELECT 0 Result, 'Ошибка! Табл. #CSVLoad_tmp: НЕ СОЗДАНА !' Msg
	RETURN
END


----------------------------------------------------------------------------------------------------
-- Заполнение данными
-- перебор строк - построчно
-- строку - разбить на поля
-- вставить в табл

-- поле rowNum -- номер по порядку -- может не совпадать с id после удаления
-- получить кол-во строк для вставить - отнять первую строку - заголовок
DECLARE @dataStringsCounter int = 0
SELECT  @dataStringsCounter = MAX( rowNum ) FROM  @T_allStings

DECLARE @idCounter int = 2 -- счетчик перебора строк -- 1-я строка заголовок, начали со 2-й

--Объявляем переменную для строки - ДАННЫЕ столбцов с типом данных - пока все столбцы NVARCHAR(MAX)
DECLARE @insertDataСolumn NVARCHAR(MAX);
DECLARE @quotationFirstPos int = 0 -- позиция первой кавычки в сформированной строке
DECLARE @insertStr NVARCHAR(MAX);  -- Строка вставки полностью с заголовком и данными

SELECT @insertHeaderСolumn = N'INSERT INTO #CSVLoad_tmp ( ' + @insertHeaderСolumn + ') VALUES ( ' -- дописать постоянную часть вставки

WHILE @idCounter <=  @dataStringsCounter
    BEGIN
	    -- SELECT @idCounter AS idCounter

		SELECT  @tmp_valueSting   =  N'' -- обнуление
		SELECT  @insertDataСolumn =  N'' -- обнуление
		-- перебор строк - построчно
		SELECT  @tmp_valueSting = valueSting FROM @T_allStings WHERE rowNum = @idCounter -- id = @idCounter

		-- строку - разбить на поля
		   --Формируем строку
		   SELECT @insertDataСolumn = ISNULL( @insertDataСolumn + N' , ','') + ''''+( t.value )+'''' -- кавычки т.к. все текст
		   FROM (
				 SELECT value FROM STRING_SPLIT( @tmp_valueSting, @divider )
				 ) as t

		-- позиция первой кавычки в сформированной строке
		SELECT @quotationFirstPos = CHARINDEX ( '''' , @insertDataСolumn, 1)
		-- если позиция больше 1 , то впереди строки появились пробелы и запятая (непонятно откуда?)
		-- обрезаем - чтобы строка начиналась с кавычки
		IF  @quotationFirstPos > 1
			SELECT @insertDataСolumn =  SUBSTRING ( @insertDataСolumn , @quotationFirstPos , len(@insertDataСolumn) )

		-- Формирование строки вставки
		SELECT @insertStr = @insertHeaderСolumn + @insertDataСolumn + ') '
		--  в последнем столбце остался перевод на новую строку - убираем - оказался CHAR(13)
		SET @insertStr = REPLACE ( @insertStr , CHAR(13) , N'' )

		-- SELECT @insertStr
		-- SELECT CAST( (@idCounter) AS VARCHAR(10) )  + ' - ' + @insertStr

		-- выполнение вставки в табл
		EXEC ( @insertStr )

		--
        SET @idCounter = @idCounter + 1
    END;

SELECT @result_str = @result_str + N' Вставлено строк с данными: '+CAST( (@dataStringsCounter -1) AS VARCHAR(10) )  + '; '  + CHAR(10)
----------------------------------------------------------------------------------------------------
-- Данные записаны в табл.
-- анализ данных

-- список колонок -- вырезать часть строки из скрипта вставки, ориентир - скобки
-- позиция открывающая скобка
DECLARE @posBracket1 int = 0
SELECT  @posBracket1 = CHARINDEX ( '(' , @insertHeaderСolumn , 1)
-- позиция закрывающая скобка
DECLARE @posBracket2 int = 0
SELECT  @posBracket2 = CHARINDEX ( ')' , @insertHeaderСolumn , 1)
-- длина между скобками

SELECT @result_str = @result_str + N' Колонки: '+ SUBSTRING ( @insertHeaderСolumn , @posBracket1+1 , ( @posBracket2 - (@posBracket1+1) ) )  + '; '  + CHAR(10)

DECLARE @CSVLoadСounter int = 0
SELECT  @CSVLoadСounter = ISNULL ( COUNT( * ) , 0 ) FROM #CSVLoad_tmp -- [dbo].[CSVLoad_tmp]
SELECT  @result_str = @result_str + N' Cтрок с данными в таблице: '+ CAST( @CSVLoadСounter AS VARCHAR(10) )  + '; '  + CHAR(10)

----------------------------------------------------------------------------------------------------
-- SELECT * FROM #CSVLoad_tmp
-- удалить табл. в конце --
DROP TABLE #CSVLoad_tmp

-- результат в формате -- 1 SELECT (Result, Msg)
SELECT 1 Result, @result_str AS Msg


END