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

Откуда:
Сообщений: 33
Исходные данные:
XLS файлы (доки поставщиков, с/ф, торг-12, упд)
имеются на них линкованые сервера ([xlsPrixodSF], [xlsPrixodTORG12], [xlsPrixodUPD]), провайдер Microsoft.ACE.OLEDB.12.0
Требуется:
Закачать данные в таблицы базы
Решение:
основная закачка идет через OPENROWSET
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0; database=D:\ForExchange\pTORG12.xls',
                   'SELECT * FROM [TDSheet$A20:FZ]')
Вроде как в целом - работает..
Проблема:
В ТОРГ-12 есть колонка [3] - код товара. у разных поставщиков все по разному, в основном цифро-буквенные значения, однако у некоторых есть чистые цифры без букв (внутренний id товара вероятно). С такими возникают проблемы - часть кодов переносятся нормально, часть в виде "12345Е+06". решение - проверка на NUMERIC:
CodePost = CASE WHEN ISNUMERIC([3]) = 1 THEN CAST(CAST([3] AS BIGINT) AS VARCHAR(50))
                    ELSE [3]
               END

но не тут то было! Решил проверить в полевых условиях и вручную поменял парочку кодов в XLS, 251541 на фф251541 +
251543 на 251543фф. И при закачке обнаружил неприятную вещь - в [3] у этих позиций стояли NULL
причем что интересно, в прилинкованном сервере в [3] есть все что нужно
SELECT * FROM [xlsPrixodTORG12]...[TDSheet$]
но этот SELECT меня не устраивает по той причине, что во первых, колонки называются как попало, во вторых, данные берутся с самого начала, не как в OPENROWSET с 20й строки, игнорируя шапку (FROM [TDSheet$A20:FZ])
Причина:
Я не уверен на 100%, но похоже, при отработке OPENROWSET тип полей присваивается "автоматом", по каким то определенным правилам, и полю [3] присваивается тип FLOAT, а мне конечно же хочется NVARCHAR. И, т.к. значения "фф251541", "251543фф" не могут преобразоваться во FLOAT, в поле прописывается NULL

1. Как решить эту проблему, есть способ?
2. Как выбирать с линкованого XLS данные с определенной строки + значения полей предыдущей строки должны быть именами колонок ?
30 авг 17, 07:15    [20757594]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
sdh
Member

Откуда:
Сообщений: 33
способ SELECT-а с определенной строки вроде нашел
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL 
    DROP TABLE ##tmp
SELECT
    IDENTITY( INT, 1,1 ) AS ROW
  , xls.*
INTO
    ##tmp
FROM
    [xlsPrixodTORG12]...[TDSheet$] AS xls
SELECT
    *
FROM
    ##tmp
WHERE
    ROW >= 20
осталось только выяснить, как имена колонок "вытащить" с 20й строки и переименовать во временной таблице. сейчас они выглядят наподобие [F1], [F2] ... [Fn]. нужно "переприсвоить" им имена из их значений ( [F1] = [1], [F3] = [2], [F8] = [3] и т.д...)
30 авг 17, 08:04    [20757617]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
Невский
Member

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

ISNUMERIC не всегда правильно определяет содержимое поля как число. Например:

select ISNUMERIC('1.2345e+013'),ISNUMERIC('100'),ISNUMERIC('0100')

Возвращает все три результата как нумерик, хотя третье для тебя актуально как текст.

Если проблема только в больших числах с "e+", то не проще ли CASE WHEN ISNUMERIC([3]) = 1 заменить на CASE WHEN CHARINDEX('e+',([3])) > 0
30 авг 17, 15:22    [20759109]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
lefty1
Guest
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;database=D:\ForExchange\pTORG12.xls;IMEX=1',
'SELECT * FROM [TDSheet$A20:FZ]')
30 авг 17, 15:34    [20759173]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
sdh ,
я использую макет таблицы, он даёт имена столбцов и типы
select 
Customer, Customer_Name, MLFB, GBK, FAGR, PG, datum, mnozstvi, new_orders, obrat, VSP
from Template_Table
where 0=1

UNION ALL

SELECT * FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;HDR=NO; IMEX=1; 
database=D:\DATA\business.xlsx',
'SELECT * FROM [List1$A2:I]')
30 авг 17, 16:20    [20759371]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
Ганс1970
Guest
Шыфл,
может, достаточно IMEX=1 ?
Если данные вставляются в какую-то таблицу, преобразование к нужному типу произойдет автоматически при вставке.
30 авг 17, 17:19    [20759575]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с типами полей при работе OPENROWSET, закачка из XLS файла  [new]
sdh
Member

Откуда:
Сообщений: 33
IMEX=1
то что доктор прописал! помогло
Шыфл
sdh ,
я использую макет таблицы, он даёт имена столбцов и типы
select 
Customer, Customer_Name, MLFB, GBK, FAGR, PG, datum, mnozstvi, new_orders, obrat, VSP
from Template_Table
where 0=1
UNION ALL
SELECT * FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;HDR=NO; IMEX=1; 
database=D:\DATA\business.xlsx',
'SELECT * FROM [List1$A2:I]')
крайне интересное решение, спасибо за идею. правда там оказался небольшой побочный эффект - при селекте строки отсортировались сами собой (вверх ушли "пустые", с NULL в значениях), надо проанализировать, не выйдет ли это боком еще где нибудь, при дальнейшей обработке закачиваемых данных, но я уже приценился, где такой приемчик можно использовать. респект :)
31 авг 17, 01:57    [20760266]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить