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

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/11/2008 :  04:24:31  Show Profile  Reply with Quote
Hi Madhi

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\Service Tax Details Table.xls'

It creates an Excel with the table column name

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\ServerName\Sharable\Service Tax Details Table.xls;',
'SELECT * FROM [Service Tax Details Table$]') select * from ServiceTaxDetails

While run the insert query it shows the following error

Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.


================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/11/2008 :  05:11:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by karthickbabu

Hi Madhi

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\Service Tax Details Table.xls'

It creates an Excel with the table column name

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\ServerName\Sharable\Service Tax Details Table.xls;',
'SELECT * FROM [Service Tax Details Table$]') select * from ServiceTaxDetails

While run the insert query it shows the following error

Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.


================================================

When you realize you've made a mistake, take immediate steps to correct it.



When you run the query, make sure the EXCEL file is closed

Madhivanan

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 11/11/2008 :  10:15:52  Show Profile  Reply with Quote
For *.xlsx files:

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & GetExcelFileName().ToString & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
Go to Top of Page

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/11/2008 :  23:50:01  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by karthickbabu

Hi Madhi

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\Service Tax Details Table.xls'

It creates an Excel with the table column name

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\ServerName\Sharable\Service Tax Details Table.xls;',
'SELECT * FROM [Service Tax Details Table$]') select * from ServiceTaxDetails

While run the insert query it shows the following error

Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.


================================================

When you realize you've made a mistake, take immediate steps to correct it.



When you run the query, make sure the EXCEL file is closed

Madhivanan

Failing to plan is Planning to fail




Yes, File is Closed. Excel file is in our server machine.
I run that query in my machine.

It works on My Machine when i run that same Query. It create excel and fill those data from that table.

================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/12/2008 :  02:29:32  Show Profile  Reply with Quote
Just now i notice,

I run the below queries :

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\Service Tax Details Table.xls'

It Creates Excel File with Column Name from that Table.
File Name : Service Tax Details Table.xls.

Then run the below query

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\ServerName\Sharable\Service Tax Details Table.xls;',
'SELECT * FROM [Service Tax Details Table$]') select * from ServiceTaxDetails

It shows an error - Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
But it create one file name "Service" without any extension, I check this file - it contain the data from that table instead of filling Service Tax Details Table.xls

Another Case - If i run the single query without any space in excel file

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\ServiceTaxDetailsTable.xls'

It creates excel and fill the data from that table. It works in my machine and server machine also.

================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2008 :  04:18:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by darkdusky

For *.xlsx files:

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & GetExcelFileName().ToString & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"


Thanks. It seems that you copied it from .NET code

More generally,


"Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=D:\testing.xls;" & _
            "Extended Properties='Excel 12.0 Xml;HDR=YES';"


Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2008 :  04:19:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by karthickbabu

Just now i notice,

I run the below queries :

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\Service Tax Details Table.xls'

It Creates Excel File with Column Name from that Table.
File Name : Service Tax Details Table.xls.

Then run the below query

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\ServerName\Sharable\Service Tax Details Table.xls;',
'SELECT * FROM [Service Tax Details Table$]') select * from ServiceTaxDetails

It shows an error - Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
But it create one file name "Service" without any extension, I check this file - it contain the data from that table instead of filling Service Tax Details Table.xls

Another Case - If i run the single query without any space in excel file

EXEC proc_generate_excel_with_columns 'MyDBName','ServiceTaxDetails','\\ServerName\Sharable\ServiceTaxDetailsTable.xls'

It creates excel and fill the data from that table. It works in my machine and server machine also.

================================================

When you realize you've made a mistake, take immediate steps to correct it.



Avoid having space in the file or sheet names
Use underscore instead

Instead of

Service Tax Details Table

Use

Service_Tax_Details_Table

Madhivanan

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

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/12/2008 :  04:26:29  Show Profile  Reply with Quote
Madhi

Thank you very much.

================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2008 :  08:19:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by karthickbabu

Madhi

Thank you very much.

================================================

When you realize you've made a mistake, take immediate steps to correct it.



You are welcome

Madhivanan

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

asafg
Starting Member

39 Posts

Posted - 11/17/2008 :  11:49:48  Show Profile  Reply with Quote
Hello

I need a VBA code that insert an excel row and/or column into a table in my DB
1. I think I need a VBA because I can't put excel on the server - I work on my local machine.
2. I do have a DNS definition on my PC (but I found examples for select and not for insert).

Thanks
Asaf
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/18/2008 :  04:40:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by asafg

Hello

I need a VBA code that insert an excel row and/or column into a table in my DB
1. I think I need a VBA because I can't put excel on the server - I work on my local machine.
2. I do have a DNS definition on my PC (but I found examples for select and not for insert).

Thanks
Asaf


1 You can use the UNC path like \\system_name\file_path_name
2 Show us the code

Madhivanan

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

asafg
Starting Member

39 Posts

Posted - 11/18/2008 :  06:48:46  Show Profile  Reply with Quote
dont have one.
All I want to do is copy a column/table or what ever from excel to SQLServer:

I found a code in the internet and after ajusting...:

Sub QueryWorkSheet()
    Dim rs As ADODB.Recordset
    Dim ConnectionString As String

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties=Excel 8.0;"

    Dim SQL As String
    Dim rngName As Name
    'verifing that the range name does not exist
    For Each rngName In ActiveWorkbook.Names
        If rngName = "rngExcelTable" Then
            ActiveWorkbook.Names("rngExcelTable").Delete
            Exit For
        End If
    Next
    ' Selecting the table area (including titles)
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ' Giving a name to the range
    ActiveWorkbook.Names.Add Name:="rngExcelTable", RefersToR1C1:="=data!R2C1:R" + Trim(Str(Selection.Rows.Count + 1)) + "C" + Trim(Str(Selection.Columns.Count)) 'strRNG
        
    SQL = "SELECT * FROM rngExcelTable;"
    Set rs = New ADODB.Recordset
    
    On Error GoTo Error
    rs.Open SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText
    
    ' Displaying the output
    Range("z10").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    Exit Sub
Error:
    Debug.Print Err.Description
    If (rs.State = ObjectStateEnum.adStateOpen) Then
        rs.Close
    End If
    
    Set rs = Nothing

End Sub


now I need a vba code


Pseudocode:
dim conn = ...somthing with sqlserver
open conn
insert into mySQLServerTable my rs ' from excel
Go to Top of Page

Tkoletsis
Starting Member

Greece
5 Posts

Posted - 11/19/2008 :  05:13:08  Show Profile  Reply with Quote
Hello
My problem has to do with import from excel and although i've read all the postings here, and other forums, and microsoft's pages, i have not taken a clear answer.

The message is:
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)".

The script is:

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
--SET @datasrc = 'c:\Book1.xls'
SET @datasrc = '\\semantics-main\Production\theo\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

EXEC sp_addlinkedsrvlogin 'XLTEST_sp', 'false', null, null, null

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\semantics-main\Production\theo\book1.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$

This script runs ok in another server and another machine with sql server 2000

IS IT SO DIFFICULT TO IMPORT AN EXCEL FILE TO AN SQL SCRIPT AT LAST?
Go to Top of Page

Tkoletsis
Starting Member

Greece
5 Posts

Posted - 11/19/2008 :  11:08:31  Show Profile  Reply with Quote
Ok the answer is that the excel file must be at the same volume where the sql server is. (If you save it in another volume you'll get these wonderful messages)
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 01/13/2009 :  12:49:50  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/15/2009 :  02:09:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 01/15/2009 02:10:57
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 01/15/2009 :  15:42:01  Show Profile  Reply with Quote
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?
Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 01/15/2009 :  15:42:53  Show Profile  Reply with Quote
forgot to add...the table is in sql server....
source is excel.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/15/2009 :  22:27:03  Show Profile  Reply with Quote
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

Go to Top of Page

dedbeat
Starting Member

9 Posts

Posted - 01/16/2009 :  11:27:56  Show Profile  Reply with Quote
im pretty sure too...but what is it any how??
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.16 seconds. Powered By: Snitz Forums 2000