Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
Надо было распарсить CSV средствами TSQL: SSIS по сложным причинам версионного ада использовать нельзя. STRING_SPLIT тоже, хоть и 2017 на дворе. Да и одного его мало: надо же сплитнуть файл на строки, потом через CROSS APPLY строки на поля, потом поля на имена столбцов как-то мапить. Так что я возжелал найти такую процедуру чтобы ты ей файл, а она тебе таблицу с именованными по первой строке столбцами. Хорошо искал, два дня. А может плохо искал, тогда простите. Короче, пришлось самому писать. Я здесь положу, чтобы не потерялось, ОК? Там много зла типа XML, курсоров, динамического SQL и xp_cmdshell, но коли у Вас есть права на сервере то всё будет работать и достаточно быстро. Проверял на 2008 R2 (compat level 100).

Предварительные требования:
1. xp_cmdshell включён и SQL Server имеет права права чтения целевого файла.
2. Надо создать маленький пользовательский тип чтобы в динамическом SQL использовать:

CREATE TYPE tblNumNumStr AS  TABLE (iKey INT, iOrder INT, sValue NVARCHAR(MAX))


Ну и сама процедура. Фунцию с динамическим SQL и всей этой магией не собрать :(
CREATE PROCEDURE CSVToDataset (
	@FullFileName NVARCHAR (MAX),
	@delimiter VARCHAR(10)
)
AS
BEGIN
	DECLARE @xml XML, @str VARCHAR(255)

	DECLARE @Headers tblNumNumStr, @Values tblNumNumStr

	--Open file
	DECLARE @rc INT        
	DECLARE @res VARCHAR(MAX) = ''     
	DECLARE @output TABLE (line NVARCHAR(255) null)

	SET @rc = NULL
	SET @res = ''     
	DELETE FROM @output 

	SET @str =  'type ' + @FullFileName

	INSERT @output
	EXEC @rc = master..xp_cmdshell @str;

	SELECT TOP 1 @str = line  from @output

	IF @str = 'The system cannot find the file specified.'
	BEGIN
		RAISERROR(@str, 16, 2)
		RETURN
	END

	SET @xml = cast(('<ROOT><X>'+replace(@str, @delimiter, '</X><X>')+'</X></ROOT>') as xml)
	INSERT INTO @Headers
	SELECT
		NULL,
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)), 
		T.c.query('.').value('.', 'VARCHAR(100)')
	FROM @xml.nodes('/ROOT/X') T(c) 

	--*Proceed lines
	DECLARE LINES CURSOR LOCAL STATIC FOR
		SELECT * FROM @output
		WHERE 
			SUBSTRING(line, 1, 8) <> 'STATDATE'

	DECLARE @curLine NVARCHAR(MAX)
	DECLARE @curLineNo INT = 1

	OPEN LINES

	FETCH NEXT FROM LINES INTO @curLine
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		SET @xml = cast(('<ROOT><X>'+replace(@curLine, @delimiter, '</X><X>')+'</X></ROOT>') as xml)
		INSERT INTO @Values
		SELECT 
			@curLineNo,
			ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN, 
			T.c.query('.').value('.', 'VARCHAR(100)')
		FROM @xml.nodes('/ROOT/X') T(c) 

		SET @curLineNo = @curLineNo + 1
		FETCH NEXT FROM LINES INTO @curLine
	END
	CLOSE LINES
	DEALLOCATE LINES
	--*


	DECLARE @dSQL NVARCHAR(MAX)

	DECLARE @curOrder INT, @curField NVARCHAR(MAX)
	DECLARE FIELDS CURSOR LOCAL STATIC FOR
		SELECT iOrder, sValue FROM @Headers WHERE sValue <> '' ORDER BY iOrder

	SET @dSQL = 'SELECT '

	OPEN FIELDS

	FETCH NEXT FROM FIELDS INTO @curOrder, @curField
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		SET @dSQL = @dSQL + 'MAX(CASE WHEN iOrder = ' + CONVERT(VARCHAR, @curOrder) + ' THEN sValue ELSE '''' END) AS ' + @curField + ', '
		FETCH NEXT FROM FIELDS INTO @curOrder, @curField
	END
	CLOSE FIELDS
	DEALLOCATE FIELDS

	SET @dSQL = @dSQL + ''''' AS DUMMY FROM @Values GROUP BY iKey ORDER BY iKey'
	EXEC sp_executesql @dSQL, N'@Values tblNumNumStr READONLY', @Values

