Интеграция импорт и парсинг файла CSV
Процесс импорта и парсинга файла CSV происходит следующим образом.
Загружается файл CSV через ресурс Содержимое файла (fileContent).
Полученный файл передается в виде строки в процедуру парсинга - as_parsingCSV.
Алгоритм парсинга.
- Входная строка разбивается на строки – разделителем строки считается CHAR(10).
- Первая строка считается заголовком – названиями полей. Кол-во столбцов может быт переменное.
- В БД создается временная таблицы с полями из заголовка.
- После создания таблицы обрабатываются строки, начиная со 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
Примеры удобных таблиц и форм Примеры дашборда Примеры графиков и диаграмм Пример календаря Таблица с данными по API Пример вывода карты с точками Виды полей форм Пример виджета статистики Пример дерева иерархии Пример компонента Временная линия Пример карточки товаров Пример учета загрузки ресурса Пример Канбан доски Пример мастер формы Пример выгрузки документов Excel/Word Пример заполнения данных по ИНН- Руководства
- Основа Falcon Space
- Основные компоненты
- Возможности Falcon Space
- Подсистемы
- Дополнительные компоненты
- Интеграции Интеграция импорт и парсинг файла CSV Интеграция API DaData.ru подсказки по адресам Интеграция API IpGeoBase Город по IP-адресу Интеграция API DaData.ru Город по IP-адресу Генерация RSS в SQL процедуре Прием платежей через Яндекс.Кассу Онлайн-платежи. Интеграция с Робокассой (платежный шлюз) Телеграм боты и отправка сообщений в Telegram Zapier интеграция на платформе Falcon Space Интеграция Яндекс YML. Импорт-экспорт каталога товаров в YML Интеграция с Яндекс XML API Интеграция коллтрекинга МАНГО ОФИС (режим Площадка) Интеграция API Курсы валют Центрального Банка РФ Интеграция API Почта РФ Интеграция API Служба доставки СДЭК (CDEK) Интеграция API Служба доставки Деловые линии Интеграция API Яндекс.Вебмастер Сканирование штрих кодов и QR кодов через камеру и с картинок Видеочат. Интеграция с Vox Implant Получение данных контрагента по ИНН Базовая интеграция с Jitsi Meet (звонки с видео)
- HOWTO
- HOWTO Таблицы
- HOWTO Формы
- HOWTO JS
- HOWTO CSS
- HOWTO Верстка
- Решение проблем
Платформа Falcon Space
Это снижение стоимости владения
за счет меньшего количества людей для поддержки
Это быстрое внесение изменений
по ходу эксплуатации программы
Это современный интерфейс
полная адаптация под мобильные устройства
Бесплатное обучение разработке на Falcon Space
