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

Откуда: Stk
Сообщений: 30
Подскажите как быть. есть работающий запрос и он переносит все что есть на листе excel в бд, а как сделать выборку диапазона данных? например с А4 х В54
SELECT * INTO XLImport1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\2.xls', 'SELECT * FROM [test$]')
10 ноя 09, 13:14    [7907400]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
нашел ответ, нужно было всего лишь вставить диапазон после страницы :-[
test$A4:B54
10 ноя 09, 13:29    [7907512]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
появился такой вопрос. Есть 2 таблицы в sql базе, в одной(material) находится имя материала и его id, а другая таблица(temp) создается с помощью запроса. Как написать, чтобы из excel брались данные материала и записывались в таблицу "temp" с id'шками из таблицы "material" ?
10 ноя 09, 14:12    [7907826]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
iljy
Member

Откуда:
Сообщений: 8711
Etamin419,
select t1.*, t2.id from
#XLImport t1 -- можете предварительно импортнуть сюда, можете заменить на OPENROWSET
  join
material t2 on t2.name = t1.name
10 ноя 09, 14:18    [7907877]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

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

select t1.*, t2.id from
#XLImport t1 -- можете предварительно импортнуть сюда, можете заменить на OPENROWSET
  join
material t2 on t2.name = t1.name

Извените, не до конца понял смысла, а откуда берется t1.[name] ?
select *, t2.[name] INTO t1
	FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;Database=C:\test\2.xls',
					'SELECT * FROM [test$E8:E31]') as name INNER JOIN
                    dbo.material t2 ON t2.[name] = t1.name
у меня он ругается что не может найти t1
The column prefix 't1' does not match with a table name or alias name used in the query.
12 ноя 09, 09:19    [7917929]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
iljy
Member

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

select t1.*, t2.id from
#XLImport t1 -- можете предварительно импортнуть сюда, можете заменить на OPENROWSET
  join
material t2 on t2.name = t1.name

Извените, не до конца понял смысла, а откуда берется t1.[name] ?
select *, t2.[name] INTO t1
	FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;Database=C:\test\2.xls',
					'SELECT * FROM [test$E8:E31]') as name INNER JOIN
                    dbo.material t2 ON t2.[name] = t1.name
у меня он ругается что не может найти t1
The column prefix 't1' does not match with a table name or alias name used in the query.

ясное дело не может! вы на OPENROWSET псевдоним сделайте не name, а t1, и будет вам счастье.
12 ноя 09, 12:31    [7919436]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
iljy,
благодарю, работает.
а как можно сделать, чтобы из двух колонок в excel'е данные объединялись в колонку Data типа datetime?
дата Время
31 августа21
13 ноя 09, 08:14    [7923583]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
pacha
Member

Откуда:
Сообщений: 239
Странное у вас представление даты. Именно так оно выглядит? Т.е. без года? Вообще, в Экселе колонки тоже имеют типизацию. Попробуйте сконвертировать в правильный формат. Еще есть sql функция dateadd которая вам тоже может пригодиться
13 ноя 09, 08:44    [7923628]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
pacha,
да, к сожалению дата выглядит так и формат ячеек в экселе стоит тип "дата", проблема в том что таких документов много и править дату во всех нереально.
Но в каждом документе страницы пронумерованы в таком духе "31.08.2009 г. ", если как то можно вытягивать от туда дату, то было бы неплохо :)
13 ноя 09, 09:18    [7923732]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
pacha
Member

Откуда:
Сообщений: 239
Ну если формат ячеек - дата, то это просто отображение. И вы легко сконвертируете поле "дата" из Эксель в sql поле типа datetime без дополнительных преобразований. А потом уж воспользуйтесь функцией dateadd и прибавьте то, что у вас там в колонке "время". Часы?
13 ноя 09, 09:46    [7923845]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
pacha,
Да, в колонке время указаны часы.
С функцией dateadd разобрался, но вот с конвертированием даты чтото не выходит.
select  convert(datetime,F1)  as Time into #Date
	FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;Database=C:\test\2.xls',
					'SELECT F1 FROM [test$A8:E31]') as t3
ругается что не может сконвертировать "30 августа"
пробовал менять отображение даты "30 августа 2009", но все равно ошибка
Syntax error converting datetime from character string.
13 ноя 09, 13:25    [7925706]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
iljy
Member