END
26 май 17, 14:58    [20515138]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
aleks2
Guest
Открой для себя BULK INSERT.
26 май 17, 15:06    [20515176]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
aleks2,

Я в курсе. А точнее в данном случае OPENROWSET. Но есть причины. И, кстати, BULK INSERT херачит ошибки в отдельный файл, который потом проверять надо, никаких своих обработчиков не вставишь. И ему, на секундочку, нужна целевая таблица. И лучше "настоящая" - чтобы вставить во временную или в табличную переменную опять таки колдовать надо. А этот запрос просто читает данные, а не читает-пишет-читает, как пришлось бы делать с использованием булки. Да мало какие ещё ограничения могут быть - булка это отдельный exe-шник, а это опенсорс :) Допиливай на здоровье. Задачи всякие по дебильности требований бывают и два способа лучше одного.
26 май 17, 15:28    [20515275]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7407
HuRN,

автор
SSIS по сложным причинам версионного ада использовать нельзя


А в этом месте, пожалуйста, поподробнее.
26 май 17, 15:58    [20515438]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
 
  IF (OBJECT_ID(N'CSVToTable', N'P') IS NULL) 
  BEGIN
	  EXEC (N'CREATE PROCEDURE CSVToTable AS RETURN 0');
  END 

GO

 
 
ALTER PROCEDURE CSVToTable 
  @CSV  varchar(MAX)    
, @Separator    char       = ';'
, @Terminator   Varchar(4) = 'CRLF' /* CR - Carriage Return ;  LF - Line Feed  ; CRLF - both*/
, @SkipN        Int        =  0 
, @GetRowOrder bit = 0 /* !!!Значимо просаживает быстродействие!!! Если мне не принципиален порядок возвращаемых строк, т.е. на входе плоский файл, то 0. Если некая иерархическая структура, включаем механизм нумерации строк.
@GetRowOrder = 1  добавит на выход стольбец XML_ROW_NUMBER , который содежит номера нод по порядку следования */ 
 
