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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/16/2009 :  13:22:13  Show Profile  Reply with Quote
quote:
Originally posted by dedbeat

hey! thanks for the response.
do you really have to copy the data to a staging table? i have to do this without... is there any other functionality other than the excel sheet having a hardcoded incrementing number in a particular column?


i think you can try using OPENROWSET provided you can identify the sort order which enables you to seperate the first 10 rows
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/16/2009 :  17:37:59  Show Profile  Reply with Quote
Here it is:
http://weblogs.asp.net/mikebosch/archive/2007/10/26/ssis-skipping-rows-and-stripping-subtotals.aspx
Go to Top of Page

Humate
Posting Yak Master

101 Posts

Posted - 01/16/2009 :  20:22:35  Show Profile  Reply with Quote
I have the following error when trying to insert data from SQL table into an Excel file:

Insert Error: Column name or number of supplied values does not match table definition.

I made a linked server with this query:

insert into OPENQUERY(EXCELTEST, 'SELECT * FROM [Sheet1$]') select * from employees

Can anyone help with fixing the error?

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/16/2009 :  23:53:03  Show Profile  Reply with Quote
Isn't it possible to use Export/Import wizard instead? Also Table definitions on source and destination is similar.
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

India
408 Posts

Posted - 01/17/2009 :  07:54:13  Show Profile  Send ashishashish a Yahoo! Message  Reply with Quote
i got the errors when i tried this,,,,,

Queries as Below..

EXEC proc_generate_excel_with_columns 'ashish','audittrail','D:\testing.xls'

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [testing$]') select * from audittrail
Error.....

Msg 7399, Level 16, State 1, Line 2
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, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


If i did any thing wrong deni donn know m very new to SQl...
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/17/2009 :  11:06:39  Show Profile  Reply with Quote


Corrected one:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=No', 
'SELECT * FROM [testing$]') select * from audittrail


Edited by - sodeep on 01/17/2009 11:07:21
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 01/17/2009 :  12:23:56  Show Profile  Reply with Quote
i was able to solve this by specifying a range...

humate. when you use select * ensure the source and destination have the same no of columns. avoid using the * if possible and specify column names. it will also make mapping easier.
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

India
408 Posts

Posted - 01/19/2009 :  04:45:27  Show Profile  Send ashishashish a Yahoo! Message  Reply with Quote
quote:
Originally posted by sodeep



Corrected one:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=No', 
'SELECT * FROM [testing$]') select * from audittrail





Thanks For Replying Sir,,,,
But Still I have The same error .........
for this,,,,,

Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Ohkk ...Thanks,,,If u Have Some Other Way to Export Data from Sql to Excel File Through T-SQL then please let me know i also read two more
Refrences for this,,but still i cant figure it out that where i m Wrong,,,

So let me know if there is any other way?
Thanks...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/19/2009 :  07:24:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by ashishashish

quote:
Originally posted by sodeep



Corrected one:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=No', 
'SELECT * FROM [testing$]') select * from audittrail





Thanks For Replying Sir,,,,
But Still I have The same error .........
for this,,,,,

Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Ohkk ...Thanks,,,If u Have Some Other Way to Export Data from Sql to Excel File Through T-SQL then please let me know i also read two more
Refrences for this,,but still i cant figure it out that where i m Wrong,,,

So let me know if there is any other way?
Thanks...


1 Make sure the EXCEL exists in the SERVER's directory and not in the CLIENT directory
2 Make sure the EXCEL is closed when you execute the code
3 Also try
INSERT INTO OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\testing.xls',
'SELECT * FROM [testing$]')
SELECT * FROM AUDITTRAIL


Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/19/2009 :  07:27:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Humate

I have the following error when trying to insert data from SQL table into an Excel file:

Insert Error: Column name or number of supplied values does not match table definition.

I made a linked server with this query:

insert into OPENQUERY(EXCELTEST, 'SELECT * FROM [Sheet1$]') select * from employees

Can anyone help with fixing the error?

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.



Also make use of OPENROWSET which you dont need a Linked Server

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 01/19/2009 :  08:58:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sodeep

I think there is functionality to skip first 10 rows in SSIS .

quote:
Originally posted by madhivanan

quote:
Originally posted by dedbeat

hey guys...
im stuck in a bit of a problem.

so theres an excel file that i have to load into an existing table.
i need to skip the first 10 rows since its information that doesnt pertain to me.

the sheet has data in about 20 columns...they dont have column names or header info...i need the data from the first 3 columns, map them into the right columns in the destination table and insert the data. i need to be able to do this to many files. the horizontal format remains the same but the no. of rows differ so i cant really use a range. can anyone help me write a query with some kind of openrowset function to accomplish this?


1
Copy the data to staging table that has identity column
select identity(int,1,1) as sno,* staging_table from Openrowset(....)

Now skip 10 rows by

select columns from staging_table
where sno>10

2
What do you mean destination table?
Is it a table in Server or in Excel?


Madhivanan

Failing to plan is Planning to fail




I forgot to specify that you can use named range
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=3#232434

Madhivanan

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

bpage
Starting Member

1 Posts

Posted - 02/03/2009 :  12:56:36  Show Profile  Reply with Quote
quote:
Originally posted by ashishashish

quote:
Originally posted by sodeep



Corrected one:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=No', 
'SELECT * FROM [testing$]') select * from audittrail





Thanks For Replying Sir,,,,
But Still I have The same error .........
for this,,,,,

Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Ohkk ...Thanks,,,If u Have Some Other Way to Export Data from Sql to Excel File Through T-SQL then please let me know i also read two more
Refrences for this,,but still i cant figure it out that where i m Wrong,,,

So let me know if there is any other way?
Thanks...



