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

Откуда: Königsberg in Preußen
Сообщений: 79
Пытаюсь вставить данные из файла в таблицу:
CREATE TABLE [dbo].[Table](
	[1] [char](1) NULL,
	[2] [varchar](10) NULL,
	[3] [varchar](100) NULL,
	[4] [varchar](max) NULL,
	[5] [varchar](20) NULL,
	[6] [varchar](20) NULL,
	[7] [varchar](20) NULL,
	[8] [varchar](20) NULL,
	[9] [varchar](20) NULL,
	[10] [varchar](20) NULL,
	[11] [varchar](20) NULL,
	[12] [varchar](20) NULL,
	[13] [varchar](20) NULL,
	[14] [varchar](20) NULL,
	[15] [varchar](20) NULL,
	[16] [varchar](20) NULL,
	[17] [varchar](20) NULL,
	[18] [varchar](20) NULL,
	[19] [varchar](20) NULL,
	[20] [varchar](20) NULL
)
Только при таких параметрах смог добиться вставки значений в таблицу
fieldterminator = '§',
rowterminator = '\n'
Но перенос строки не воспринимается, данные пишутся последовательно причем в последнюю ячейку и с разделителями вместе.
Как исправить, что бы сервер вставлял что есть в строке и переходил к следующей?

К сообщению приложен файл (file.txt - 137bytes) cкачать
18 авг 11, 17:35    [11141441]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Glory
Member

Откуда:
Сообщений: 104751
Super_DJ
Но перенос строки не воспринимается,

Потому что "перенос строк" в вашем файле есть два символа \r\n
И в последней строке так их вообще нет
18 авг 11, 17:51    [11141579]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Glory,

То что должно быть \r\n (HEX 0D 0A) я понял, но с таким параметром:
rowterminator = '\r\n' вообще не работает, говорит:
Сообщение 4863, уровень 16, состояние 1, строка 1
Bulk load data conversion error (truncation) for row 1, column 20 (20).
18 авг 11, 18:03    [11141672]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ну так а где у вас в перой же строке файла 20 значений с разделителем § ?
И вообще у вас в каждой строке файла почему то разное число полей
18 авг 11, 18:07    [11141699]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Glory,

Число полей в каждой строке может быть от 2 до 20, причем не всегда в первой строке их количество максимально.
Как тогда сделать импорт из такого файла?
18 авг 11, 18:11    [11141712]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
iljy
Member

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

заливайте в таблицу с одной строкой, затем руками разбирайте по разделителям на нужное количество полей.
18 авг 11, 18:53    [11141985]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
iljy,

По первой части делаю:
CREATE TABLE #T (T VARCHAR(MAX))

BULK INSERT #T FROM 'F:\TEMP\FILE.TXT'
   WITH
     (
       ROWTERMINATOR = '\R\N',
       CODEPAGE = 1251
      )
SELECT * FROM #T

DROP TABLE #T
Получаю все в одну строку, вместо знаков переноса пробелы, я так понимаю сервер опять не "понял" ROWTERMINATOR
Как быть?
18 авг 11, 19:11    [11142088]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Baddy
Member

Откуда: Харьков
Сообщений: 174
Super_DJ,

FIELDTERMINATOR= '\R\N'
18 авг 11, 19:14    [11142108]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Упс, с тормозил, спасибо.
iljy
Super_DJ,

заливайте в таблицу с одной строкой, затем руками разбирайте по разделителям на нужное количество полей.

Как теперь её разобрать? Можно какой нибудь наглядный пример?
18 авг 11, 19:24    [11142157]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Шыфл
Member

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


declare @text varchar(100)
declare @tmp varchar(100)
declare @delim char(1)
declare @i int
set @text = 'a!b!c!dfs!sgs!!!'
set @delim='!'
set @i =0

while CHARINDEX (@delim,@text)>0
begin
set @tmp = left(@text,CHARINDEX (@delim,@text)-1)
set @text = right(@text,len(@text)-CHARINDEX (@delim,@text))
set @i=@i+1
print cast(@i as varchar) + ' ' + @tmp
end


18 авг 11, 20:13    [11142327]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Шыфл,

Благодарю, идею уловил ;-)
18 авг 11, 20:23    [11142360]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Делаю :
    SELECT 
    [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20]
    FROM (SELECT  ID, T FROM TmpTbl3) x
    PIVOT
    (AVG(ID)
    FOR T
    IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20])
    ) pvt
Получаю строку из 20-ти null, что я делаю не так?
19 авг 11, 12:36    [11145280]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Glory
Member

Откуда:
Сообщений: 104751
Super_DJ
Получаю строку из 20-ти null, что я делаю не так?

А что вы намеревались получить ?
Вы думаете, что PIVOT вам распарсит содержимое поля T ?

Сообщение было отредактировано: 19 авг 11, 12:43
19 авг 11, 12:41    [11145342]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Glory,

Честно говоря да, предполагалось нечто подобное.
19 авг 11, 13:16    [11145755]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
iljy
Member

Откуда:
Сообщений: 8711
Super_DJ
Glory,

Честно говоря да, предполагалось нечто подобное.

А ознакомление с документацией и примерами предполагалось?
19 авг 11, 13:36    [11145933]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Glory
Member

Откуда:
Сообщений: 104751
Super_DJ
Glory,

Честно говоря да, предполагалось нечто подобное.

Ваше предположение не поддтверждается простым прочтением хелпа по команде PIVOT
19 авг 11, 13:37    [11145947]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Mgvlad
Member

Откуда: Минск
Сообщений: 97
Super_DJ
Glory,

Число полей в каждой строке может быть от 2 до 20, причем не всегда в первой строке их количество максимально.
Как тогда сделать импорт из такого файла?


А Вы хотите решить задачку исключительно средствами TSQL? Просто в SSIS эта задачка решается на раз. А на два делается универсальное решение, которому только надо передать целевую табличку и путь к файлу.
19 авг 11, 14:42    [11146644]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Mgvlad,

Хотелось средствами TSQL, начал делать динамический запрос, но как то тоже не задается.
Если и вправду так просто, приведите пример как это решается с помощью SSIS?
19 авг 11, 15:39    [11147297]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Mgvlad
Member

Откуда: Минск
Сообщений: 97
Super_DJ
Mgvlad,

Хотелось средствами TSQL, начал делать динамический запрос, но как то тоже не задается.
Если и вправду так просто, приведите пример как это решается с помощью SSIS?


Ну SSIS это я по привычке ляпнул :) На самом деле на C#, просто код легко впихнуть в Скриптаск SSIS (или в консольное приложение)



                string FileIn = исходный файлиг
                string FileOut = FileIn + ".out";

                StreamReader sr = new StreamReader(FileIn); // Для чтения
                StreamWriter sw = new StreamWriter(FileOut); // Для записи

                while (sr.Peek() > 0) //начинаем построчно читать
                {
                    string CurrentLine = sr.ReadLine(); //Считываем исходную строку
                    string WrLine = "";

                    string[] LineArray = CurrentLine.Split('тут надо указать символ-разделитель'); //Создаём массив для данных и 
                                                                                        // загоняем туда строчку, команда Split разбивает строку на элементы.

                    for (int i = 1; i < LineArray.Length; i += 2) // цикл по элементам массива через один (т.к. в массиве у нас будет поле-разделитеть - поле)
                    {
                        //Тут собственно можно сделать с данными что угодно - подчистить от нежелательных символов, как-то изменить... 
                       //в примере мы формируем выходную строку с разделителем | и попутно подчищаем от пробелов до и после текста.
                        if (i == 1)
                            WrLine = LineArray[i].Trim();
                        else
                            WrLine = WrLine + "|" + LineArray[i].Trim();

                    }
                    sw.WriteLine(WrLine); //пишем чистенькую строчку в выходной файл.
                    // тут можно добить в строчку недостающие поля что бы bulk Insert не спотыкался. Например, в целевой табличке у нас 20 полей,
                   // в реальной строчке файла 10. Добиваем ещё 10 символов | и получаем в выходном файле все строчки по 20 полей.
                }
                sw.Close();
                sr.Close();
                
                string sql = "bulk insert ЦЕЛЕВАЯ_ТАБЛИЧКА from 'ПОДГОТОВЛЕННЫЙ_ФАЙЛ' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\\n', firstrow=1)"; 
                   
                 SqlConnection Conn = new SqlConnection(ConnectionString к SQL); 
                 Conn.Open(); //Открываем соединение
                 SqlCommand Cmd = new SqlCommand(sql, Conn);
                 Cmd.ExecuteNonQuery(); // выполняем вставку

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

Плюсы способа - гибкость, скорость, можно отлавливать ошибки вставки.

Минусы - надо кодить на C#. Сей минус утрачивает значимость если задачу приходится решать часто. Один раз сделал и забыл.
19 авг 11, 16:43    [11147930]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
Mgvlad
На самом деле на C#, просто код легко впихнуть в Скриптаск SSIS


Хм... Почему тогда просто готовую сборку не скормить движку и не привлекать посторонних процессов?
19 авг 11, 17:24    [11148225]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Mgvlad,

Спасибо за пример, но я в шарпе не силе, посему все же поробую собрать динамический запрос.
19 авг 11, 23:49    [11149616]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
Пытаюсь формировать строку для последующего подпихивания в INSERT VALUES
declare @sql nvarchar(max)
set @sql=N'('''+(select t from TmpTbl3 where id=1)+''','''+(select t from TmpTbl3 where id=2)+''','''+(select t from TmpTbl3 where id=3)+''','''+(select t from TmpTbl3 where id=4)+''','''+(select t from TmpTbl3 where id=5)+''','''+(select t from TmpTbl3 where id=6)+''')'
select @sql
Строка вроде правильно формируется, если не встречаются null
Как обойти эту проблему?
20 авг 11, 15:14    [11151064]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Super_DJ,
isnull(...,'NULL')
?
20 авг 11, 15:47    [11151149]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
Super_DJ
Member

Откуда: Königsberg in Preußen
Сообщений: 79
kDnZP,

Ну я так и пробовал, но что-то с ковычками видать намутил, проддемонстрируй на отдеом сегменте как правильно впихнуть isnull.
20 авг 11, 15:53    [11151169]     Ответить | Цитировать Сообщить модератору
 Re: Bulk Insert - проблема с импортом из файла  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
Super_DJ
kDnZP,

Ну я так и пробовал, но что-то с ковычками видать намутил, проддемонстрируй на отдеом сегменте как правильно впихнуть isnull.
declare @sql nvarchar(max)
set @sql=N'(' + isnull('''' + (select t from TmpTbl3 where id=3)+'''', 'NULL') + ',' + ... + ')'
select @sql
20 авг 11, 22:14    [11151785]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить