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

Файл
SUB_REGION_ID, SUB_REGION_CODE, SUB_REGION_NAME, REGION_ID, STATUS, STATUS_DATE, LAST_ACTIVITY_TYPE
"966","AG","Glacier Bay","29","True","2/16/2007 8:17:02 AM","I"

Файл формата
11.0
7
1 SQLCHAR 0 12 "," 1 SUB_REGION_ID ""
2 SQLCHAR 0 4 "," 2 SUB_REGION_CODE Cyrillic_General_CI_AS
3 SQLCHAR 0 128 "," 3 SUB_REGION_NAME Cyrillic_General_CI_AS
4 SQLCHAR 0 12 "," 4 REGION_ID ""
5 SQLCHAR 0 32 "," 5 STATUS Cyrillic_General_CI_AS
6 SQLCHAR 0 24 "," 6 STATUS_DATE ""
7 SQLCHAR 0 4 "\n" 7 LAST_ACTIVITY_TYPE Cyrillic_General_CI_AS

Инструкция SQL
    select *
      from openrowset(BULK 'C:\Gate\In\TB_SUB_REGION.csv'
                     , FORMATFILE = 'C:\Gate\In\TB_SUB_REGION.fmt'
                     , CODEPAGE = 'RAW'
                     , FIRSTROW = 2

                ) AS test_file


Результат
dummy SUB_REGION_ID SUB_REGION_CODE SUB_REGION_NAME REGION_ID STATUS STATUS_DATE LAST_ACTIVITY_TYPE
"966" "AG" "Glacier Bay" "29" "True" "2/16/2007 8:17:02 AM" "I"

1. В конце файла источник данных ставит 0x0D0A (т.е. конец файла 0x0A0D0A), хотя разделитель строк 0x0A. И это дает в конце инструкции SQL ошибку.

Сообщение 4832, уровень 16, состояние 1, строка 1
Массовая загрузка: непредвиденный конец файла данных.
Сообщение 7399, уровень 16, состояние 1, строка 1
Поставщик OLE DB "BULK" для связанного сервера "(null)" сообщил об ошибке. Поставщик не предоставил данных об ошибке.
Сообщение 7330, уровень 16, состояние 2, строка 1
Не удалось получить строку от поставщика OLE DB "BULK" для связанного сервера "(null)".

Если ручками убрать 0D0A - ошибка исчезает.

Есть какие идеи?
На крайняк, вдруг тут гуру .NET есть, загрузка файла идет так (powershell)

           $ftprequest = [System.Net.FtpWebRequest]::Create( $uri )
           $ftprequest.Credentials = $credentials
           $ftprequest.Method = [System.Net.WebRequestMethods+Ftp]::DownloadFile
           $responseStream = $ftprequest.GetResponse().GetResponseStream()

           $localfile = $target + $file
           $fileStream =  New-Object System.IO.FileStream( $localfile, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write )
           $responseStream.CopyTo($fileStream)
           $responseStream.Close()
           $fileStream.Close()


Я не силен в тонкостях .NET, но может есть дешевый способ просто отрезать этот конец (два последних байта)?

2. Как бы избавиться от кавычек ""? Советы https://blogs.msdn.microsoft.com/sqlserverfaq/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file/
приводят к "исчезновению" первой строки.
20 июл 16, 10:18    [19430020]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
Владислав Колосов
Member

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

Вы как-то сложно заходите. Используйте SSIS для импорта-экспорта. У Вас классический (! редкость) CSV и проблемы на пустом месте.
20 июл 16, 13:35    [19430948]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
Владислав Колосов
aleks2,

Вы как-то сложно заходите. Используйте SSIS для импорта-экспорта. У Вас классический (! редкость) CSV и проблемы на пустом месте.

Мне не нужен "экскаватор", чтобы выкопать ямку.
Использование SSIS - удел лохов.

Нечего сказать по делу - иди мимо.
20 июл 16, 19:55    [19432781]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Что такой грубый?
Вставьте в Powershell:
(Get-Content Filepath).replace('0x0D0A', '') | Set-Content Filepath
20 июл 16, 20:08    [19432849]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
3unknown
Что такой грубый?
Вставьте в Powershell:
(Get-Content Filepath).replace('0x0D0A', '') | Set-Content Filepath


Ужос. Надо ж думать и об "эффективности". Занафига грузить процессор и IO бессмысленным поиском?
Stream имеет метод SetLength.

$localfile = $target + $file
$fileStream =  New-Object System.IO.FileStream( $localfile, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write )
$responseStream.CopyTo($fileStream)

$fileStream.SetLength( $fileStream.Length - 2 )

$responseStream.Close()
$fileStream.Close()


вариант (но может не пройти, если $responseStream seek не поддерживает). Хотя лучше.

$localfile = $target + $file
$fileStream =  New-Object System.IO.FileStream( $localfile, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write )

$responseStream.SetLength( $responseStream.Length - 2 )

$responseStream.CopyTo($fileStream)
$responseStream.Close()
$fileStream.Close()
20 июл 16, 20:44    [19432953]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
aleks2
вариант (но может не пройти, если $responseStream seek не поддерживает)
BinaryWriter вроде поддерживает seek.
Что бы уж без копирования.
20 июл 16, 20:52    [19432984]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
alexeyvg
aleks2
вариант (но может не пройти, если $responseStream seek не поддерживает)
BinaryWriter вроде поддерживает seek.
Что бы уж без копирования.
А вообще я плохо знаю этот ваш повершелл, и C# становится всё хуже и хуже...
Поэтому я подобные задачи делал на С, там и _lseek работает, и вообще всё просто и быстро :-)
20 июл 16, 21:02    [19433014]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
alexeyvg
alexeyvg
пропущено...
BinaryWriter вроде поддерживает seek.
Что бы уж без копирования.
А вообще я плохо знаю этот ваш повершелл, и C# становится всё хуже и хуже...
Поэтому я подобные задачи делал на С, там и _lseek работает, и вообще всё просто и быстро :-)


PowerShell = C# & .Net, только закорючки немного по другому ставятся.

ЗЫ. Я на С только раз в жизни писал, да и то kernel mode driver Windows 2000 для масс-спектрометра.
Все еще работает на Windows 2003 и даже не единого BCOD за 10 лет.
20 июл 16, 22:13    [19433273]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
alexeyvg
aleks2
вариант (но может не пройти, если $responseStream seek не поддерживает)
BinaryWriter вроде поддерживает seek.
Что бы уж без копирования.
А, ведь у FileStream есть функция SetLength, и она обрезает файл без ненужного переписывания (проверил).
FileStream fs = File.Open(filename, FileMode.Open);
fs.SetLength(fs.Length-2);
fs.Close();
21 июл 16, 10:55    [19434819]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
alexeyvg
alexeyvg
пропущено...
BinaryWriter вроде поддерживает seek.
Что бы уж без копирования.
А, ведь у FileStream есть функция SetLength, и она обрезает файл без ненужного переписывания (проверил).
FileStream fs = File.Open(filename, FileMode.Open);
fs.SetLength(fs.Length-2);
fs.Close();

aleks2
Stream имеет метод SetLength.
А как этот повершелл устроен, там этот полная поддержка C#, а не какая то обрезка, так что SetLength однозначно поддерживается?

Нужно, конечно, тогда вместо таких утилиток на PowerShell писать...
21 июл 16, 11:12    [19434949]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
Владислав Колосов
Member

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

Вы как-то сложно заходите. Используйте SSIS для импорта-экспорта. У Вас классический (! редкость) CSV и проблемы на пустом месте.

Мне не нужен "экскаватор", чтобы выкопать ямку.
Использование SSIS - удел лохов.

Нечего сказать по делу - иди мимо.


Вы уже сколько суток потратили на решение проблемы? В помощью SISS такая задача пишется максимум за полчаса. Впрочем, невежество - страшная сила :) Цивилизации из-за этого гибли, не то, что проблемы импорта не решались.

SSIS - не экскаватор... Это система для любой задачи импорта-экспорта, как крупной, так и микроскопической. Самая простая реализация - выбрать правым кликом на базе "Задачи" - "Импорт данных...". Пакет можно сохранить для использования в проекте или выполнить без сохранения. Вы же пытаетесь использовать какие-то допотопные инструменты...
21 июл 16, 11:33    [19435144]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
powershell просто запускает полноценные сборки .NET.
И может фсе, что может .NET. За вычетом GUI.

У него своего, канешно, наворочено тоже.

PS. Хотя, может и GUI может.
21 июл 16, 11:36    [19435158]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
Владислав Колосов
aleks2
пропущено...

Мне не нужен "экскаватор", чтобы выкопать ямку.
Использование SSIS - удел лохов.

Нечего сказать по делу - иди мимо.


Вы уже сколько суток потратили на решение проблемы? В помощью SISS такая задача пишется максимум за полчаса. Впрочем, невежество - страшная сила :) Цивилизации из-за этого гибли, не то, что проблемы импорта не решались.

SSIS - не экскаватор... Это система для любой задачи импорта-экспорта, как крупной, так и микроскопической. Самая простая реализация - выбрать правым кликом на базе "Задачи" - "Импорт данных...". Пакет можно сохранить для использования в проекте или выполнить без сохранения. Вы же пытаетесь использовать какие-то допотопные инструменты...


Мне не "один раз файл" загрузить. А систему надо, которая будет грузить это быстро, долго и счастливо. По минимуму нагружая сервер.

На загрузку первого файла я потратил часа четыре. На загрузку 4-го - минут двадцать. Надо еще парочку состряпать.

На "изучение" SSIS уйдет гораздо больше.
21 июл 16, 11:40    [19435189]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
aleks2
Guest
   bulk insert #TB_SHIP
      from 'C:\Gate\In\TB_SHIP.csv' 
      with ( FORMATFILE = 'C:\Gate\In\TB_SHIP.fmt'
           , DATAFILETYPE = 'char'
           , CODEPAGE = 'RAW'
           , FIRSTROW = 2      
           );

Не знаю как кому, но мне такой допотопный способ нравится больше.
21 июл 16, 11:43    [19435214]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
Владислав Колосов
Вы уже сколько суток потратили на решение проблемы? В помощью SISS такая задача пишется максимум за полчаса. Впрочем, невежество - страшная сила :) Цивилизации из-за этого гибли, не то, что проблемы импорта не решались.

SSIS - не экскаватор... Это система для любой задачи импорта-экспорта, как крупной, так и микроскопической. Самая простая реализация - выбрать правым кликом на базе "Задачи" - "Импорт данных...". Пакет можно сохранить для использования в проекте или выполнить без сохранения. Вы же пытаетесь использовать какие-то допотопные инструменты...
А потом как вы это использовать будете? Как это передать в службу эксплуатации для развёртывания, а им ведь нужно написать инструкцию для выдачи прав, в пакете это ведь нетривиально? А логировать, а настраивать логирование в пакете, или для вас "программирование SSIS" - это мышкой налабать DataFlow, 2 квадратика и одна палочка, и потом нажать кнопочку для запуска?

Владислав Колосов
Вы как-то сложно заходите. Используйте SSIS для импорта-экспорта. У Вас классический (! редкость) CSV и проблемы на пустом месте.
Для безошибочного классического правильного (! редкость) CSV импорт пишется как одна (ОДНА!!!) строчка кода bulk insert, и без написания инструкции для развёртывания, плясками с правами и т.д., которые нужны для SSIS. То есть минуты vs дни.

Я не понимаю, как можно сравнивать строчку кода и целый проект, пихая везде "проект" как более простое решение???

А неправильный CSV, как у aleks2, боюсь, у SSIS тоже может вызвать проблемы.
21 июл 16, 15:17    [19437068]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
2311_denis
Member

Откуда: Краснодар
Сообщений: 5
Здравствуйте, помогите разобраться с ошибкой. Не могу понять в какую сторону копать. Ситуация такая:
Есть папка с XML-файлами, необходимо в каждый из них заглянуть и вывести определенные значения в таблицу, столкнулся с проблемой перебора файлов, команда OPENROWSET (BULK(.....) выдает ошибку "Сообщение 102, уровень 15, состояние 1, строка 34
Неправильный синтаксис около конструкции "@new_name"."

Вот код

if object_id('tempdb..#TEMPXML') is not null drop table #TEMPXML -- проверка на существование таблицы
create table #TEMPXML (name varchar (max), depth int, [file] int) -- создание временной таблицы
insert into #TEMPXML (name,depth,[file])
exec sys.xp_dirtree 'C:\XML\', 1,1

DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql as varchar (max)

DECLARE name_cursor CURSOR LOCAL FOR
SELECT name
FROM #TEMPXML
WHERE depth=1
ORDER BY name

OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
set @new_name = char(39)+'C:\XML\'+@name+char(39) --присваиваем переменной полный путь к файлу
print @new_name

DECLARE @xml XML
declare @inn nvarchar (max)

SET @xml =
(
SELECT * FROM OPENROWSET (
BULK @new_name, SINGLE_BLOB
) AS xml
)
set @inn = @xml.value('(/Файл/Документ/СвНП/НПФЛ/@ИННФЛ)[1]', 'nvarchar (max)')
select @inn as ИНН
-- переход к следующему файлу--

FETCH NEXT FROM name_cursor
INTO @name
END
CLOSE name_cursor
DEALLOCATE name_cursor
drop table #TEMPXML
20 сен 16, 21:18    [19689434]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1723
2311_denis,

+

ооо кто-то 6-НДФЛ походу полез копать


а что у вас объявление переменных то так страдает?
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql as varchar (max)


когда надо
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql varchar (max)
20 сен 16, 22:36    [19689671]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
o-o
Guest
felix_ff
а что у вас объявление переменных то так страдает?
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql as varchar (max)


когда надо
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql varchar (max)

нормальное у него определение переменных:
BOL
DECLARE
{
{ @local_variable [AS] data_type | [ = value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
| { @table_variable_name [AS] <table_type_definition> }

DECLARE @local_variable (Transact-SQL)

-----------
ошибка вот тут:
SELECT * FROM OPENROWSET (
BULK @new_name, SINGLE_BLOB
) AS xml

BOL
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
21 сен 16, 07:46    [19690075]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
2311_denis
Member

Откуда: Краснодар
Сообщений: 5
o-o
felix_ff
а что у вас объявление переменных то так страдает?
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql as varchar (max)


когда надо
DECLARE @name VARCHAR(max),
@new_name varchar (max),
@sql varchar (max)

нормальное у него определение переменных:
BOL
DECLARE
{
{ @local_variable [AS] data_type | [ = value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
| { @table_variable_name [AS] <table_type_definition> }

DECLARE @local_variable (Transact-SQL)

-----------
ошибка вот тут:
SELECT * FROM OPENROWSET (
BULK @new_name, SINGLE_BLOB
) AS xml

BOL
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )


o-o, Вы правы на счет ошибки, просмотрел кучу документации и сайтов с подобной проблемой, пробовал вместо @new_name подставлять '''+@new_name+''' и CHAR(39), всё равно ругается
21 сен 16, 08:45    [19690135]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1723
2311_denis,
Заверните в динамику тогда, там по идее строковый литерал должен быть

о-о,
Согласен забыл что там as как необязательная часть.
21 сен 16, 09:27    [19690211]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
2311_denis
Member

Откуда: Краснодар
Сообщений: 5
[quot felix_ff]2311_denis,
Заверните в динамику тогда, там по идее строковый литерал должен быть

Извиняюсь, я с SQL недавно столкнулся, и то пришлось по работе. То есть, Вы имеете ввиду конвертацию в динамический вид данных, я правильно понял?
21 сен 16, 09:41    [19690299]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
iljy
Member

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

нет, он имеет в виду собрать текст запроса в строковой переменной, а потом выполнить его через EXEC. Проверять, что у вас там собралось, удобно через PRINT
21 сен 16, 10:51    [19690619]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
2311_denis
Member

Откуда: Краснодар
Сообщений: 5
iljy
2311_denis,

нет, он имеет в виду собрать текст запроса в строковой переменной, а потом выполнить его через EXEC. Проверять, что у вас там собралось, удобно через PRINT


Спасибо за разъяснение. Попробую, позже отпишусь по результатам
21 сен 16, 11:16    [19690753]     Ответить | Цитировать Сообщить модератору
 Re: OPENROWSET( BULK ...  [new]
2311_denis
Member

Откуда: Краснодар
Сообщений: 5
2311_denis,

Помогла вот такая операция

declare @cmd_nds nvarchar(max) = N'select @xml = cast(t.data as xml) from openrowset(bulk ' + quotename(@new_name_nds, N'''') + N', single_blob) t(data)';

exec sp_executesql @cmd_nds, N'@xml xml output', @xml output;

Спасибо всем, кто отозвался, у меня вопрос закрыт
21 сен 16, 15:02    [19692581]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить