Интеграция импорт и парсинг файла 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
Google поиск по нашей документации
- Руководства
- Основа Falcon Space
- Основные компоненты
- Возможности
- Коммуникация с пользователем
- Дизайн, стилизация
- Лендинги
- Универсальный API
- Примеры интеграций Прием платежей через Яндекс.Кассу Онлайн-платежи. Интеграция с Робокассой (платежный шлюз) Zapier интеграция на платформе Falcon Space Интеграция коллтрекинга МАНГО ОФИС (режим Площадка) Интеграция API DaData.ru подсказки по адресам Интеграция API Курсы валют Центрального Банка РФ в веб-платформе Falcon Space Интеграция API Почта РФ Интеграция API Служба доставки СДЭК (CDEK) Интеграция API Служба доставки Деловые линии Интеграция импорт и парсинг файла CSV Интеграция API IpGeoBase Город по IP-адресу Интеграция API DaData.ru Город по IP-адресу Как вычислить расстояние между 2 точками с координатами через Google Maps Сканирование штрихкодов и QR кодов через камеру и с картинок Получение данных контрагента по ИНН Прием платежей на сайте через CloudPayments Как сделать интеграцию с Мой Склад Внедрение подсказок dadata на сайт Вывод точек на карте Яндекс. Интеграция с Яндекс Карты Интеграция с телефонией Zadarma.com Получение данных о контрагенте - интеграция с сервисом ЗаЧестныйБизнес Интеграция с AMO CRM Как импортировать данные в базу CRM из Google Контакты Вход/регистрация через ВКонтакте(vk.com) Интеграция CRM с онлайн чатом на сайте (Replain) Как связать yandex metrika clientID с пользователем на сайте и посмотреть полный путь его по сайту? Телеграм. Работа с файлами Как сделать обработку входящей почты (создание клиента в CRM из Email) Интеграция сайта с ChatGPT (openAI) Интеграция с Яндекс Метрика Интернет-эквайринг Тинькоф Банк Интеграция с платежным шлюзом LifePay Как сделать вебхук для передачи данных с Тильды на Falcon Space Пополнение средств через Yookassa (Юкасса) Тинькофф Банк - уведомление в учетную систему по операциям с расчетным счетом Интеграция с Ютуб. Получение роликов канала
- Каталоги
- Навигация
- Документы
- Дополнительные компоненты
- Продвижение, SEO
- Системные моменты
- Системное администрирование
- HOWTO
- Таблицы
- Формы
- Загрузка файлов, картинок
- Работа с SQL
- HOWTO JS
- HOWTO Верстка
- Решение проблем
Falcon Space
Это снижение стоимости владения
за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом
Это быстрое внесение изменений
по ходу эксплуатации программы. Как создается функционал на платформе
Это простой удобный интерфейс
адаптация под мобильные устройства. Про юзабилити платформы