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

Откуда:
Сообщений: 11
Все привет. Вкратце изложу проблему: есть Excel файл (.xls) есть таблица в БД. Импортирую следующим запросом:
set @SQL = N'


INSERT INTO [192.168.3.13].[NONAME].[dbo].[TABLE]
           ([PR_NAME]
           ,[FULL_NAME]
           ,[WWW]
           ,[EMAIL]
           ,[ADRES]
           ,[PRODUCT]
           ,[FIO1]
           ,[POST1]
           ,[TEL1]
           ,[TEL2]
           ,[TEL3]
           ,[TEL4]
           ,[TEL5]
           ,[TEL6]
           ,[DOPNUM]
           ,[OTRASL]
           ,[PODOTRASL]
           ,[PR])
	Select '+@columns1+','+str(@pr1)+' 
    from OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
     ''Excel 8.0;HDR=Yes; IMEX=1; CommandTimeout = 180; Database='+@filename1+';'',
    ''SELECT '+case when @test1=1 then 'top 1' else '' end+' * FROM ['+@listname1+']'' )'  
 Exec sp_executesql @SQL

Параметр @columns1 имеет вид:

cast([Наименование] as varchar),'' '','' '','' '',cast([Адрес] as varchar),cast([Товары/услуги] as varchar),'' '','' '',cast([Телефон 1] as varchar),cast([Телефон2] as varchar),cast([Телефон3] as varchar),'' '','' '','' '','' '',cast([Отрасль] as varchar),cast([Подотрасль] as varchar)

Сам процесс импорта происходит без сучка и задоринки. Так в чем же проблема ?! - спросят многие из вас. Проблема в том, что OleDB провайдер расценивает номера телефонов (а именно столбцы [Телефон 1],[Телефон 2],[Телефон 3]) как числа и заносит их в базу в следующем (экспоненциальном) виде:

2.9961e+006 (в то время как в Excel он хранится так: 2996099)

Теперь о том как я пробовал решить эту проблему:
1. Менял формат ячеек в Excel
2. Переливал данные из Excel в новый Excel лист, предварительно задавая там формат ячеек
3. попробовал (как вы видите) приведение к типам со стороны SQL server
4. прописывал в ячейке апостроф слева для того, чтобы привести к тексту, и о аллилуя, это помогло,НО неужели мне придется в каждую ячейку это прописывать, там ведь сотни строк в файле????

Ребят, помогите. Так хочется автоматизировать этот процесс. Ведь как назло разработал классный графический интерфейс для этого на C#, настроил SQL server и как назло такая мелочь, муха, которую не могу прихлопнуть!!! Может быть программно можно столбцы Excel можно как-то предварительно преобразовать??
14 июл 11, 09:59    [10971439]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
Glory
Member

Откуда:
Сообщений: 104751
lildare
4. прописывал в ячейке апостроф слева для того, чтобы привести к тексту, и о аллилуя, это помогло,НО неужели мне придется в каждую ячейку это прописывать, там ведь сотни строк в файле????

Придется
Потому что именно так задается тип данных ячейки.
Не путать с форматом отображение

lildare
Ребят, помогите. Так хочется автоматизировать этот процесс. Ведь как назло разработал классный графический интерфейс для этого на C#, настроил SQL server и как назло такая мелочь, муха, которую не могу прихлопнуть!!! Может быть программно можно столбцы Excel можно как-то предварительно преобразовать??

Сохраните ваш Excel как текстовый файл. Масса проблем исчезнет.
14 июл 11, 10:07    [10971471]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
lildare
Member

Откуда:
Сообщений: 11
если что, стучите в аську: 570-804-318
14 июл 11, 10:09    [10971476]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
lildare, попробуйте так:

ab
1,00*дурим*
2996099,002996099,00


 SELECT a,
        b --INTO #tst 
 FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1; Database=D:\Книга1.xls ', [Лист1$])
Ну или работайте через OLE со стороны клиента, либо CLR на сервере тоже можно поиспользовать.
14 июл 11, 10:34    [10971603]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
lildare
Member

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

фишку понял!:) заливается нормально. но тогда вопрос, как сделать так чтобы первая строка (1,00 *дурим*) не заливалась в базу?
14 июл 11, 11:06    [10971865]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
ё
Guest
lildare
Проблема в том, что OleDB провайдер расценивает номера телефонов (а именно столбцы [Телефон 1],[Телефон 2],[Телефон 3]) как числа и заносит их в базу в следующем (экспоненциальном) виде:

2.9961e+006 (в то время как в Excel он хранится так: 2996099)


собственно, kDnZP, об этом уже сказал, но навсякей ))

драйвер, при стандартных настройках - проверяет (и принимает решение о том какой тип) - первые 8-мь строк данных

http://office.microsoft.com/en-us/access-help/initializing-the-microsoft-excel-driver-HP001032159.aspx
Entry Description
TypeGuessRows The number of rows to be checked for the data type. The data type is determined given the maximum number of kinds of data found. If there is a tie, the data type is determined in the following order: Number, Currency, Date, Text, Boolean. If data is encountered that does not match the data type guessed for the column, it is returned as a Null value. On import, if a column has mixed data types, the entire column will be cast according to the ImportMixedTypes setting. The default number of rows to be checked is 8.

если установить этот параметр TypeGuessRows=0, то будут проверятся все строки,
и если где-то в строках есть НЕ валидный числу Телефон,
весь столбец будет импортироватся с типом =ImportMixedTypes
14 июл 11, 11:11    [10971902]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
lildare
Member

Откуда:
Сообщений: 11
я тут подумал и решил сделать так:

со стороны клиента приписывать апостроф к первой (ну если быть точнее то ко второй) ячейке столбца, тогда остальные ячейки в столбце будут расцениваться как текст,и всё шикарно!) жаль только придется тратить лишние секунды на это действие))
14 июл 11, 11:13    [10971919]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
ё
Guest
lildare
cast([Телефон 1] as varchar),cast([Телефон2] as varchar),cast([Телефон3] as varchar),

а вы не пробывали так, в исходном запросе
cast(cast([Телефон2] as int) as varchar)
непроканает, случайно )) ?
14 июл 11, 11:19    [10971989]     Ответить | Цитировать Сообщить модератору
 Re: Импорт из Excel в SQL server 2005 через openrowset  [new]
lildare
Member

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

там могут быть и номера сотовых (тогда уж bigint использовать только если). и возможен такой такой вариант, что номер будет записан с помощью скобочек или черточек: напрмер: 8(950)ххх-хх-хх,

хотя это бывает редко, что жаль, ибо это мне сыграло бы на руку при заливке))
14 июл 11, 11:41    [10972195]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить