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.

---

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

---

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';"

---

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

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.

---

quote:

---

Originally posted by karthickbabu

Madhi

Thank you very much.

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

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

---

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

---

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

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?

---

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

---