Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > Парсинг Excel

Автор: Itsys 13.3.2008, 13:34

Требуется импортировать данные из Excel файлов в таблицу MS SQL
В SQL таблица состоит из колонок Col1, Col2 ... Col30 тип ntext

Надо сделать импорт в эту таблицу произвольного Excel файла (структура может меняться, т.е. набор и количество колонок не постоянно) определенных колонок, т.е. не все подряд, а только допустим 3, 4 и 12 в соответствующие колонки таблицы MS SQL, т.е. 3 колонка в Col3, 4 в Col4, 12 в Col12 и начиная с определенной строки, например с 20 и до конца.

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

Второе больше не усложнение, а условие - файл Excel находится на другом сервере в сети и доступен через шару.

PS Данный механизм реализован на Perl, т.е. perl парсит Excel файл и с помошью запросов вставляет это в таблицу, но работает все это очень медлено файл Excel на 16000 строк и 20 колонок парсится около 1-2 минут. Загрузка процессора под 100%, весь отжирается памяти примерно вес файла *3 и загружается сеть, т.к. perl стоит там же, где лежит Excel - на другом сервере. Загрузку файла на 50000 строк, я так и не дождался smile

- обрубил.

ЗЫ Самый главный вопрос, если это реализовать средствами MS SQL, если это, конечно, впринципе возможно, будет ли это работать быстрее и меньше грузить процессор, память и сеть?

Автор: Akina 13.3.2008, 13:42

А почему не реализовать это собсно средствами VBA? прямо из модулёчка в самом этом Эксельном файле?
Или, например, промежуточная ADP-шка.

Автор: Magnifico 13.3.2008, 14:04

для 2005 open rowset

Код


Select *  From 
Openrowset('msdasql','DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DefaultDir=c:\files\names.xls', 
'Select * From [sheet1$]')

Автор: SharedNoob 13.3.2008, 14:52

А собственно ДТС пакет не выход ?

Автор: Itsys 13.3.2008, 15:06

Цитата(Akina @ 13.3.2008, 13:42

)

Потому-что это используется в интернет-магазине, написанном на perl, а файл Excel - это прайсы поставщиков, которые загружаются, сверяются с существующими товарами поставщика, корректируются цены и д.р. параметры товаров в магазине и добавляются новые - нет возможности добавить в Excel доп обработчики, т.к. файлы не наши, и обяснять как это делать каждому менеджеру после получения файла, собственно говоря не хочется smile

Цитата(Magnifico @ 13.3.2008, 14:04

)

для 2005 open rowset

У нас 2000 и пока никаких причин, чтобы покупать 2005 нет, хотя это может стать причиной, вопрос в скорости работы... насколько быстро данный запрос откроет файл на 10 мегов?

Добавлено через 2 минуты и 2 секунды

Цитата(SharedNoob @ 13.3.2008, 14:52

)

А собственно ДТС пакет не выход

Вообще не выход, т.к.

Цитата(Itsys @ 13.3.2008, 13:34

)

Надо сделать импорт в эту таблицу произвольного Excel файла (структура может меняться, т.е. набор и количество колонок не постоянно) определенных колонок, т.е. не все подряд, а только допустим 3, 4 и 12 в соответствующие колонки таблицы MS SQL, т.е. 3 колонка в Col3, 4 в Col4, 12 в Col12 и начиная с определенной строки, например с 20 и до конца.

Какие колонки импортировать а какие нет - определяет менеджер при импорте, и файлы у всех поставщиков очень уж разные smile

Автор: Magnifico 13.3.2008, 15:16

локальный xls на 10,3 мега
в менеджмент студио за 23 секунды 44392 строк

Автор: SharedNoob 13.3.2008, 15:17

Цитата(Magnifico @ 13.3.2008, 14:04)

для 2005 open rowset

Код


Select *  From 
Openrowset('msdasql','DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DefaultDir=c:\files\names.xls', 
'Select * From [sheet1$]')

Дык а чем не устраивает это ? возвращает набор данных который лежит в ексель файле .... да дальше вороти-нехачу .

Добавлено @ 15:18
ой извеняюсь, недочитал ...sql2005

