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

Откуда:
Сообщений: 10
Всем привет!
Пробую свои шаги в освоении MS SQL.
И встала задача такого плана: имеем несколько файлов CSV и их надо импортировать в таблицу (таблицу временную)
Притом как импортировать по одному файлу я разобрался, а как разом из каталога все файлы с разрешением CSV обработать у меня не получается. Притом что надо использовать курсор. Я в интернете нашел скрипт и пытался его под себя адаптировать но у меня ничего не вышло. Может поможете новичку? ))
Мой запрос такой :

IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
              [Times] VARCHAR(100),
	        [Caller_Name] VARCHAR (200),
			[Caller_Number] VARCHAR (200),
			[Callee_Name] VARCHAR (200),
			[Callee_Numbers] VARCHAR(100),
			[DOD] VARCHAR(100),
			[DID] VARCHAR(100),
			[Call_Duration_(s)] VARCHAR (200),
			[Talk_Duration_(s)] VARCHAR (200),
			[Status] VARCHAR(100),
			[Source_Trunk] VARCHAR(50),
			[Destination Trunk] VARCHAR(100),
			[Communication_Type] VARCHAR(100),
			[PIN_Code] VARCHAR(10),
			[Caller IP Address] VARCHAR(200),
			[Cost] VARCHAR(100),
			[Billing_Account] VARCHAR(100)
			
)
-- Импортировать во временную таблицу информацию из файла csv

-- BULK INSERT #csv_temp
-- FROM 'C:\serg\all.csv'
-- WITH (firstrow = 2,fieldterminator = ';', rowterminator = '0x0a',CODEPAGE = '1251');
-- переменные 
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
		@filename varchar(255)
--получить список файлов для обработки:
SET @path = 'C:\serg\'

--цикл курсора
declare c1 CURSOR LOCAL  READ_ONLY  FAST_FORWARD  FOR
 SELECT TIMES,Caller_Name FROM @filename where  [Times]  like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT #csv_temp FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
               FIELDTERMINATOR = '','', 
               ROWTERMINATOR = ''\n'', 
               FIRSTROW = 2 
            ) '
print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1



select * from #csv_temp


Сообщение было отредактировано: 16 фев 21, 13:18
16 фев 21, 13:06    [22281559]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
komrad
Member

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

для получения списка файлов и загрузки можно использовать такую обертку (выбираются все файлы .log из папки c:\temp).

+
set nocount on;

declare @files table (subdir varchar(255),dep smallint,isfile smallint)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\temp'

insert into @files (subdir,dep,isfile)
exec xp_dirtree @path,1,1

delete @files where subdir not like '%.log'
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=subdir from @files 

	/*import file*/
	print 'Import file: '+ @path+'\'+@filename  

	delete @files where subdir=@filename 
	set @filename=''
end 


Сообщение было отредактировано: 16 фев 21, 13:18
16 фев 21, 13:20    [22281561]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

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

Предварительно я создаю временную таблицу (т.к. у меня ее нет)
Командами :
IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
[Times] VARCHAR(100),
[Caller_Name] VARCHAR (200),
[Caller_Number] VARCHAR (200),
[Callee_Name] VARCHAR (200),
[Callee_Numbers] VARCHAR(100),
[DOD] VARCHAR(100),
[DID] VARCHAR(100),
[Call_Duration_(s)] VARCHAR (200),
[Talk_Duration_(s)] VARCHAR (200),
[Status] VARCHAR(100),
[Source_Trunk] VARCHAR(50),
[Destination Trunk] VARCHAR(100),
[Communication_Type] VARCHAR(100),
[PIN_Code] VARCHAR(10),
[Caller IP Address] VARCHAR(200),
[Cost] VARCHAR(100),
[Billing_Account] VARCHAR(100)
И далее использую эту обертку которую вы предложили?
16 фев 21, 13:32    [22281567]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
komrad
Member

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

да, сначала создаёте таблицу
в обертку вставляете блок с bulk insert (set @sql ... exec (@sql)) после/вместо print-а

полагаю, что:
- файлы csv однотипные,
- права на запуск sp_dirtree у вас есть,
- у учетки сиквела есть доступ в папку с файлами
16 фев 21, 13:42    [22281577]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
komrad
Snegovik1987,

да, сначала создаёте таблицу
в обертку вставляете блок с bulk insert (set @sql ... exec (@sql)) после/вместо print-а

полагаю, что:
- файлы csv однотипные,
- права на запуск sp_dirtree у вас есть,
- у учетки сиквела есть доступ в папку с файлами