I bet if you restart the SQL Server process it will work... at least for a little while. This is a known issue with SQL Server 2005, that MS seems to have only just acknowledged.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284113&wa=wsignin1.0
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 02/09/2009 :  09:06:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by bpage

quote:
Originally posted by ashishashish

quote:
Originally posted by sodeep



Corrected one:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=No', 
'SELECT * FROM [testing$]') select * from audittrail





Thanks For Replying Sir,,,,
But Still I have The same error .........
for this,,,,,

Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Ohkk ...Thanks,,,If u Have Some Other Way to Export Data from Sql to Excel File Through T-SQL then please let me know i also read two more
Refrences for this,,but still i cant figure it out that where i m Wrong,,,

So let me know if there is any other way?
Thanks...



I bet if you restart the SQL Server process it will work... at least for a little while. This is a known issue with SQL Server 2005, that MS seems to have only just acknowledged.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284113&wa=wsignin1.0


Thats interesting. My guess is that when the SERVER is restarted, the EXCEL file which might have opened earlier is forced to be closed. Whenever you get an error, make sure that EXCEL file is not opened or not used by any other application

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 02/09/2009 09:09:00
Go to Top of Page

Keirmcc
Starting Member

1 Posts

Posted - 02/10/2009 :  06:03:52  Show Profile  Reply with Quote
Hi all!

I have read through this thread and i may be barking up the wrong tree on this one but it seems to be the most relevant. I have been working with the query builder in SAP so have little experience when it comes to using the sql server module although i have used it a bit. Basically i am trying to firstly export the following query to an excel sheet and secondly have it automatically emailed each day. If anyone can provide some help for further readin that would be great.

SELECT T0.[U_IIS_DDDT], T0.[U_iis_plan], t0.U_AVL_offered,t0.u_avl_na1,T0.U_AVL_na2, dbo.fhGetWorkingDaysWithoutWeekEnds(T0.createdate, T0.[closeDate]) AS DaysToClose, T0.U_IIS_INSCODES, T0.subject, T0.U_IIS_INADR, T0.custmrName FROM [dbo].[OSCL] T0 LEFT OUTER JOIN [dbo].[OHEM] T1 ON T0.technician = T1.empID INNER JOIN OCRD T2 ON T0.U_iis_job = T2.CardCode INNER JOIN OUSR T3 ON T0.assignee = T3.INTERNAL_K INNER JOIN OSCS T4 ON T0.status = T4.statusID where LEFT(T2.ZipCode, 2) IN ('BB','BL','CH','CW','FY','IM','L1','L2','L3','L4','L5','L6','L7','L8','L9','LA','LL','M1','M2','M3','M4','M5','M6','M7','M8','M9','OL','PR','SK','ST','WA','WN') order by T0.U_IIS_DDDT
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/10/2009 :  20:16:18  Show Profile  Reply with Quote
You can use Import/Export Wizard or SSIS and use Send Mail Task email it daily using SQL Jobs.
Go to Top of Page

grvs
Starting Member

1 Posts

Posted - 02/17/2009 :  06:02:35  Show Profile  Reply with Quote
Hi
I am pretty new in SQL and referred this forum first time. A very good learning experience indeed.

I wanted to use that "creating a procedure" to export data.
Now I have no idea about bcp and exec, still I copy pasted the code and I have some error.
I need to give -u or -T option in following line.


set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

can you help me on that ? I mean how to use that? How to tell SQL server that its a trusted connection (actually local). What would be my username? (funny but so new I am)
thanks

(what would be the username
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 02/18/2009 :  08:23:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by grvs

Hi
I am pretty new in SQL and referred this forum first time. A very good learning experience indeed.

I wanted to use that "creating a procedure" to export data.
Now I have no idea about bcp and exec, still I copy pasted the code and I have some error.
I need to give -u or -T option in following line.


set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

can you help me on that ? I mean how to use that? How to tell SQL server that its a trusted connection (actually local). What would be my username? (funny but so new I am)
thanks

(what would be the username



instead of exec(@sql), use print @sql and post the result here


Madhivanan

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

korssane
Posting Yak Master

104 Posts

Posted - 02/19/2009 :  09:06:48  Show Profile  Reply with Quote
Hi Guys,
I am new in SQL and all what i have seen till is how to export SQL data to Excel.
Actually i am using Linked server to import Excel data into SQL 2005 data table. The problem is : i do not know how to update the SQL table automaticaly when the excel data changes..

Please, any help will be greaty appreciated.

Thanks
Go to Top of Page

korssane
Posting Yak Master

104 Posts

Posted - 02/19/2009 :  09:58:59  Show Profile  Reply with Quote
Sorry guys, I completely forgot to check the previous 18 pages in this topic which very instructive.
My idea basically is to build reports and charts. For this i need to have my sql table uptodates. Some of these tables data need to be imported from Excel Sheet.
i am a new in SQL and i was able the 1st time to use linked server to import data from Excel Sheet.
The thing i want to know what is the next step to have the data updated automatiocally from excel file to my SQL data table.
Thanks Guys.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 03/02/2009 :  00:49:50  Show Profile  Reply with Quote
I have attempted to make some changes to procedure 5 as detailed within the first post by madhivanan. This is mainly a learning exercise for me to use and in this case and I am running into a little trouble. I am trying to add the machinename as a variable and hence have added the following towards the top of the file:

--Set Machine Name
Declare @MachineName nvarchar (100)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '/XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

If I set as follows it works:

set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S butterfingers\XTRALIS_CYCLOPS'''

but if changed to this it fails:

set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "'+@MachineName+'"'''

When run it continues to execute and never returns.

Edited by - harlingtonthewizard on 03/02/2009 02:03:28
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