AS
BEGIN 
  SET NOCOUNT ON 
  DECLARE @ErrorMessage Varchar(2000) = '' 

 
  SET @Terminator = CASE 
                      WHEN @Terminator = 'CR'   THEN CHAR(13) 
                      WHEN @Terminator = 'LF'   THEN CHAR(10) 
                      WHEN @Terminator = 'CRLF' THEN CHAR(13) + CHAR(10) 
                    END
 -- PRINT REPLACE(REPLACE(@Terminator,CHAR(13),'#13'), CHAR(10), '#10')

 
 
  /* пропускаем N первых строк */ 
  WHILE @SkipN <> 0 
  BEGIN 
    SET @CSV = RIGHT(@CSV, LEN(@CSV) - CHARINDEX(@Terminator,@CSV))       
    SET @SkipN  = @SkipN - 1 
  END
  
  IF CHARINDEX(@Terminator, @CSV) = 1 
  BEGIN 
    SET @CSV = RIGHT(@CSV, LEN(@CSV) - 1) 
  END    
  
  IF CHARINDEX(CHAR(10), @CSV) = 1 
  BEGIN 
    SET @CSV = RIGHT(@CSV, LEN(@CSV) - 1) 
  END    
  
  IF CHARINDEX(CHAR(13), @CSV) = 1 
  BEGIN 
    SET @CSV = RIGHT(@CSV, LEN(@CSV) - 1) 
  END    
   
 
  /* скриним плохие знаки чтоб собрать XML*/ 
  SET @CSV = REPLACE (@CSV, '<', '&lt;')
  SET @CSV = REPLACE (@CSV, '>', '&rt;')
  
  /* превращаем CSV в XML*/ 
  DECLARE @MyData xml
  DECLARE @Buff varchar(MAX) 
  SET @Buff   = LEFT(@CSV, CHARINDEX(CHAR(13),@CSV)) 
  SET @CSV = '<row>' + REPLACE('<col>' + REPLACE(@CSV, @Separator, '</col><col>') + '</col>', @Terminator, '</col></row><row><col>')  
  SET @CSV = '<data>' + LEFT(@CSV, LEN(@CSV) - 16) + '</data>'
   
  /*собираем дин. эскуль т.к. мы не знаем количества столбцов в указанном файле*/  
  DECLARE 
    @MyQuery      NVarchar (MAX) = N'SELECT '
  , @XMLBuff      XML 
  , @Counter      Tinyint 
  , @Iterator     Tinyint = 1 
  , @IteratorChar NVarChar(4) 

  SET @XMLBuff = (SELECT CAST('<a>' + REPLACE(REPLACE (@Buff, ';', '</a><a>'),@Terminator,'') + '</a>' AS XML)) 
  SET @Counter = (SELECT COUNT(SS.a) FROM (SELECT [a] = Q.W.value('.', 'Varchar(1000)') FROM @XMLBuff.nodes('a')Q(W))SS) 
  
  /*удаляем пустые строки */
  SET @CSV = REPLACE(@CSV, '<row>' + REPLICATE ('<col></col>', @Counter)+ '</row>' , '')  

  /* скриним плохие знаки чтоб конвертнуть строку в XML*/ 
  SET @CSV = REPLACE (@CSV, '&', '&amp;')
  SET @CSV = REPLACE (@CSV, '''', '&apos;')
  SET @CSV = REPLACE (@CSV, '''''', '&qout;')

  SET @MyData =  CAST(@CSV AS XML)  

 
  WHILE @Iterator <= @Counter 
  BEGIN 
    SET @IteratorChar = CAST(@Iterator AS NVarchar (4))
    SET @MyQuery      = @MyQuery  + ' [Col' + @IteratorChar + '] =  X.R.value(''col[' + @IteratorChar + ']'',''NVarchar (1000)'') ' + ','  
    SET @Iterator     = @Iterator + 1
  END
  
  SET @MyQuery = LEFT(@MyQuery, LEN(@MyQuery) - 1)
  SET @MyQuery = @MyQuery  
   +  CASE WHEN @GetRowOrder = 1 THEN ', [XML_ROW_NUMBER] =  X.R.value(''count(for $s in /data/row where $s << . return("1"))'', ''int'')  '  ELSE '' END 
   + ' FROM @MyData.nodes(''/data/row'') X(R)'   

  EXEC SP_EXECUTESQL @MyQuery, N'@MyData XML', @MyData 
   

END 

 


  


Вот такая, на глазок, работает. Мб нужно довести чутка.
26 май 17, 16:14    [20515498]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Я к тому, что мой вариант без пользовательских типов и цмдшелл
26 май 17, 16:17    [20515504]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
Владислав Колосов,

На продакшене SSIS-пакет, котрый сейчас и грузит этот файл, вся логика внутри. Надо кое-что поправить оказалось через несколько лет. Пакет этот старой версии (VS2005, PackageFormatVersion = 3), даже структура файла другая. Такую старую студию (и принципиально лицензионную) уже не найти, есть 2015 и 2017 (PackageFormatVersion = 8), но они могут только на пару версий назад даунгрейдить - в рамках нового типа формата. Изменения не такие точечные чтобы в блокноте делать, хотя это и возможно теоретически. Короче, новый пакет не принимает сервер, а старый пакет нечем поправить. Вот Word почему-то можно сохранить хоть в формат Word97, а SSIS только на пару версий назад.
26 май 17, 16:48    [20515605]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
Cammomile,

Спасибо. Ещё бы вариант без динамического SQL (ибо дальше нас может ждать "An INSERT EXEC statement cannot be nested") и, в идеале, не процедура, а TVF :)
26 май 17, 16:54    [20515635]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Запилить сборку на дотнете, но это уже в другой форум.

Покуда мы не знаем структуры цсв, нам без динамики никак.
26 май 17, 17:16    [20515699]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
aleks2
Guest
Орлы, изобретение непромокаемого пороха - благородное занятие. НО!

Я еще никогда не видал нужды в таблице "неизвестного содержимого".
А для всего остального: BULK INSERT - лучший выбор.
26 май 17, 17:23    [20515729]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Ой, вообще запросто.

Вот у была у меня контора, в которой отчетики было принято грузить через интерфейс непосредственнов блоб БД.

Пр том, что таблица такая вполне себе универсальная

FileId / FileBody

А вот файлбоди было от мп3 и картинок с котятками до CSV шек от коллег.

При том, в одном бизнес процессе одна CSV, а в другом другая. Понятно, что в рамках процесса формат CSV известен( файлы типизированы), ОДНАКО сама универсальная процедура разбора CSV в датасет об этом ничего не знает.
26 май 17, 17:35    [20515771]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
автор
FileId / FileBody

Ошибочка вышла выше. Таблица с файлами примерно така

FileID bigint, FileTypeID bigint, FileBody blob
26 май 17, 17:36    [20515777]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7407
HuRN,

у меня есть только пакет PackageFormatVersion 3, и он импортируется в VS2015 проект без проблем. Версии 2, к сожалению, нет для проверки. Но VS2010, полагаю, обновила бы до версии 3. Затем пакет можно будет обновить дальше.
26 май 17, 18:11    [20515903]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Владислав Колосов,

Надо написать скрипт на обновление пакетов...
26 май 17, 18:15    [20515915]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
Владислав Колосов,

Наоборот же всё: продакшн старый (2008), тестинг новый (2016). И студии новые (2015, 2017), а старую студию (2005) не добыть. А пакет нужно поправить на продакшн. Т.е. разработать с PackageFormatVersion 8, а деплойнуть в PackageFormatVersion 3. Даунгрейд. Я ж привёл пример с Вордом: вы до сих пор можете сохранить документ Word 2013 в Word 97 (и vice versa). Это при том что Word97 - аццкий бинарный контейнер, а Word 2013 - XML. Совсем разные вещи. А тут нужно новый XML с внятной структурой сохранить в старый с невнятной, но всё же тоже XML. А нельзя. Можно только на пару версии назад (при этом в рамках PackageFormatVersion 8) - там структура не меняется, только версии, неймспейсы и GUIDы - такой даунгрейд я и сам могу в Блокноте сделать. А вот чтобы посерьёзному даунгрейднуть со сменой структуры - хрен. Потому что, полагаю, команды у Мелкософта там совсем разные, а XSLT-разработчик у них один и работает в команде Office :) В инете есть примеры как "всего-то" за двадцать-тридцать реплейсов даунгрейднуть пакет с одной версии на другую, но это очень специфичные гайды для конкретных версий - чуть только билд студии меняется - все GUIDы другие и такой гайд отправляет пакет в помойку.
29 май 17, 15:48    [20520493]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
HuRN
Наоборот же всё: продакшн старый (2008), тестинг новый (2016). И студии новые (2015, 2017), а старую студию (2005) не добыть. А пакет нужно поправить на продакшн. Т.е. разработать с PackageFormatVersion 8, а деплойнуть в PackageFormatVersion 3. Даунгрейд.
Как же так можно работать? Зачем тестинг, если ничего нельзя разрабатывать и тестировать? Зачем разрабатывать, если нельзя задеплоить?

Нужно либо поднимать версии продакшена, либо ставить на тест окружение, соответствующее по версии продакшену.
29 май 17, 15:57    [20520544]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
HuRN
Member

Откуда: Санкт-Петербург
Сообщений: 15
alexeyvg,

Прям филосовский вопрос. Не хотел даже ввязываться в старую песню про тех кто определяет и тех кто делает. Но всё же один момент обозначу. Вот захотели Вы проапгрейдить SQL Server с 2008 на 2016. Где вы его сначала обновите? Сразу на продакшн? Или может у себя дома? Так и получилось, что на тестинге более свежая версия, чем на бою. Выглядит вполне разумно. Не вполне разумно выглядит, что M$ бросает поддержку своих продуктов и одновременно:
1. В старый формат новые версии MSVS не сохраняют (в отличие от Word, например как я упоминал)
2. Старых версий MSVS официально нет. Можно где-то найти, но лицензировать их нельзя. А если можно после долгой переписки, то снова за новые деньги - старая подписка уже истекла, а новая не покрывает старых версий. Там тупо система лицензирования уже три раза сменилась. И старые версии при этом не стали нихрена бесплатными.

Знаете как совещаются бейсболисты? Встанут в круг, наклонятся друг к другу и шепчутся. А вот представьте, что это не бейсболисты, а сумоисты в своих "костюмах". Представили? Вот так выглядит подход к клиентам у M$ - с какой стороны не подойди - всюду жопа. И даже сверху это кольцо похоже на жопу.
29 май 17, 17:50    [20521018]     Ответить | Цитировать Сообщить модератору
 Re: CSV в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
HuRN
Вот захотели Вы проапгрейдить SQL Server с 2008 на 2016. Где вы его сначала обновите? Сразу на продакшн? Или может у себя дома? Так и получилось, что на тестинге более свежая версия, чем на бою. Выглядит вполне разумно.
Если такое минут на 10, то так и делают.

А если надолго, то нужно в течении всего процесса перехода на новую версию держать на тесте и старую (для тестирования текущей версии продакшена), и новую (для тестирования новой версии системы, которую потом нужно будет деплоить, и для тестирование самого процесса перехода) версию.

Ну, или если требования не такие жёсткие, можно и на продакшене разрабатывать - теста же нет, от тестирования же вы отказались.

И не только с SSIS проблемы, различия между версиями есть везде.
HuRN
Не вполне разумно выглядит, что M$ бросает поддержку своих продуктов и одновременно:
1. В старый формат новые версии MSVS не сохраняют (в отличие от Word, например как я упоминал)
Тут я согласен, нехорошо.получается.
HuRN
2. Старых версий MSVS официально нет. Можно где-то найти, но лицензировать их нельзя. А если можно после долгой переписки, то снова за новые деньги - старая подписка уже истекла, а новая не покрывает старых версий. Там тупо система лицензирования уже три раза сменилась. И старые версии при этом не стали нихрена бесплатными.
Эта проблема всё таки не так остра. Лицензии на старые версии у вас есть со старых времён, они же бессрочные. Если вы работаете с продакшеном и тестом 2008, используя какие то инструменты, так и продолжайте с ними работать. Это не объясняет, зачем нужно отформатировать тест с 2008, установить 2016, и остаться без рабочего тестового окружения.

Ещё хочу сказать, что есть возможность даунгрейда версии, т.е. если у вас есть SQL Server 2016, то он автоматически включает лицензию на SQL Server 2008. Впрочем, для разработки вам это не надо - оно теперь бесплатное.
Насчёт студии - не скажу, однако тут важно, как вы купили студию - если это подписка, типа "Visual Studio Ultimate with MSDN", то там она включает и старые версии студий, и другие МС продукты.
29 май 17, 18:14    [20521087]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить