SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/07/2010 :  10:30:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 04/08/2010 :  03:05:59  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/08/2010 :  05:13:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 04/08/2010 :  05:30:41  Show Profile  Reply with Quote
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
Go to Top of Page

kashyapsid
Yak Posting Veteran

India
78 Posts

Posted - 04/08/2010 :  06:34:31  Show Profile  Visit kashyapsid's Homepage  Reply with Quote
this works

KaShYaP
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 04/09/2010 :  06:47:01  Show Profile  Reply with Quote
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
Go to Top of Page

abartoch
Starting Member

Thailand
1 Posts

Posted - 04/20/2010 :  23:21:20  Show Profile  Reply with Quote

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/21/2010 :  02:47:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

lsharathkumar
Starting Member

India
1 Posts

Posted - 04/22/2010 :  17:09:15  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/27/2010 :  09:18:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

thenappann
Starting Member

4 Posts

Posted - 05/12/2010 :  02:42:51  Show Profile  Reply with Quote
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
Go to Top of Page

thenappann
Starting Member

4 Posts

Posted - 05/12/2010 :  14:42:11  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/13/2010 :  10:17:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

thenappann
Starting Member

4 Posts

Posted - 05/14/2010 :  15:25:22  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/08/2010 :  02:51:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

seoulaja
Starting Member

Indonesia
4 Posts

Posted - 06/15/2010 :  03:40:18  Show Profile  Reply with Quote
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
Go to Top of Page

sadhat
Starting Member

Malaysia
1 Posts

Posted - 06/16/2010 :  23:52:12  Show Profile  Reply with Quote
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
Go to Top of Page

seoulaja
Starting Member

Indonesia
4 Posts

Posted - 06/17/2010 :  06:33:07  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/21/2010 :  09:06:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/21/2010 :  09:07:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000