Reading pasword protected excel file

Tue, 09 Feb 2016 05:46:32 GMT

Hi All,

i have a requirement to read password protected excel file whose password will be same always.

I had gone through the various post on qlikview community for thi and solcution is provided is that to create ODBC connection and read the file and that excel file is opned on the server.But when it is password protected file means that file is having sensitive data so its not supposed to be opened.So i want a solution to read the file and password we wil put in qlikview backend.

Thanks..

Re: Reading pasword protected excel file

Wed, 10 Feb 2016 06:19:13 GMT

Please suggest something

Re: Reading pasword protected excel file

HirisH_V7 — Wed, 10 Feb 2016 11:09:16 GMT

Hi,

Check this,

How to open Password protected Excel file in qlikview?

Re: Reading pasword protected excel file

sujeetsingh — Wed, 10 Feb 2016 11:14:37 GMT

load a Password protected Excel File to QlikView

Re: Reading pasword protected excel file

avinashelite — Wed, 10 Feb 2016 11:15:15 GMT

Did you check this:

load a Password protected Excel File to QlikView

Re: Reading pasword protected excel file

Mon, 15 Feb 2016 06:48:01 GMT

I have gone thorugh all the links related with the above topic.But i want a way to read a file without opening an excel file in server

Re: Reading pasword protected excel file

HirisH_V7 — Mon, 15 Feb 2016 17:33:51 GMT

Hi,

Is there any data base for that Server Means you can export it as a excel .By running excel export services .

Whats your database? i think it may be SQL means,

1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable




2 Export data from Excel to new SQL Server table
select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')




3 Export data from Excel to existing SQL Server table
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')




4 If you dont want to create an EXCEL file in advance and want to export data to it, use


EXEC sp_makewebtask 
  @outputfile = 'd:\testing.xls', 
  @query = 'Select * from Database_name..SQLServerTable', 
  @colheaders =1, 
  @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)




5 To export data to new EXCEL file with heading(column names), create the following procedure


create procedure proc_generate_excel_with_columns
(
  @db_name varchar(100),
  @table_name varchar(100),
  @file_name varchar(100)
)
as


--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
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 '),',',',''''')


--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.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'''
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'''
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)


After creating the procedure, execute it by supplying database name, table name and file path


EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Please check,

HTH,

Hirish

topic Reading pasword protected excel file in QlikView

Reading pasword protected excel file

Re: Reading pasword protected excel file

Re: Reading pasword protected excel file

Re: Reading pasword protected excel file

Re: Reading pasword protected excel file

Re: Reading pasword protected excel file

Re: Reading pasword protected excel file