Да файлы однотипные в остальном вроде все так.
А скажите обязательно использовать курсор?В вашем примере его нет а в целом?

Посмотрите пожалуйста - вот так :


IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
[Times] VARCHAR(100),
[Caller_Name] VARCHAR (200),
[Caller_Number] VARCHAR (200),
[Callee_Name] VARCHAR (200),
[Callee_Numbers] VARCHAR(100),
[DOD] VARCHAR(100),
[DID] VARCHAR(100),
[Call_Duration_(s)] VARCHAR (200),
[Talk_Duration_(s)] VARCHAR (200),
[Status] VARCHAR(100),
[Source_Trunk] VARCHAR(50),
[Destination Trunk] VARCHAR(100),
[Communication_Type] VARCHAR(100),
[PIN_Code] VARCHAR(10),
[Caller IP Address] VARCHAR(200),
[Cost] VARCHAR(100),
[Billing_Account] VARCHAR(100)

)

set nocount off;

declare @files table (Times varchar(255), Caller_Name VARCHAR (200),Caller_Number VARCHAR (200))
declare @filename varchar(255)
declare @path varchar(255) = 'c:\serg'

insert into @files (TImes,Caller_Name,Caller_Number)
exec xp_dirtree @path,1,1

delete @files where Times not like '%.csv'
--select * from @files

while exists (select top 1 1 from @files)
begin
select top 1 @filename=Times from @files

/*import file*/
--print 'Import file: '+ @path+'\'+@filename
bulk insert (set @sql ... exec (@sql))

delete @files where Times=@filename
set @filename=''
end
16 фев 21, 13:54    [22281583]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
komrad
Member

Откуда:
Сообщений: 5498
Snegovik1987

А скажите обязательно использовать курсор?В вашем примере его нет а в целом?

"А скрипач не нужен, родной. Он только лишнее топливо жрёт." (с) классика
в данном случае, вместо курсора используется цикл

+ как-то так
set nocount off;

declare @files table ([filename] varchar(255), sub smallint, isfile smallint)
declare @sql varchar(2048)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\serg'


insert into @files ([filename],sub,isfile)
exec xp_dirtree @path,1,1

delete @files where [filename] not like '%.csv' 
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=filename from @files 

	/*import file*/
	--print 'Import file: '+ @path+'\'+@filename  

	set @sql = 'BULK INSERT #csv_temp FROM ''' + @path +'\'+ @filename + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 ) '
	print @sql
	exec (@sql)

	delete @files where [filename]=@filename 
	set @filename=''
end
16 фев 21, 14:23    [22281619]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
komrad
Snegovik1987

А скажите обязательно использовать курсор?В вашем примере его нет а в целом?

"А скрипач не нужен, родной. Он только лишнее топливо жрёт." (с) классика
в данном случае, вместо курсора используется цикл

+ как-то так
set nocount off;

declare @files table ([filename] varchar(255), sub smallint, isfile smallint)
declare @sql varchar(2048)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\serg'


insert into @files ([filename],sub,isfile)
exec xp_dirtree @path,1,1

delete @files where [filename] not like '%.csv' 
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=filename from @files 

	/*import file*/
	--print 'Import file: '+ @path+'\'+@filename  

	set @sql = 'BULK INSERT #csv_temp FROM ''' + @path +'\'+ @filename + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 ) '
	print @sql
	exec (@sql)

	delete @files where [filename]=@filename 
	set @filename=''
end


Благодарю Вас!
Но в процессе запуска у меня ошибка выстреливает
 

IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
-- Создаю временную таблицу #csv_temp
CREATE TABLE #csv_temp (
            [Times] VARCHAR(250),
	        [Caller_Name] VARCHAR (250),
			[Caller_Number] VARCHAR (250),
			[Callee_Name] VARCHAR (250),
			[Callee_Numbers] VARCHAR(250),
			[DOD] VARCHAR(250),
			[DID] VARCHAR(250),
			[Call_Duration_(s)] VARCHAR (200),
			[Talk_Duration_(s)] VARCHAR (200),
			[Status] VARCHAR(200),
			[Source_Trunk] VARCHAR(50),
			[Destination Trunk] VARCHAR(100),
			[Communication_Type] VARCHAR(100),
			[PIN_Code] VARCHAR(10),
			[Caller IP Address] VARCHAR(200),
			[Cost] VARCHAR(100),
			[Billing_Account] VARCHAR(100)
			
)

set nocount off;

declare @files table ([filename] varchar(255), sub smallint, isfile smallint)
declare @sql varchar(2048)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\serg'


insert into @files ([filename],sub,isfile)
exec xp_dirtree @path,1,1

delete @files where [filename] not like '%.csv' 
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=filename from @files 

	/*import file*/
	--print 'Import file: '+ @path+'\'+@filename  

	set @sql = 'BULK INSERT #csv_temp FROM ''' + @path +'\'+ @filename + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 ) '
	print @sql
	exec (@sql)

	delete @files where [filename]=@filename 
	set @filename=''
end


Сообщение 4866, уровень 16, состояние 8, строка 1
Массовая загрузка не удалась. Слишком длинный столбец в файле данных в строке 1, столбце 1. Убедитесь, что признак конца поля и конца строки были указаны правильно.
Сообщение 7301, уровень 16, состояние 2, строка 1
Не удалось получить требуемый интерфейс ("IID_IColumnsInfo") от поставщика OLE DB "BULK" для связанного сервера "(null)".
16 фев 21, 14:50    [22281644]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
Snegovik1987,
Файлы такие
во вложении

К сообщению приложен файл (cdr-30.10.0.17 (3).csv - 105Kb) cкачать
16 фев 21, 14:55    [22281646]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
Snegovik1987,
Поставил
ROWTERMINATOR = ''0x0a'' и все получилось.
Я благодарен Вам!
16 фев 21, 15:03    [22281651]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Владислав Колосов
Member

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

это, в общем-то, дедовский способ, для решения ETL задач можно использовать Integration Services. Намного удобнее, к тому же, самодокументируемо и Вы сможете выполнять несколько задач загрузки одновременно.
16 фев 21, 16:02    [22281691]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
aleks222
Member

Откуда:
Сообщений: 1237
Владислав Колосов
Snegovik1987,

это, в общем-то, дедовский способ, для решения ETL задач можно использовать Integration Services. Намного удобнее, к тому же, самодокументируемо и Вы сможете выполнять несколько задач загрузки одновременно.


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

Сообщение было отредактировано: 16 фев 21, 19:38
16 фев 21, 19:45    [22281880]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
.Евгений
Member

Откуда:
Сообщений: 653
Если у человека нет потребностей в трансформации данных, нет вопросов по безопасности (доступ SQL Сервера к файловой системе), не требуется более-менее осмысленной обработки ошибок... другими словами, если человеку надо сделать простую копипасту - проще использовать обычный SQL, чем ставить SSIS, VS, настраивать и деплоить. Если человек "пробует свои шаги", то зачем отягощать его проблемами уровня миддл и выше?
aleks222
Ну и запустить десяток скриптов, если очень хочется, тоже нема проблем.
В принципе оркестрацию можно делать и на SQL, но в реальной жизни я бы отнесся к такому предложению с негативным предубеждением.
16 фев 21, 23:57    [22281992]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
.Евгений
Если у человека нет потребностей в трансформации данных, нет вопросов по безопасности (доступ SQL Сервера к файловой системе), не требуется более-менее осмысленной обработки ошибок... другими словами, если человеку надо сделать простую копипасту - проще использовать обычный SQL, чем ставить SSIS, VS, настраивать и деплоить. Если человек "пробует свои шаги", то зачем отягощать его проблемами уровня миддл и выше?
aleks222
Ну и запустить десяток скриптов, если очень хочется, тоже нема проблем.
В принципе оркестрацию можно делать и на SQL, но в реальной жизни я бы отнесся к такому предложению с негативным предубеждением.


Да, все верно. Пробую свои силы.
17 фев 21, 09:15    [22282048]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
komrad
Snegovik1987,

для получения списка файлов и загрузки можно использовать такую обертку (выбираются все файлы .log из папки c:\temp).

+
set nocount on;

declare @files table (subdir varchar(255),dep smallint,isfile smallint)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\temp'

insert into @files (subdir,dep,isfile)
exec xp_dirtree @path,1,1

delete @files where subdir not like '%.log'
--select * from @files 

while exists (select top 1 1 from @files)
begin
	select top 1 @filename=subdir from @files 

	/*import file*/
	print 'Import file: '+ @path+'\'+@filename  

	delete @files where subdir=@filename 
	set @filename=''
end 


Вы меня простите за занудство. А можете рассказать как работает этот цикл?
24 фев 21, 10:34    [22285498]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
komrad
Member

Откуда:
Сообщений: 5498
Snegovik1987


Вы меня простите за занудство. А можете рассказать как работает этот цикл?


буквально так, как там написано


+ теперь с комментами
set nocount on;
/*декларация переменных и рабочей таблицы*/
declare @files table (subdir varchar(255),dep smallint,isfile smallint)
declare @filename varchar(255) 
declare @path varchar(255) = 'c:\temp'

/*занесение списка файлов в таблицу*/
insert into @files (subdir,dep,isfile)
exec xp_dirtree @path,1,1

/*удаление лишнего*/
delete @files where subdir not like '%.log'
--select * from @files 

/*пока что-то (файлы) есть  в таблице*/
while exists (select top 1 1 from @files)
begin
	/*берется первый попавшийся файл из таблицы*/
	select top 1 @filename=subdir from @files 

	/*работа с файлом*/
	print 'Import file: '+ @path+'\'+@filename  

	/*удаление файла из таблицы*/
	delete @files where subdir=@filename 

	/*инициализация переменной*/
	set @filename=''
end 

24 фев 21, 11:57    [22285544]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
komrad,
То есть логика такая :
Объявляем переменные и таблицы с которой будет работать.
Далее делаем занесение списка файлов в временную таблицу.
Потом delete @files where subdir not like '%.log' - удаляем лишнее а лишнее это лишние файлы или что именно?

Далее /*пока что-то (файлы) есть в таблице*/
while exists (select top 1 1 from @files) пока файл в таблице (я так понимаю пока один файл )
Добавляем его в таблицу и импортируем.
И цикл работает по кругу пока все файлы с определенным расширением не заберет в таблицу?

Скажите еще,у вас есть почта или другие каналы связи? У меня есть предложение.
24 фев 21, 12:17    [22285566]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
komrad
Member

Откуда:
Сообщений: 5498
Snegovik1987
komrad,
То есть логика такая :
1) Объявляем переменные и таблицы с которой будет работать.

2) Далее делаем занесение списка файлов в временную таблицу.

3) Потом delete @files where subdir not like '%.log' - удаляем лишнее а лишнее это лишние файлы или что именно?

в этом примере обрабатывались файлы с расширением log
поскольку в папке могут быть другие файлы, которые обрабатывать не надо, то удаляем все файлы с расширением отличным от log

Snegovik1987

4) Далее /*пока что-то (файлы) есть в таблице*/
while exists (select top 1 1 from @files) пока файл в таблице (я так понимаю пока один файл )

это проверка на наличие хотя бы одного (top 1) файла в таблице
если файлов в таблице нет, то цикл заканчивается

Snegovik1987

5) Добавляем его в таблицу и импортируем.
И цикл работает по кругу пока все файлы с определенным расширением не заберет в таблицу?

все файлы и так уже в таблице - см. п.2-3
в цикле берется один любой (нет сортировки к top 1) файл из таблицы и обрабатывается как нужно

Snegovik1987
Скажите еще,у вас есть почта или другие каналы связи? У меня есть предложение.

почта в профиле
24 фев 21, 13:05    [22285624]     Ответить | Цитировать Сообщить модератору
 Re: Массовый импорт CSV в SQL  [new]
Snegovik1987
Member

Откуда:
Сообщений: 10
komrad
Snegovik1987
komrad,
То есть логика такая :
1) Объявляем переменные и таблицы с которой будет работать.

2) Далее делаем занесение списка файлов в временную таблицу.

3) Потом delete @files where subdir not like '%.log' - удаляем лишнее а лишнее это лишние файлы или что именно?

в этом примере обрабатывались файлы с расширением log
поскольку в папке могут быть другие файлы, которые обрабатывать не надо, то удаляем все файлы с расширением отличным от log

Snegovik1987

4) Далее /*пока что-то (файлы) есть в таблице*/
while exists (select top 1 1 from @files) пока файл в таблице (я так понимаю пока один файл )

это проверка на наличие хотя бы одного (top 1) файла в таблице
если файлов в таблице нет, то цикл заканчивается

Snegovik1987

5) Добавляем его в таблицу и импортируем.
И цикл работает по кругу пока все файлы с определенным расширением не заберет в таблицу?

все файлы и так уже в таблице - см. п.2-3
в цикле берется один любой (нет сортировки к top 1) файл из таблицы и обрабатывается как нужно

Snegovik1987
Скажите еще,у вас есть почта или другие каналы связи? У меня есть предложение.

почта в профиле

Спасибо.
Я написал на почту.
24 фев 21, 13:58    [22285680]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить