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

Откуда:
Сообщений: 3
Добрый день, уважаемые коллеги! Пожалуйста, помогите в решении следующей задачи.
Дан файл с данными в формате CSV. Необходимо импортировать эти данные в таблицу SQL Server.
Вот созданная таблица на языке SQL, оператор BULK INSERT, а ниже файл с данными.
CREATE TABLE TABLE1
(
[ID_Sales Organization] SMALLINT NOT NULL,
ID_Country VARCHAR(10) NOT NULL,
ID_Plant SMALLINT NOT NULL,
[ID_Sold-to Party] INT NOT NULL,
[ID_Ship-to Party] INT NOT NULL,
ID_PP VARCHAR(50) NULL,
[Calendar Day] DATE NOT NULL,
[Billing Document] INT NULL,
[Reference Document] INT NULL,
[Intercompany Ind] TINYINT NOT NULL,
[Inv (CS)] SMALLINT NULL,
[Inv (SU)] FLOAT NULL,
[Inv (ISV Mkt)] FLOAT NULL,
[Inv (M3)] FLOAT NULL,
[Inv (USD)] FLOAT NULL,
[Inv (PC)] SMALLINT NULL
);

BULK INSERT DB1.dbo.TABLE1 FROM 'C:\Users\Кирилл\Downloads\SalesDetail.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR='\r\n'
)
GO

При запуске этого кода ругается и выдаёт кучу ошибок:
Msg 4864, Level 16, State 1, Line 36
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID_Sales Organization).
Msg 4864, Level 16, State 1, Line 36
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 7 (Calendar Day).
Msg 4864, Level 16, State 1, Line 36... И ВСЕ В ТАКОМ ЖЕ ДУХЕ.

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

К сообщению приложен файл (SalesDetail.csv - 22Kb) cкачать
3 апр 16, 17:15    [19011246]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Glory
Member

Откуда:
Сообщений: 104760
У вас в первой строке файла стоят имена полей.
Их очень трудно преобразовать к типам данных ваших полей
Используйте
FIRSTROW =first_row
Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.
3 апр 16, 20:09    [19011712]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Glory
Member

Откуда:
Сообщений: 104760
И еще у вас разделитель строк не \r\n, а \r\r\n.
Что очень странно, для стандартного CSV

Сообщение было отредактировано: 3 апр 16, 20:13
3 апр 16, 20:13    [19011718]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Nansuti
Member

Откуда:
Сообщений: 3
Glory, спасибо большое! Разобрался! :)
5 апр 16, 02:36    [19016883]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Некропостинг, ибо жуть тупняк!

Файл из двух строчек, не могу прочитать, твоюмать твоюмать твоюмать... Таблица пустая хоть ты тресни! Люди добрые, спасайте идиота
create table ##tt
	(
		SalesOrganization nvarchar(50),
		DistributionChannel nvarchar(255),
		SapNr nvarchar(255),
		lst varchar(10)
	)

declare @s as nvarchar(max)
set @s='BULK INSERT ##tt
	 FROM ''\\prgs043a\data\SapInterface\copy.CSV''
	 WITH
	( firstrow = 2,
	 FIELDTERMINATOR = '';'',
	 ROWTERMINATOR = ''\r''
	)' 
				 
exec sp_executesql @s

exec master.dbo.xp_cmdshell 'BCP ##tt in \\prgs043a\data\SapInterface\copy.CSV  -c -t ; -r -T'

select * from ##tt


(0 row(s) affected)


К сообщению приложен файл (Copy.CSV - 38bytes) cкачать
5 дек 17, 19:16    [21008574]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Это тоже не работает

К сообщению приложен файл (Copy.CSV - 34bytes) cкачать
5 дек 17, 19:19    [21008578]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Шыфл
Файл из двух строчек, не могу прочитать, твоюмать твоюмать твоюмать..
А зачем вы задали опцию "пропустить 2 строчки"? Вот оно и не читает.
5 дек 17, 20:10    [21008658]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

Это чтобы шапка не считывалась. Если не задавать начало со второй строчки,вторая строка считывается как первая и запихивает вторую в последнее поле через разделитель. BCP тоже не работает. Почему - не понимаю... Хелп!!!
6 дек 17, 12:19    [21010397]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Там в шапке 3 названия столбцов, а по факту 4 - последния столбец всегда 0 после него перевод строки, кроме шапки, где нет названия для последнего нулевого столбца шапки.
Если добавить строчек с читать без шапки, то считывает нормально, но на 1 строку меньше! Если с шапкой, то запихивает первую строку в шапку

SOr	DC	SapNr	lst
VkOrg VWeg Debitor 4204 03;2153;0
4204 03 2153 0
4204 03 2153 0
4204 03 2153 0
6 дек 17, 12:33    [21010442]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А ты можешь физически свой файл бинарником в поле пихнуть?

Могу поделиться процкой которая из CSV бинарников селекты делает...
6 дек 17, 12:39    [21010464]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

Я могу сделать всё, что угодно, чтобы исправить ситуацию. После небольшого исследования выяснил, что первая строка пропадает во всех импортах! Прочто на 1000 строк это не так заметно, как на 1.

Если есть идеи, с радостью подхвачу.
6 дек 17, 12:43    [21010479]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Так, версию которая берет из бинарника я куда то засунул, и под рукой нет. Но есть вот такой заход, думаю должно сработать.

CSV в таблицу
6 дек 17, 12:50    [21010511]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Шыфл
Это тоже не работает

У меня норм сработал. Твою CSV открывал в Notepad++ и копипастил в Студию.
DECLARE @CSV varchar(max) ='VkOrg;VWeg;Debitor

4204;03;2153;0
'
EXEC [dbo].[GetDataFromCSV] 
  @CSV  = @CSV 
, @Separator        = ';'
, @SkipN   = 0 
 
6 дек 17, 13:01    [21010566]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
+
  IF (OBJECT_ID(N'[dbo].[GetDataFromCSV]', N'P') IS NULL) 
  BEGIN
	  EXEC (N'CREATE PROCEDURE [dbo].[GetDataFromCSV] AS RETURN 0');
  END 

GO
/*
Павлов А.М. 

Процедура получает на вход CSV файл в виде строки, и делает из него SELECT * 

*/ 
 
ALTER PROCEDURE [dbo].[GetDataFromCSV] 
  @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 , который содежит номера нод по порядку следования */ 
, @ColumnsCount int = 0
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) 
   
  IF @ColumnsCount > 0
    BEGIN 
      SET @Counter = @ColumnsCount 
    END 
  /*удаляем пустые строки */
  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 <= 20-- @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)'   


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

END 


Еще коммент. Мне кажется, я там слегка нарукожопил с способом определения количества столбцов. ПОЭТОМУ под спойлером быстрый патч в котором количество столбцов можно задать руками.

Твой изначальный "SalesDetail.CSV" разбирается влёт вот таким вызовом

DECLARE @CSV varchar(max) ='
ID_Sales Organization,ID_Country,ID_Plant,ID_Sold-to party,ID_Ship-to Party,ID_PP,Calendar Day,Billing document,Reference document,Intercompany Ind,Inv XCSX,Inv XSUX,Inv XISV MktX,Inv XM3X,Inv XUSDX,Inv XPCX

1619,RKR,4738,28595127,28595242,000000000001351620,14.05.2000,47874427,89644859,2,64,12.288,770.68,0.128,1009.28,1024

1619,RKR,4738,28595127,28595242,000000000001351620,24.05.2000,47876284,89668324,2,49,9.408,590.05,0.098,772.73,784

'
EXEC [dbo].[GetDataFromCSV] 
  @CSV  = @CSV 
, @Separator   = ','
, @SkipN   = 0
, @ColumnsCount = 16 
--, @Terminator = 'LF'
 
6 дек 17, 13:13    [21010622]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Шыфл
Если есть идеи, с радостью подхвачу.
У вас в файле 3 столбца, а в таблице 4 колонки.
Удалите колонку из таблицы, и всё будет импортиться правильно.
6 дек 17, 13:27    [21010703]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
alexeyvg
Шыфл
Если есть идеи, с радостью подхвачу.
У вас в файле 3 столбца, а в таблице 4 колонки.
Удалите колонку из таблицы, и всё будет импортиться правильно.


Неа. Он тогда value;0 пихает в последнюю колонку. Если последняя колонка дата, к примеру, то
Cannot bulk load because the maximum number of errors (10) was exceeded.

2 Cammomile

Спасибо огромное, спасаешь. А то пукан чуть не загорелся...
6 дек 17, 14:09    [21010947]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
На здоровье!

ps

Блин, под спойлером тож накосячил, поторопился.

 WHILE @Iterator <= 20-- @Counter 
Впрочем в твоем случае и так сработает :D
6 дек 17, 14:19    [21011009]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
О, нашел где накосорезил с определением количества столбцов

  SET @XMLBuff = (SELECT CAST('<a>' + REPLACE(REPLACE (@Buff, ';', '</a><a>'),@Terminator,'') + '</a>' AS XML)) 

меняем на

  SET @XMLBuff = (SELECT CAST('<a>' + REPLACE(REPLACE (@Buff, @Separator, '</a><a>'),@Terminator,'') + '</a>' AS XML)) 
6 дек 17, 14:24    [21011034]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

			/*
			--/**********************************************/
			--nefunguje spravne, vznechava 1. radek!!!!
			--/*********************************************/
			set @s='BULK INSERT #tt
				 FROM ''\\prgs043a\data\SapInterface\' + @fname + '''
				 WITH
				 ( firstrow = 2,
				 FIELDTERMINATOR = '';'',
				 ROWTERMINATOR = ''\n''
				 )' 

			exec sp_executesql @s	 
			*/
			
			set @s='select @csv=bulkcolumn from openrowset(bulk ''\\prgs043a\data\SapInterface\'+ @fname +''' , single_clob) a'
			exec sp_executesql @s,N'@csv nvarchar(max) out',@csv=@csv output
			insert #tt
			EXEC [dbo].[sp_GetDataFromCSV]  @CSV , ';','CRLF',1 
6 дек 17, 14:38    [21011123]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

Скормил твоей процке CSV на 100 метро. Если не завалится, пойдёт в продакшн :)
6 дек 17, 14:47    [21011183]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
382314 строк, 42 поля - 27 минут 42 секунды. Не bulk insert, конечно скорости, но удовлетворительно. А главное - правельное колличество строк!
6 дек 17, 15:29    [21011391]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Слушай, а вот не в службу, а в дружбу. Давно хотел проверить влияет ли длина тегов на парсинг на больших объемах.
Не мог бы ты в этой процке всяки <col> и <row> поменять на <с> и <r>, соответственно, и прогнать по новой загрузку?

Я так подозреваю, что на таких размерах как 40 столбцов на 300 строк, такая замена СИЛЬНО урежет размер XML...
6 дек 17, 15:43    [21011481]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

не, пока в продакшн не пойдёт...
Msg 9455, Level 16, State 1, Procedure sp_GetDataFromCSV, Line 88
XML parsing: line 1, character 111, illegal qualified name character


К сообщению приложен файл (FIC.M171206.001959AQIWZSSA_SD_AD==AUT_ODBLOK_OBJ.CSV - 36bytes) cкачать
6 дек 17, 18:22    [21012361]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Ну незнаю, у меня пашет ок. Скинь мыло, я тебе отправлю последнюю версию, чтоб форум не захламлять. (жаль тут лички нет)

К сообщению приложен файл. Размер - 14Kb
6 дек 17, 18:42    [21012468]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из файла CSV в таблицу SQL Server  [new]
Шыфл
Member

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

ispalin[sobaka]seznam.cz
6 дек 17, 19:08    [21012542]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить