Falcon Space

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

Оглавление

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

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

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

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

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

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

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

№

Параметр

Тип данных

Значение по умолчанию

Прим.

1

@strCSV

nvarchar(max)

нет

 

2

@divider

nvarchar(12)

','

 

3

@param_username

nvarchar(256)

NULL

если не указан, будет значение CURRENT_USER

Примечания.

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

 

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

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

'Ошибка! Не найден символ перевода строки 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

Falcon Space - функциональная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

Google поиск по нашей документации

Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Создать концепт в личном кабинете
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Демо решений
Сайт использует Cookie. Правила конфиденциальности OK