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
22769 Posts

Posted - 03/16/2009 :  00:58:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dona

File is not open. dont know what the problem could. even made the temp environment variables to point to a location where write access is there
quote:
Originally posted by matty

If the file is open, close it and try. I had the same error, it worked when i closed the file.




Try this and replace excel and sheet names


SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\book1.xls',
'SELECT * FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/16/2009 :  02:37:35  Show Profile  Reply with Quote
Getting the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] External table is not in the expected format.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

quote:
Originally posted by madhivanan

quote:
Originally posted by dona

File is not open. dont know what the problem could. even made the temp environment variables to point to a location where write access is there
quote:
Originally posted by matty

If the file is open, close it and try. I had the same error, it worked when i closed the file.




Try this and replace excel and sheet names


SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\book1.xls',
'SELECT * FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

drani
Starting Member

India
1 Posts

Posted - 03/19/2009 :  00:01:20  Show Profile  Reply with Quote
how i can comple excel three sheets
Go to Top of Page

squatchman74
Starting Member

9 Posts

Posted - 03/20/2009 :  09:18:27  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by frtslgn

how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
A jan ---
B jim ---
C jon ---
D jen ---
E jun ---
F --- MRC
G --- BMW
H --- FRD
I --- RVR


but i want

1 2
--- ---
A jan mrc
B jim bmw
C jon frd
D jen rvr
e jun


Can you post your question as a new topic?


Madhivanan

Failing to plan is Planning to fail



Anyone have a answer for this post
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 03/26/2009 :  08:48:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by harlingtonthewizard

Hi,

I am trying to setup a process for exporting and importing excel data to a SQL 2005 DB Tables. I have been successful with procedure 5 to export however I cannot get the import to work. I have been searching for answers and getting very confused.

I have set 'ad hoc queries' = 1

With this:

Select *
FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=C:\export\users_20090313_110544.xls', 'SELECT * FROM [users_20090313_110544$] ' )

I get:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] External table is not in the expected format.".
Msg 7303, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 21
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".'

With this:

Insert into UserDetails Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\export\users_20090313_110544.xls;HDR=YES',
'SELECT * FROM [users_20090313_110544$]')

I get:

Msg 7399, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 30
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


As specified on other replies, make sure

1 The file exists in Server's directory
2 The file is closed during the execution


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 03/26/2009 :  08:50:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by squatchman74

quote:
Originally posted by madhivanan

quote:
Originally posted by frtslgn

how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
A jan ---
B jim ---
C jon ---
D jen ---
E jun ---
F --- MRC
G --- BMW
H --- FRD
I --- RVR


but i want

1 2
--- ---
A jan mrc
B jim bmw
C jon frd
D jen rvr
e jun


Can you post your question as a new topic?


Madhivanan

Failing to plan is Planning to fail



Anyone have a answer for this post


Answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121646

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 03/26/2009 :  09:08:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dona

Getting the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] External table is not in the expected format.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

quote:
Originally posted by madhivanan

quote:
Originally posted by dona

File is not open. dont know what the problem could. even made the temp environment variables to point to a location where write access is there
quote:
Originally posted by matty

If the file is open, close it and try. I had the same error, it worked when i closed the file.




Try this and replace excel and sheet names


SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\book1.xls',
'SELECT * FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail




As an alternate, trying creating a linked server to EXCEL and query it
Read about sp_addlinkedserver in SQL Server help file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Matt008
Starting Member

1 Posts

Posted - 04/14/2009 :  00:35:18  Show Profile  Reply with Quote
Hi guys,

I've been reading through the forum and I am still having trouble with the SQL export to Excel. I am trying to run this query through a ASP.NET method, and here is my code below:

"CREATE procedure proc_generate_excel_with_columns(" +
                                        "@db_name	varchar(100),@table_name varchar(100),"+	
	                                    "@file_name	varchar(100)) AS " +
                                        "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 " +
                                        "Set [@file_name] = [@file_name] + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'" +
                                        "select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''') "+
                                        "select @data_file=substring(@file_name,1,len(@file_name)-charindex"+
                                        "('\\',reverse(@file_name)))+'\\data_file.xls' " +
                                        "set @sql='exec master..xpcmdshell ''bcp \" select * from (select '+@columns+')"+
                                        "as t\" queryout \"'+@file_name+'\" -c'''exec(@sql)"+
                                        "set @sql='exec master..xpcmdshell ''bcp \"select * from '+@db_name+'..'+@table_name+'\""+
                                       " queryout \"+\"@data_file\"+\" -c'''exec(@sql) "+
                                        "set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> \"'+@file_name+'\"'''exec(@sql) ";
                                        "set @sql= 'exec master..xpcmdshell ''del '+@data_file+''''exec(@sql) "+
                                        "EXEC proc_generate_excel_with_columns 'db', 'table','your file path'";


I'm storing this string within a variable and then pass it later into the SQLStatement.CommandText parameter. I get an error saying that there is an open statement somewhere near
" queryout \"+\"@data_file\"+\" -c'''exec(@sql) "+
I'm not sure what the problem is, but this has been incredibly frustrating. Thanks for the help in advance!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 04/20/2009 :  10:15:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Matt008

Hi guys,

I've been reading through the forum and I am still having trouble with the SQL export to Excel. I am trying to run this query through a ASP.NET method, and here is my code below:

"CREATE procedure proc_generate_excel_with_columns(" +
                                        "@db_name	varchar(100),@table_name varchar(100),"+	
	                                    "@file_name	varchar(100)) AS " +
                                        "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 " +
                                        "Set [@file_name] = [@file_name] + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'" +
                                        "select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''') "+
                                        "select @data_file=substring(@file_name,1,len(@file_name)-charindex"+
                                        "('\\',reverse(@file_name)))+'\\data_file.xls' " +
                                        "set @sql='exec master..xpcmdshell ''bcp \" select * from (select '+@columns+')"+
                                        "as t\" queryout \"'+@file_name+'\" -c'''exec(@sql)"+
                                        "set @sql='exec master..xpcmdshell ''bcp \"select * from '+@db_name+'..'+@table_name+'\""+
                                       " queryout \"+\"@data_file\"+\" -c'''exec(@sql) "+
                                        "set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> \"'+@file_name+'\"'''exec(@sql) ";
                                        "set @sql= 'exec master..xpcmdshell ''del '+@data_file+''''exec(@sql) "+
                                        "EXEC proc_generate_excel_with_columns 'db', 'table','your file path'";


I'm storing this string within a variable and then pass it later into the SQLStatement.CommandText parameter. I get an error saying that there is an open statement somewhere near
" queryout \"+\"@data_file\"+\" -c'''exec(@sql) "+
I'm not sure what the problem is, but this has been incredibly frustrating. Thanks for the help in advance!


Why are creating the procedure via ASP.NET?
Also before executing the dynamically built string, print it and see if it is syntaxically correct

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

BBY
Starting Member

1 Posts

Posted - 04/21/2009 :  07:53:19  Show Profile  Reply with Quote
Hi,

I am using BCP to export to an excel file from SQL and this works perfectly. The trouble is that my table has over 200,000 rows of data so i need to split it into multiple sheets. Is there anyway of doing this.

I cannot use the OPENROWSET command. So if anyone knows how to solve this using the BCP command it would be appreciated.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 04/22/2009 :  07:40:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by BBY

Hi,

I am using BCP to export to an excel file from SQL and this works perfectly. The trouble is that my table has over 200,000 rows of data so i need to split it into multiple sheets. Is there anyway of doing this.

I cannot use the OPENROWSET command. So if anyone knows how to solve this using the BCP command it would be appreciated.


Search for Pagination+SQL Server and try to apply the logic in bcp

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

micoypogi
Starting Member

Philippines
6 Posts

Posted - 04/30/2009 :  02:33:23  Show Profile  Reply with Quote
Hello.

Below post helped me a lot to get myself acquainted with exporting data from SQL server to Excel.

However, I'm having problems with the the 5th item. I'm not able to include the column names to the data to be transferred to Excel file.

Hoping to hear any suggestions from your part.

Thanks.

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

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'

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

micoypogi
Starting Member

Philippines
6 Posts

Posted - 04/30/2009 :  03:24:44  Show Profile  Reply with Quote
done with below post. have only to change database.

One thing though, can anyone provide me some suggestions on how to consolidate different exported tables to a single excel file; only separated through excel sheet.

thank you very much.

quote:
Originally posted by micoypogi

Hello.

Below post helped me a lot to get myself acquainted with exporting data from SQL server to Excel.

However, I'm having problems with the the 5th item. I'm not able to include the column names to the data to be transferred to Excel file.

Hoping to hear any suggestions from your part.

Thanks.

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

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'

Madhivanan

Failing to plan is Planning to fail



Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/04/2009 :  03:02:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by micoypogi

done with below post. have only to change database.

One thing though, can anyone provide me some suggestions on how to consolidate different exported tables to a single excel file; only separated through excel sheet.

thank you very much.

quote:
Originally posted by micoypogi

Hello.

Below post helped me a lot to get myself acquainted with exporting data from SQL server to Excel.

However, I'm having problems with the the 5th item. I'm not able to include the column names to the data to be transferred to Excel file.

Hoping to hear any suggestions from your part.

Thanks.

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

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'

Madhivanan

Failing to plan is Planning to fail






You need to write seperate query for each table and seperate sheetname to send data to excel sheet

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

micoypogi
Starting Member

Philippines
6 Posts

Posted - 05/04/2009 :  21:39:15  Show Profile  Reply with Quote
Thanks.

I was able to do it using your first sample query. Clarification though, after running the script, it provided me with an excel file with the exported data as well as its columns formatted as "General". Will there be any possibilities that the *excel file inherits the column types of its SQL table counterparts (i.e. column1 in SQL Server with data type Date Time be exported to Excel file also with data type Date Time)

*excel file will only be generated during the script's execution.

btw, thanks for the response.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/07/2009 :  03:37:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by micoypogi

Thanks.

I was able to do it using your first sample query. Clarification though, after running the script, it provided me with an excel file with the exported data as well as its columns formatted as "General". Will there be any possibilities that the *excel file inherits the column types of its SQL table counterparts (i.e. column1 in SQL Server with data type Date Time be exported to Excel file also with data type Date Time)

*excel file will only be generated during the script's execution.

btw, thanks for the response.


Becuase you will have column names in the first row which are all of strings, the format becomes General.
I dont think there is way to do exactly what you want. But better you ask it in a new thread so that who know the answer may reply

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 05/07/2009 03:41:07
Go to Top of Page

watherton
Starting Member

United Kingdom
6 Posts

Posted - 05/18/2009 :  11:39:26  Show Profile  Reply with Quote
Hi, I was wondering if someone could lend me a hand. I am sucessfully creating an xsl sheet using the above code, but instead of the content being written in the first line, second line so on and so forth, my content starts at line 179 etc.

any reason why this is?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/19/2009 :  01:52:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by watherton

Hi, I was wondering if someone could lend me a hand. I am sucessfully creating an xsl sheet using the above code, but instead of the content being written in the first line, second line so on and so forth, my content starts at line 179 etc.

any reason why this is?


It means you deleted the content upto 178 lines. Create a new sheet and try again

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jaythree03
Starting Member

Philippines
1 Posts

Posted - 05/25/2009 :  04:55:24  Show Profile  Send jaythree03 a Yahoo! Message  Reply with Quote
Hi,

I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I'm working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\...analysis.xls', [data$])

you can contact me here: jaythree03@gmail.com
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/26/2009 :  07:39:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jaythree03

Hi,

I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I'm working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\...analysis.xls', [data$])

you can contact me here: jaythree03@gmail.com


Make sure the file exists in Server's directory. Otherwise you should use UNC path like '\\System_name\D$\....

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.19 seconds. Powered By: Snitz Forums 2000