Using ADO.Net to create an excel file.

  • Thread starter Thread starter jjd
  • Start date Start date
J

jjd

Hi All,

Hope someone can help me or guide me in the right direction.

I will received random excel files from a user that I want to copy and
rename as "backup of...". I then want to take the original file, delete
it and create a new blank copy of the file with the same structure as
the "backup of..." file with 2 to 3 additional columns.

I will then use DataReader to read the data from the "backup of..."
file, as the file may be to big to use DataSet, and "INSERT" the data
using ADO with the additional data to occupy the new additional
columns.

I am able to open the file "backup of..." file and get the table
structure from GetSchemaTable but I am unable to create the structure
string to "CREATE" the new blank excel file.

My question is how do I create the new structure string for the blank
excel file with the same using only ADO? I cannot guarantee that excel
is installed on the PC running the program so this is the reason that
ADO is the prefered option.

Any help would be greatly appreciated.
 
On 21 Dec 2006 05:08:17 -0800, (e-mail address removed) wrote:

¤ Hi All,
¤
¤ Hope someone can help me or guide me in the right direction.
¤
¤ I will received random excel files from a user that I want to copy and
¤ rename as "backup of...". I then want to take the original file, delete
¤ it and create a new blank copy of the file with the same structure as
¤ the "backup of..." file with 2 to 3 additional columns.
¤
¤ I will then use DataReader to read the data from the "backup of..."
¤ file, as the file may be to big to use DataSet, and "INSERT" the data
¤ using ADO with the additional data to occupy the new additional
¤ columns.
¤
¤ I am able to open the file "backup of..." file and get the table
¤ structure from GetSchemaTable but I am unable to create the structure
¤ string to "CREATE" the new blank excel file.
¤
¤ My question is how do I create the new structure string for the blank
¤ excel file with the same using only ADO? I cannot guarantee that excel
¤ is installed on the PC running the program so this is the reason that
¤ ADO is the prefered option.
¤
¤ Any help would be greatly appreciated.

There are a couple of different ways to do this. One is to simply use a blank Excel template file
and create a copy when you need create a new structure.

The other method would be to use the DDL supported by Jet SQL:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
ExcelConnection.Open()

Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _
"(CustomerID INTEGER, " & _
"[Last Name] TEXT(50), " & _
"[First Name] TEXT(50), " & _
"Phone TEXT(10), " & _
"Email TEXT(50))"

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()

ExcelConnection.Close()

Below is a Jet SQL link:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,

Thanks for the reply, however what I really need to do is create an SQL
command string to create the file by looping through the DataTable
returned by the GetSchemaTable method of the DataReader because I will
not know the structure of the file in question so I cannot hardcore the
SQL command used to create the file.

What I am mainly having problems with is finding best practise for
doing this as well as converting the .net framework data type held in
the DataTable "DataType" field to the SQL datatype.

Again if you or anyone else could help me out I would be very grateful.
Thanks again, James.
 
¤ Hi Paul,
¤
¤ Thanks for the reply, however what I really need to do is create an SQL
¤ command string to create the file by looping through the DataTable
¤ returned by the GetSchemaTable method of the DataReader because I will
¤ not know the structure of the file in question so I cannot hardcore the
¤ SQL command used to create the file.
¤
¤ What I am mainly having problems with is finding best practise for
¤ doing this as well as converting the .net framework data type held in
¤ the DataTable "DataType" field to the SQL datatype.

Is there a reason why you're using GetSchemaTable instead of GetOleDbSchemaTable?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,

Hmmm, to be honest no! I am gonna presume, since you have suggested it,
that GetOleDbSchemaTable will provide me with what I am looking for.
Will give it a go, thanks loads for your help.

James
 
Hi all,

An update on my question. I think that GetSchemaTable should be able to
provide me with what I require if I can resolved one final question.

The DataTable returned by GetSchemaTable contains two columns, DataType
and ProviderType. To create the new file I need to be able to translate
one of these two columns to the correct type to use to build a SQL
command string. I have attempted this, using VB.Net, by trying this:

CType(rowStruct.Item("ProviderType"), OleDbType).ToString

Which under my tests returns a type of "VarWChar" for a column of type
string. I do not think I can use this type within my SQL command string
and when I try:

CType(rowStruct.Item("ProviderType"), SqlDbType).ToString

I get a type of "202"!

Any help as always would be greatly appreciated, James
 
¤ Hi all,
¤
¤ An update on my question. I think that GetSchemaTable should be able to
¤ provide me with what I require if I can resolved one final question.
¤
¤ The DataTable returned by GetSchemaTable contains two columns, DataType
¤ and ProviderType. To create the new file I need to be able to translate
¤ one of these two columns to the correct type to use to build a SQL
¤ command string. I have attempted this, using VB.Net, by trying this:
¤
¤ CType(rowStruct.Item("ProviderType"), OleDbType).ToString
¤
¤ Which under my tests returns a type of "VarWChar" for a column of type
¤ string. I do not think I can use this type within my SQL command string
¤ and when I try:
¤
¤ CType(rowStruct.Item("ProviderType"), SqlDbType).ToString
¤
¤ I get a type of "202"!
¤
¤ Any help as always would be greatly appreciated, James

You're going to have to map the value returned for GetSchemaTable with the SQL keyword equivalent if
you use DDL. The OleDbType values are only directly equivalent to the ADO/ADOX DataTypeEnum. This
means that you would have to use ADOX, which of course is the COM alternative (VB 6.0 code below):

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\TestExcel.xls;Extended Properties=Excel 8.0"

Set tbl = New ADOX.Table
tbl.Name = "SheetName"

Set col = New ADOX.Column
With col
.Name = "Col6"
.type = adVarWChar
.DefinedSize = 12
End With
tbl.Columns.Append col

Set col = New ADOX.Column
With col
.Name = "Col1"
.type = adDouble
End With
tbl.Columns.Append col
Set col = Nothing

Set col = New ADOX.Column
With col
.Name = "Col2"
.type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Set cat = Nothing


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Is there a way to find the hidden worksheets from the schema? All of them are listed in the "Tables" but the hidden one are missing from the "Columns".

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Back
Top