| Author |
 Topic  |
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
 Posted - 04/07/2010 : 10:30:28
|
quote:
Originally posted by sathiesh2005
Hi Madhivanan,
I am using the below code. This works fine when i execute in in a administrator(sa) login. But it is not working in my regular sql login.
SELECT * INTO #XLImportTEST
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\WebApp~1\Internet-Portal\sggi\Domestic\Testing.xls','SELECT * FROM [Sheet1$]')
This is the error message:
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
Is it possible to execute it through my login? i.e., by giving permission to my login ? If possible which permission should i give to my login.
Thanks in advance.
Regards,
Sathieshkumar. R
Run this
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
SELECT * INTO #XLImportTEST
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\WebApp~1\Internet-Portal\sggi\Domestic\Testing.xls','SELECT * FROM [Sheet1$]')
GO
sp_configure 'show advanced options', 0
RECONFIGURE
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
sathiesh2005
Yak Posting Veteran
India
85 Posts |
Posted - 04/08/2010 : 03:05:59
|
quote:
Originally posted by madhivanan
quote:
Originally posted by sathiesh2005
Hi Madhivanan,
I am using the below code. This works fine when i execute in in a administrator(sa) login. But it is not working in my regular sql login.
SELECT * INTO #XLImportTEST
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\WebApp~1\Internet-Portal\sggi\Domestic\Testing.xls','SELECT * FROM [Sheet1$]')
This is the error message:
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
Is it possible to execute it through my login? i.e., by giving permission to my login ? If possible which permission should i give to my login.
Thanks in advance.
Regards,
Sathieshkumar. R
Run this
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
SELECT * INTO #XLImportTEST
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\WebApp~1\Internet-Portal\sggi\Domestic\Testing.xls','SELECT * FROM [Sheet1$]')
GO
sp_configure 'show advanced options', 0
RECONFIGURE
Madhivanan
Failing to plan is Planning to fail
Hi Madhi,
Thank you very much for the reply.
I tried the above suggested by you, results in the following error.
I think i should get permission to my login (My login mame is sglass).
when i run the same through a login with full permission, it is working. Please tell me which permission should i get to my login.
error is:
Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.
Server: Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.
Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Valid configuration options are:
Server: Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.
Server: Msg 7415, Level 16, State 1, Line 3
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.
Server: Msg 5812, Level 14, State 1, Line 3
You do not have permission to run the RECONFIGURE statement.
Regards,
Sathieshkumar. R |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 04/08/2010 : 05:13:50
|
Ok. Your login has no permission to do it
Have you tried LinkedServer?
Read about sp_addLinkedServer in SQL Server help file
Madhivanan
Failing to plan is Planning to fail |
|
|
|
sathiesh2005
Yak Posting Veteran
India
85 Posts |
Posted - 04/08/2010 : 05:30:41
|
quote:
Originally posted by madhivanan
Ok. Your login has no permission to do it
Have you tried LinkedServer?
Read about sp_addLinkedServer in SQL Server help file
Madhivanan
Failing to plan is Planning to fail
Thanks Madhi,
I am using linked server between two sql servers.
I will try it with Excel and get back to you.
Regards,
Sathieshkumar. R
Regards,
Sathieshkumar. R |
|
|
|
kashyapsid
Yak Posting Veteran
India
78 Posts |
Posted - 04/08/2010 : 06:34:31
|
this works
KaShYaP |
|
|
|
sathiesh2005
Yak Posting Veteran
India
85 Posts |
Posted - 04/09/2010 : 06:47:01
|
quote:
Originally posted by kashyapsid
this works
KaShYaP
Its working, but depending on the permission that your sql login have.
I checked it with different sql logins, its not working with my login.
Regards,
Sathieshkumar. R |
|
|
|
abartoch
Starting Member
Thailand
1 Posts |
Posted - 04/20/2010 : 23:21:20
|
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
SET ANSI_PADDING OFF
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=U:\WWW\acc\300\upexcel300.xls',Sheet1$)
help together |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 04/21/2010 : 02:47:53
|
quote:
Originally posted by abartoch
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
SET ANSI_PADDING OFF
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=U:\WWW\acc\300\upexcel300.xls',Sheet1$)
help together
Goto to the first page of this thread and read the first post for proper syntax
Madhivanan
Failing to plan is Planning to fail |
|
|
|
lsharathkumar
Starting Member
India
1 Posts |
Posted - 04/22/2010 : 17:09:15
|
Hi Guys,
I have a doubt. My SQL server is in one machine, and I am building a desktop application using Java Swing and am connecting to SQL Server using jdbc.
My excel file will be in my local machine. How do I give the file path such that the data from the excel file in my local machine is imported by the sql server db in the remote system.
Sharath |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 04/27/2010 : 09:18:20
|
quote:
Originally posted by lsharathkumar
Hi Guys,
I have a doubt. My SQL server is in one machine, and I am building a desktop application using Java Swing and am connecting to SQL Server using jdbc.
My excel file will be in my local machine. How do I give the file path such that the data from the excel file in my local machine is imported by the sql server db in the remote system.
Sharath
Use UNC method
\\Your_system_name\Drive_name\folder_name\file_name
Note that the folder should be shared so that Server will have access to it
Madhivanan
Failing to plan is Planning to fail |
|
|
|
thenappann
Starting Member
4 Posts |
Posted - 05/12/2010 : 02:42:51
|
Hi Madhivanan,
I have used your type 5 To export data to new EXCEL file with heading(column names), create the following procedure. It works like a charm. I had a few doubts though
1) When ever there are double quotes i.e " in the data fields the export to excel goes crazy, it always seems to be eating up some rows, if i delete the " physically from my sql table and run the export again, it works. Pls let me know if there is a work around for this.
2) What should i do for Excel 2007 files if i give the extension as .xlsx i am not able to open it with excel 2007. i am asking this because i have few files that have about 80K rows. I would like to use 2007 instead of 2003 as there is a 65....K limit.
I have chosen 5 To export data to new EXCEL file with heading(column names), create the following procedure method because i am dynamically creating the file names and the number or records in them.
Thanks,
TN |
|
|
|
thenappann
Starting Member
4 Posts |
Posted - 05/12/2010 : 14:42:11
|
Hi All,
Just to give a little more explanation on what i am trying to do
I am trying to export a SQL Server Table to Excel file in the sizes 1)All, 2)1000, 3)10000. I have to dynamically create the excel file names as well as the number of rows that are to be exported to each file.
The below stored procedure works fine when there is no double quotes in the data (the sql server table) and works fine for any thing below 65K rows.
Pls let me know if there is a solution for these 2 scenarios
USE [FDB]
GO
/****** Object: StoredProcedure [dbo].[proc_generate_excel_with_columns] Script Date: 05/11/2010 13:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_generate_excel_with_columns]
(
@db_name varchar(100),
@table_name varchar(100),
@real_file_name varchar(100),
@file_len varchar(100)
)
as
--Generate column names as a recordset
declare @columns nvarchar(2000), @sql nvarchar(2000), @data_file nvarchar(100),
@data_def nvarchar(100), @counter_low nvarchar(100), @counter_high nvarchar(100),
@count_all INT,@file_name_cnt int,@file_name varchar(100),@total_runs int,@total_runs_flag char(1),
@current_run int,@irregular_cnt_flag char(1)
set @total_runs_flag = 'N'
set @irregular_cnt_flag = 'N'
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--set @count_all='select count(*) from '+@db_name+'..'+@table_name
--exec(@count_all)
SET @sql = N'SELECT @count_all = COUNT(*) FROM [' + @db_name + ']..[' + @table_name + ']'
EXEC sp_executesql
@query = @sql,
@params = N'@count_all INT OUTPUT',
@count_all = @count_all OUTPUT
if @file_len ='All' or (@file_len ='1000' and @count_all < 1000) or (@file_len ='10000' and @count_all < 10000)
begin
set @counter_low = '1'
set @counter_high = @count_all
set @total_runs = 1
set @total_runs_flag = 'Y'
set @file_name = @real_file_name
end
if @total_runs_flag = 'N'
begin
if (@count_all%cast(@file_len as int) = 0)
begin
set @total_runs = @count_all/cast(@file_len as int)
end
if (@count_all%cast(@file_len as int) != 0)
begin
set @total_runs = (@count_all/cast(@file_len as int))+1
set @irregular_cnt_flag = 'Y'
end
end
set @file_name_cnt = 1
set @current_run = 0
while (@current_run<@total_runs)
begin
if @total_runs_flag = 'N'
begin
if @current_run = 0
begin
set @counter_low = 1
set @counter_high = cast(@file_len as int)
end
if @current_run != 0 and @current_run != (@total_runs - 1)
begin
set @counter_low = (cast(@file_len as int) * @current_run) + 1
set @counter_high = cast(@file_len as int) * (@current_run + 1)
end
if @current_run = (@total_runs - 1) and @irregular_cnt_flag = 'N'
begin
set @counter_low = (cast(@file_len as int) * @current_run) + 1
set @counter_high = cast(@file_len as int) * (@current_run + 1)
end
if @current_run = (@total_runs - 1) and @irregular_cnt_flag = 'Y'
begin
set @counter_low = (cast(@file_len as int) * @current_run)+1
set @counter_high = @count_all
end
set @file_name = replace(@real_file_name,'.','_'+cast(@file_name_cnt as varchar(15))+'.')
end
--set @file_name = replace(@real_file_name,'.','_'+cast(@file_name_cnt as varchar(15))+'.')
set @data_def='select * from '+@db_name+'..'+@table_name+' where Sno >= '+@counter_low +' and Sno <='+@counter_high
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file1.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
exec(@sql)
--Generate data in the dummy file
--set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
set @sql='exec master..xp_cmdshell ''bcp "'+@data_def+'" queryout "'+@data_file+'" -c -T'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)
set @file_name_cnt = @file_name_cnt + 1
set @current_run = @current_run + 1
end |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 05/13/2010 : 10:17:08
|
1 To remove double quotes, in the query use
select replace(col,'"','')
2 If the target system has EXCEL version 2007, there wont be nor problem with the number of rows being exported
Madhivanan
Failing to plan is Planning to fail |
|
|
|
thenappann
Starting Member
4 Posts |
Posted - 05/14/2010 : 15:25:22
|
Hi Madhivanan,
Thanks a lot for your reply, had a few more doubts
1) you suggested to use the replace function for double quotes. As of now i require the double quotes to be exported as it appears on the sql server tables. is there any other work around
2) The file name that i use to export if excel 2003 is abcd.xls and it gives the following message but i am still able to open the file
The file you are trying to open, 'abcd.xls', is in a diffrent format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? Yes , No , Help
but when i specify the output file format as abcd.xlsx i receive the following message and i am not able to open the xlsx file. I have excel 2007 installed on my pc, pls let me know
Excel cannot open the file 'abcd.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file
Thanks,
TN |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 06/08/2010 : 02:51:02
|
quote:
Originally posted by thenappann
Hi Madhivanan,
Thanks a lot for your reply, had a few more doubts
1) you suggested to use the replace function for double quotes. As of now i require the double quotes to be exported as it appears on the sql server tables. is there any other work around
2) The file name that i use to export if excel 2003 is abcd.xls and it gives the following message but i am still able to open the file
The file you are trying to open, 'abcd.xls', is in a diffrent format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? Yes , No , Help
but when i specify the output file format as abcd.xlsx i receive the following message and i am not able to open the xlsx file. I have excel 2007 installed on my pc, pls let me know
Excel cannot open the file 'abcd.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file
Thanks,
TN
Have you found a solution?
1 Do you want to export data with double quotes?
2 Have you tried using 'abcd.xls'? I think xlsx extension can be used in version 2007 onwards
Madhivanan
Failing to plan is Planning to fail |
|
|
|
seoulaja
Starting Member
Indonesia
4 Posts |
Posted - 06/15/2010 : 03:40:18
|
Hello,
I tried this script in my server machine :
SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls;HDR=YES;IMEX=1', [Sheet1$]) AS MyTable
I got this error message :
quote:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
I tried another script :
SELECT *
FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls', 'SELECT * FROM [Sheet1$]')
i got this error message :
quote:
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Failure creating file.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
but, when I tried on my local machine, those scripts works fine.
then I tried this script :
DECLARE @exists INT
DECLARE @File VARCHAR(200)
SET @File = '\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls'
EXEC master..xp_fileexist @File, @exists output
SELECT @Exists
the result is : 1 (True)
but, when I tried on my server machine for the same script, the result is : 0 (False)
So I can't access my excel file in my local machine from my server machine.
I'm pretty sure my local and server machine in same network.
can you help me?
Best Regards,
Seoul Nainggolan
"Try not to become a man of success but a man of value."
-- Albert Einstein
|
|
|
|
sadhat
Starting Member
Malaysia
1 Posts |
Posted - 06/16/2010 : 23:52:12
|
Hey!
I have and excel sheet which contains data that needs to be imported into database (SQL) . But this data should be inserted into four different table depending upon the header.
So I would really appreciate if any one can help me in sorting out this.
Feel free to mail me. saddu.hameed@gmail.com
Hope i find a solutions from you soon.
Thank You!
Sadhat Hameed Shaik |
|
|
|
seoulaja
Starting Member
Indonesia
4 Posts |
Posted - 06/17/2010 : 06:33:07
|
what do you mean with 'header' ?
it means 'column' or 'sheet'
this is my solution to insert data from excel to sql.
this code inserts only one column to one table.
you can modify it as you needed...
CREATE PROCEDURE ExcelToSql
@FileName AS VARCHAR(1000),
@Sheet AS VARCHAR(20)
AS
BEGIN
DECLARE @Query AS VARCHAR(4000)
SET @Query ='
INSERT yourTable
SELECT CAST(Column1 AS VARCHAR(100))
FROM
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + @FileName + ';HDR=YES;IMEX=1'', ' + '[' + @Sheet + '$]) AS MyTable'
--PRINT @Query
EXEC (@Query)
END
---
quote:
Originally posted by sadhat
Hey!
I have and excel sheet which contains data that needs to be imported into database (SQL) . But this data should be inserted into four different table depending upon the header.
So I would really appreciate if any one can help me in sorting out this.
Feel free to mail me. saddu.hameed@gmail.com
Hope i find a solutions from you soon.
Thank You!
Sadhat Hameed Shaik
Best Regards,
Seoul Nainggolan
"Try not to become a man of success but a man of value."
-- Albert Einstein |
Edited by - seoulaja on 06/17/2010 06:38:33 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 06/21/2010 : 09:06:44
|
quote:
Originally posted by seoulaja
Hello,
I tried this script in my server machine :
SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls;HDR=YES;IMEX=1', [Sheet1$]) AS MyTable
I got this error message :
quote:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
I tried another script :
SELECT *
FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls', 'SELECT * FROM [Sheet1$]')
i got this error message :
quote:
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Failure creating file.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
but, when I tried on my local machine, those scripts works fine.
then I tried this script :
DECLARE @exists INT
DECLARE @File VARCHAR(200)
SET @File = '\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls'
EXEC master..xp_fileexist @File, @exists output
SELECT @Exists
the result is : 1 (True)
but, when I tried on my server machine for the same script, the result is : 0 (False)
So I can't access my excel file in my local machine from my server machine.
I'm pretty sure my local and server machine in same network.
can you help me?
Best Regards,
Seoul Nainggolan
"Try not to become a man of success but a man of value."
-- Albert Einstein
They may be in the same network. But make sure that the Server has read and write permission on that file
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 06/21/2010 : 09:07:40
|
quote:
Originally posted by sadhat
Hey!
I have and excel sheet which contains data that needs to be imported into database (SQL) . But this data should be inserted into four different table depending upon the header.
So I would really appreciate if any one can help me in sorting out this.
Feel free to mail me. saddu.hameed@gmail.com
Hope i find a solutions from you soon.
Thank You!
Sadhat Hameed Shaik
You can use column names when you use OPENROWSET function
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Topic |
|
|
|
Export to Excel