Добавлено @ 15:24

ДЛЯ SQL 2000
вариант 1

Код


SELECT *
FROM OPENDATASOURCE(
'Microsoft Excel Driver (*.xls)',
'Data Source=D:\ ;'
)...[название файлла без расширения]

вариант 2

Код


SELECT  *
  FROM OPENROWSET  ('MSDASQL',
                    'Driver={Microsoft Excel Driver (*.xls)};
                     SourceDB=d:\;
                     DefaultDir=d:\;
                     SourceType=XLS;
                     Exclusive=No;
                     BackgroundFetch=Yes;
                     Collate=Russian;
                     Null=No;
                     Deleted=No;',
                     'SELECT * FROM [название файлла без расширения]')

код на работоспособность не проверял но с драйвером для DBF все пашет

Автор: Itsys 13.3.2008, 15:41

А DefaultDir можно задать как \\server\files\?

Автор: SharedNoob 13.3.2008, 15:53

QA => F1 => поиск по указателю => OPENDATASOURCE (или OPENROWSET) => enter
и читаем.

ps
для начала прочитайте данные с локального компьютера, а потом можно и подключить сетевой диск если что.

Автор: Itsys 13.3.2008, 16:09

Цитата(SharedNoob @ 13.3.2008, 15:53

)

QA => F1 => поиск по указателю => OPENDATASOURCE (или OPENROWSET) => enter

До этого я сам догадался smile

. Спасибо что напомнил, что есть такая функция в MS SQL (шутка). Ладно протестирую сообщу результаты.

Автор: Itsys 16.3.2008, 12:17

Код


SELECT TOP 10 * FROM OPENROWSET(
 'MSDASQL', 
 'Driver=Microsoft Excel Driver (*.xls);DBQ=\\pavlov\Files\34', 
 'SELECT * FROM [LIST$]'
) as xls

Только парсит он не правильно smile

В файле Excel строка:

Код


New    NUM    CISBN    IDNAME    CLARICHEV    C_SERIE    CNAME    CAUTHOR    CLONGNAME    IINPACK    IPAGETOTAL    CFORMAT    CCOVER    cSizes1    iWeight1    YPRICE    CPARTITION    CSUBPRTION    CPUBLNAME    CEAN    cComplete

А он выдает:

Код


New    NULL    CISBN    NULL    NULL    C_SERIE    CNAME    CAUTHOR    CLONGNAME    NULL    NULL    CFORMAT    CCOVER    cSizes1    NULL    NULL    CPARTITION    CSUBPRTION    CPUBLNAME    CEAN    cComplete

Почему некоторые значения изменены на NULL?

Автор: Magnifico 16.3.2008, 14:51

Могут разные тиы данных в одном столбце:
1
2
3
4
5
6
7А

7A это потенциальный нулл - ошибка преобразования

если не принципиальны типы данных: приводить столбцы эксель к строке

Код


Sub ПривестиКСтроке()
    Dim temp As String
    Dim str As String
      str = "'"
    For Each c In Selection
     temp = Trim(c.Value)
    
     c.Value = str & temp
     Next c
End Sub

Автор: Itsys 16.3.2008, 22:11

С Excel-ем сделать ничего не могу.... т.к.

Цитата(Itsys @ 13.3.2008, 15:06

)

Есть друие предложения и варианты?

PS хотелось бы сделать все без вмешательства пользователей - получил файл по почте - загрузил в back-office интернет-магазина и все - дальше система сама все обрабатывет, менеджеру надо только подтверждать выполнение определнных действий.. smile

Автор: Magnifico 17.3.2008, 18:48

попробуй с cast(ПОЛЕ as nvarchar(255)) поиграться

Код


select  [ЗДЕСЬ]   from openrowset(
 'MSDASQL', 
 'Driver=Microsoft Excel Driver (*.xls);DBQ=\\pavlov\Files\34', 
 'SELECT   [ИЛИ ЗДЕСЬ]  FROM [LIST$]'
) as xls

если использовать эксель в качестве источника данных ADO ,OLE то полюбому будет приводится столбец к определенному типу данных
(которых больше в столбце) и будут ошибки преобразования.
Только банально перебирать столбцы и строки и приводить каждую ячейку к определн формату

для обработки грязных юзерских данных может и понадобится сложные обработчики писать и не в SQLSERVERE
да и ресурсоемкое это занятие

Автор: Itsys 17.3.2008, 21:21

Проблема только в том, что исходный файл произвольный, т.е. изначально не известно даже сколько колонок в файле, заголовки полей моут стоять в любой строке файла - моут в первой, а могут и в 20, так как в приведенном выше примере, и, первым делом чего я хочу сделать, так это получить хотябы список полей, а он мне вместо заголовка поля выдает NULL.

Есть ли способ CASTить все поля запроса в VARCHAR без их конкретного указания, ну типа CAST(* AS VARCHAR(5000))?

Автор: Beltar 18.3.2008, 08:42

Возможно, имеет смысл накидать простейшую софтину в Delphi\Builder\VS в которой просто открывать файл и указывать самому какие столбцы и с какой строчки импортировать и в какое поле БД. И пусть эта софтина все в базу кидает. Может этот процесс займет несколько минут, но ведь вы явно не по 50 файлов в день получаете. А пытаться все предусмотреть в том числе как будет названо поле с названием товара, например, "товар", "наименование" или даже "название фильма", если торгуете DVD-дисками, нереально.
Импорт в сам Excel текстовых файлов сделан именно так.

Автор: Magnifico 18.3.2008, 16:27

есть еще опции HDR=Yes;IMEX=1;

http://connectionstrings.com/?carrier=excel

"HDR=Yes; считает первую строку заголовком полей
"IMEX=1;" интерпретирует данные как текст

Автор: Magnifico 18.3.2008, 17:54

используй лучше оле дб он покорректней работает

Код


select * 
from openrowset('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0; HDR=YES; IMEX=1;Database=C:\my.xls'
, [sheet1$])


Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\my.xls;
Extended Properties="Excel 8.0;HDR=Yes;"')...[sheet1$]

Автор: Itsys 18.3.2008, 20:50

Сработало!!!!

Beltar, Обработка на perl уже написана, толь проблема в том, что грузится очень долго, вот и ищу возможность быстрее обработать данные.

Автор: Itsys 19.3.2008, 00:24

Только обрадовался.... по условию я не знаю название листа, и мне надо выбрать первый из существующих. Как указать не название листа sheet1$, а его порядковый номер?

Автор: Magnifico 19.3.2008, 11:44

Код


Sub ПеребратьИменаЛистов()
Dim XLSFile As String
Dim i As Integer
i = 1
XLSFile = "C:\files\namesw.xls"

Workbooks.Open XLSFile

Dim ws As Worksheet
For Each ws In Worksheets
    
    MsgBox "Имя " & i & " -го листа: " & ws.Name 
     
    Debug.Print "Имя " & i & " -го листа: " & ws.Name
     i = i + 1
Next ws
End Sub

Автор: Itsys 19.3.2008, 14:30

Вопрос только в том, как это запустить, а потом еще и желательнов запрос передать... мне сами именя листов не нужны, мне надо выбирать данные из первого листа в файле, может можно это как-нибудь задать не перебирая с помощью VB файл?

И так

Код


select * 
from openrowset('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0; HDR=YES; IMEX=1;Database=C:\my.xls'
, [1])

и так

Код


select * 
from openrowset('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0; HDR=YES; IMEX=1;Database=C:\my.xls'
, [sheet[1]$])

пробовал...

Автор: Magnifico 19.3.2008, 20:49

Цитата

Вопрос только в том, как это запустить

excel -> (alt + F11)(Редактор VB) - > insert ->module ->копироватьКодСюда ->правим пути в коде ->RUN

Ole automation это единственный способ добраться к свойствам и методам Эксель
Все языки программирования интегрируются с Эксель именно так, (и ничего другого кроме вышеприведенного кода не придумаешь)

Никаким запросом имена листов узнать невозможно
Эксель не база данных ,и нет возможности получить Информационную схему

Цитата

желательно в запрос передать

Если хочешь помучиться в 2000 есть расширенные хранимые процедуры на c++ (Visual Studio 6  )
(у меня на с++ "аллергия")

В БОЛЕ набрать OLE Automation там есть какие то методы работы , можно вызывать VBA методы и св-ва  (не разбирался)
ищи хороший пример

Или писать прогу на любом языке ,опять же интеграция с Эксель(код вверху)
подключение к sql server ->  передача параметров из встроенного VBA  в  хранимую процедуру (с openrowsetoM)

Цитата

мне надо выбирать данные из первого листа в файле

Вот из за этой казалось мелочи придется сильно помучиться (от Vba не убежишь)

Автор: Itsys 19.3.2008, 21:36

В общем решил я отказаться от этой затеи, т.к. срорее всего никакого убыстрения по сравнению с существующей обработкой на Perl я не получу.

Еще раз спасибо.

Автор: Magnifico 19.3.2008, 23:11

в 2005 это работает:

Код


declare @file_name varchar(255), @h_application int, @hr int,@h_workbook int ,
@data varchar(255),@source varchar(255),@description varchar(255)

set @file_name = 'c:\files\serge.xls'

exec @hr = sp_OACreate 'Excel.Application', @h_application OUT

exec @hr = sp_OAMethod @h_application, 'Application.workbooks.Open', @h_workbook OUT , @file_name 
exec @hr = sp_OAGetProperty @h_application, 'Workbooks(1).Sheets(1).Name', @data OUT

SELECT @data as [dat]
exec sp_OAMethod @h_application, 'Quit'
exec @hr=sp_OADestroy @h_application

--set @data ='NewSheet1';

declare @SQL  nvarchar(4000)
set @SQL = 
'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0''' +','+'''Excel 8.0;HDR=Yes;IMEX=1;Database=C:\files\serge.xls'''+','+ '''Select * From ['+ @data +'$]'')'

exec(@sql)

Автор: Itsys 19.3.2008, 23:55

Код


[quote=Magnifico, 19.3.2008,  23:11, post1447877]declare @file_name varchar(255), @h_application int, @hr int,@h_workbook int ,
@data varchar(255),@source varchar(255),@description varchar(255)

set @file_name = 'c:\files\serge.xls'

exec @hr = sp_OACreate 'Excel.Application', @h_application OUT

exec @hr = sp_OAMethod @h_application, 'Application.workbooks.Open', @h_workbook OUT , @file_name 
exec @hr = sp_OAGetProperty @h_application, 'Workbooks(1).Sheets(1).Name', @data OUT

SELECT @data as [dat]
exec sp_OAMethod @h_application, 'Quit'
exec @hr=sp_OADestroy @h_applicatio[/quote]

Насколько я понимаю, это все работает не очень быстро smile

Автор: Magnifico 20.3.2008, 09:27

Это работает мгновенно!
Получить имя первого листа первой открытой книги- какая же здесь может быть нагрузка

Код


exec @hr = sp_OAGetProperty @h_application, 'Workbooks(1).Sheets(1).Name', @data OUT

я просто незнаю будет ли работать в 2000?

Что то ты рано сдался! Это то что доктор прописал !!!!!!!!!!!!!!!!

Одна тонкость Workbooks(1).Sheets(1).Name -мы получаем имя первого листа первой открытой книги,если будет открыта другая книга,
допустим локально , а потом будет выполнен этот запрос -то подхватит именно эту книгу ,а

Код


exec @hr = sp_OAMethod @h_application, 'Application.workbooks.Open', @h_workbook OUT , @file_name

будет уже второй...
Из этого следует лучше обращаться к книге по имени (надюсь её название хотя бы известно или тоже скрипт писать ,перебирая все
эксель файлы? smile

)

Код


Workbooks("Names.xls").Sheets(1).Name

в 2000 OLE Automation должно как то включаться (поиск)

Автор: Itsys 20.3.2008, 17:41

Ну попробую.... насколько я знаю, OLE в принципе не самый быстрый способ, т.к. при его использовании загружается приложение сервер - это раз, считывается в память файл - это два и только потом можно из него чего-нибудь получить.