Откуда:
Сообщений: 8711
Etamin419
pacha,
Да, в колонке время указаны часы.
С функцией dateadd разобрался, но вот с конвертированием даты чтото не выходит.
select  convert(datetime,F1)  as Time into #Date
	FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;Database=C:\test\2.xls',
					'SELECT F1 FROM [test$A8:E31]') as t3
ругается что не может сконвертировать "30 августа"
пробовал менять отображение даты "30 августа 2009", но все равно ошибка
Syntax error converting datetime from character string.

Попробуйте
select  case ISDATE(F1) when 1 then convert(datetime,F1) end as Time, F1 into #Date
	FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;Database=C:\test\2.xls',
					'SELECT F1 FROM [test$A8:E31]') as t3

потом может посмотреть проблемные строки
select F1 from #Date
where Time is null
13 ноя 09, 13:43    [7925877]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

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

Пробовал как вы предложили, но не сработало. он выводит все значения даты пустыми строками. А можно ли как нибудь в экселе вытащить имя листа? просто там у меня каждый лист называется отдельной датой (31.08.2009 г.)
16 ноя 09, 13:02    [7933319]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
iljy
Member

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

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

Что именно не сработало?

Etamin419
А можно ли как нибудь в экселе вытащить имя листа? просто там у меня каждый лист называется отдельной датой (31.08.2009 г.)

? SELECT * FROM [test$], имя листа - test.
16 ноя 09, 13:23    [7933493]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

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

Что именно не сработало?

функция case ISDATE не перевела дату из экселя в таблицу sql, он вывел пустые значения null
16 ноя 09, 13:38    [7933638]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Glory
Member

Откуда:
Сообщений: 104760
Etamin419
iljy

Что именно не сработало?

функция case ISDATE не перевела дату из экселя в таблицу sql, он вывел пустые значения null

Наверное потому, что у вас там не даты в понимании MSSQL
16 ноя 09, 13:40    [7933649]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

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

да MSSQL не понимает что там дата, он берет ее как строку типа int, просто я думал есть такая функция, которая преобразовывает строку "30 августа 2009 г." в тип datetime "30.08.2009"
хотя в ячейке на которую я ссылаюсь в экселе, стоит тип "Дата".
16 ноя 09, 13:52    [7933751]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Glory
Member

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

да MSSQL не понимает что там дата, он берет ее как строку типа int, просто я думал есть такая функция, которая преобразовывает строку "30 августа 2009 г." в тип datetime "30.08.2009"
хотя в ячейке на которую я ссылаюсь в экселе, стоит тип "Дата".

Тип отображения экселя не меняет тип данных в ячейке
Если у вас там хранится(!не отображается) int, то вам придется самому преобразовывать эти значения в datetime
16 ноя 09, 13:55    [7933781]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Etamin419
Member

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

Etamin419

А можно ли как нибудь в экселе вытащить имя листа? просто там у меня каждый лист называется отдельной датой (31.08.2009 г.)

? SELECT * FROM [test$], имя листа - test.


нет, я хотел узнать, можно ли ссылаться на какой нибудь номер листа, не зная как он называется

или как можно указать переменную, где указываем лист и диапазон ячеек
пробовал так сделать, но ругается на ковычки
declare @sql nvarchar(250)
set @sql = N'select /*as Time,*/ DATEADD(HOUR,F2,case ISDATE(F1) when 1 then convert(datetime,F1) end) as date, 
t2.id_mat_en into #Date
	FROM OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',
    ''Excel 8.0;IMEX=1;HDR=NO;Database=' + @FileName + ''',
	''SELECT F1,F2,F5 FROM ['+@ch+']'') as t3 INNER JOIN
    dbo.dictMatEnergo t2 ON SUBSTRING(t2.[name], 1, 2) = t3.F5'
exec (@sql)

Unclosed quotation mark before the character string 'SELECT F1,F2,F5 FROM '.
Line 5: Incorrect syntax near 'SELECT F1,F2,F5 FROM '.


Подскажите в чем ошибка у меня?
17 ноя 09, 12:33    [7938824]     Ответить | Цитировать Сообщить модератору
 Re: перенос данных из Excel в базу SQL  [new]
Glory
Member

Откуда:
Сообщений: 104760
Etamin419

Подскажите в чем ошибка у меня?

А вы вместо exec(@sql) поставьте print @sql и сами все увидите
17 ноя 09, 12:38    [7938873]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить