AdHoc Creation?

[JaedenRuiner]

I've tested several methods...(not completely, but enough to come here for help) and I was wondering if AdHoc (OPENROWSET) statements can be used to create.

For example: I've selected data FROM an OPENROWSET AdHoc query source, (which is an excel file by the way), and now I want to export certain rows into a NEW excel file. The question is, can I use the OPENROWSET or other such AdHoc query formats to Create the new Excel file.

Right now I'm going to start on the Interop Method of creating an Excel File and hopefully once the file is made I can "Insert Into" but so far, Create Table() and Select Into won't allow me to use the AdHoc for creation of the destination. (keep getting syntax error around OPENROWSET()).
I've tried as well to follow the ADO syntax:

select * into [Excel 8.0;Database=c:\db\mydb.xls].[Sheet1] from MyTable

but that gives me an error about the schema not existing (which it doesn't) or insufficient permissions to use it.

Basically, in the end, what i need is to create an Excel file and dump a series of records from the DB into it. However, I would find anything other than a SQL statement inordinately inefficient for the transfer. If I need to Interop Create the XL file first, that's fine (i'd like not to, but i understand if its necessary) but for the transfer of records into Sheet1 I really...really want to use SQL with a SqlCommand() execution as that is the most efficient for my purposes at the moment. To loop through the records and set them 1 by 1 with Interop would be too slow and very cumbersome.

Thanks

 

[cjard]

I do this by turning a datatable into an Excel XML stream. I have come C# code to do it, if you want it.. If you don't have C# then i can compile it to a dll for you

Code would look like:

ExcelableDataTable edt = new ExcelableDataTable();
someOleDataAdapter.Fill(edt);
edt.WriteExcelXmlFile("C:\\temp.xls");

 

[JaedenRuiner]

Hrm...

That woudl work. I already had an Interop wrapper class that was used to get sheetnames and column headers for this app, so i just added the create & set column headers functions and then the AdHoc works. I was just hoping to skip that step but my code looks about the same, except I didn't wrap it up with a datatable. Thanks though.

It is amusing, however, because everything I found online shows you how to do it with ADODB, but for some reason they eliminated the functionality into ADO.Net (unless the two technologies are mutually exclusive)
*shrug*

 

[cjard]

I'm not aware of the difference between ADO.NET and ADODB being so significant as to prohibit using an example in this form.


One thing I should point out: Using Interop to output your report requires a licensed copy of Excel on every machine that you plan to use your app on, and further it is not recommended for servers. Creating a file that Excel can load is subject to neither of these restrictions

 

[JaedenRuiner]

Good to know!

However, in the corporate office here, we are assured to have a licensed copy of Office so it isn't a concern now, but that will be in the future.

As for the adodb vs ado. I would agree they should be same thing. But, every instance / example of using a Datasource style connection to create an Excel file (via create table or select into) all request the ADODB 2.1 Library be added as a reference to your app, as well they create an ADODB.Connection instead of a SqlConnection. Now, SqlConnection may not be ADO I may have my acronym technologies incorrect, but I was hoping that anything I used to do this would not involve adding more references or more types. Everything I'm working on now uses the SqlClient{} namespace and i'd prefer to keep it that way.

As for the XML version of Excel importation, I will look into that for future as that may well be the most efficient all-around solution to this.

 

[cjard]

ADODB 2.1 Library be added as a reference to your app, as well they create an ADODB.Connection instead of a SqlConnection.

Mmm.. but I'd have hoped that the connection technology was reasonably independent of the drivers being used, and they are the things (in file based modes) that parse the statements.. In short, I'd have expected the same statement to work anywhere but it's interesting to note that you use SqlClient; afaiwa this is for SQLServer databases and is not intended for use with the Jet driver (which has excel connectivity)

SQLServer can connect and export to Excel:
SQL Server Forums - Export to Excel

Note that it is the server that is performing the export, not the client.. As such paths are relative to server filesystem, and the server may also be lacking the ability to conenct to an Excel file (though what lib affords this I don't know)
Note also that if the server cannot create an excel file, there is nothing stopping you (other than multi-user headaches) copying the file to the server in blank form, to a known location and having the server export into it

 

[JaedenRuiner]

*Chuckle*

Yea, that's the exact same site I found, and that's exactly what I'm using. But as you can see, all the exports going to the Excel file via adhoc queries, are using the "INSERT INTO" method, which means the destination table must already exist. That's what i was hoping to avoid was the actual Point of Creation. As I do it now:

      Public Function Create(ByVal filename As String, Optional ByVal sheet As String = "") As Boolean
         _save = True
         Try
            StartApp()
            _wb = _app.Workbooks.Add()
            While _wb.Worksheets.Count > 1
               DirectCast(_wb.Worksheets(_wb.Worksheets.Count), Excel.Worksheet).Delete()
            End While
            _wks = _wb.ActiveSheet
            If String.IsNullOrEmpty(sheet) Then
               sheet = "Sheet1"
            End If
            _wks.Name = sheet
            _wb.SaveAs(filename)
            Return Loaded
         Catch ex As Exception
            Close()
         End Try
      End Function

I then set the Column headers (as this is all encapsulated in an Excel Interop Wrapper class that handles all my direct access to the Excel files).
Primary Usage:

• Create/Load Excel file into Invisible App Object
• Load Column Headers (As in the Excel File)
• Set Column Headers (for created Excel File)
• Load DB Column Headers (as they are renamed for multiples Name, Name, Name, becomes Name1, Name2, Name3, etc)
• All COM Marshal Closing and Disposing, making sure the _app, _wb, and _wks objects are fully released and disposed and set to nothing.

Basically I make a call to create, and then execute an INSERT INTO statement off the server. I was just hoping I could find within SQL Server the ability to do a "Create Table OpenRowSet()" type of call, but apparently we cannot. *chuckle*

thanks though, for looking into it.

 

[cjard]

I take it that you tried SELECT * INTO OPENROWSET(...) FROM localtable...

 

[JaedenRuiner]

Yup.

I forget the error but it was a SqlException from the SqlCommand class, and it wasn't syntactical, it was something like a permission to access the schema, etc. Even tried a suggested ADODB method of:
SELECT * INTO [Excel 8.0;Database=Filename].[Sheet1] from localtable, and that came up with a different error about the filename not existing.

 

[MattP]

Ran into a situation where Interop wasn't a viable choice.

When you originally fill your dataset you'll need to set the DataAdapter's AcceptChangesDuringFill to False so each row is marked as new.

	Private Sub btnSaveFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles btnSaveFile.Click

		With Me.SaveFileDialog1
			.CreatePrompt = True
			.FileName = String.Empty
			.InitialDirectory = "C:\Temp\"
			.CreatePrompt = False
			.Filter = "Excel 2003 (*.xls)|.xls|Excel 2007 (*.xlsx)|.xlsx"
		End With

		If Me.SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
			Dim doc As New FileInfo(Me.SaveFileDialog1.FileName)
			If Me.SaveFileDialog1.FileName.EndsWith("xlsx") Then
				'Excel 2007 Files
				cn = New OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties={1}Excel 12.0 Xml;HDR=YES{1}", doc.FullName, ControlChars.Quote))
			ElseIf Me.SaveFileDialog1.FileName.EndsWith("xls") Then
				'Excel 2003 & earlier files
				cn = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}Excel 8.0;HDR=YES{1}", doc.FullName, ControlChars.Quote))
			End If

			DataSetToExcel(ds, doc.FullName, cn)
		End If

	End Sub

	Public Sub DataSetToExcel(ByVal ds As DataSet, ByVal FileName As String, ByVal Connection As OleDbConnection)

		Dim cmd As New OleDbCommand(String.Empty, cn)

		Try
			'Create workbook and worksheet
			If File.Exists(FileName) Then File.Delete(FileName)
			cn.Open()
			cmd.CommandText = "CREATE TABLE [MyTable] ([Last Name] VARCHAR(30), [First Name] Varchar(30), [ID] INT, [Job Title] Varchar(50))"
			cmd.ExecuteNonQuery()

			'Transfer dataset to worksheet
			cmd.CommandText = "INSERT INTO [MyTable] ([Last Name], [First Name], [ID], [Job Title]) VALUES (?, ?, ?, ?)"
			With cmd.Parameters
				.Add("@LastName", OleDbType.VarChar, 0, "Last Name")
				.Add("@FirstName", OleDbType.VarChar, 0, "First Name")
				.Add("@ID", OleDbType.Integer, 0, "ID")
				.Add("@JobTitle", OleDbType.VarChar, 0, "Job Title")
			End With

			Dim da As New OleDbDataAdapter
			da.InsertCommand = cmd
			da.Update(ds.Tables("File"))
		Catch ex As Exception
			MessageBox.Show(ex.ToString(), "Error with file creation", MessageBoxButtons.OK, MessageBoxIcon.Error)
		Finally
			cn.Close()
			cmd.Dispose()
		End Try

	End Sub