read/write excel using odbc

how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an example.

i have managed to read an excel file using odbc

Re: read/write excel using odbc

Pretty much the same way you would write data to any database using ODBC.
Found this example on The Code Project of doing this in c++. The language
is different, but the actual SQL statements would be the same for C#. One
thing to notice in the example is that the connection string has the
parameter READONLY=FALSE.

http://www.codeproject.com/database/excel_odbc_write.asp

Disclaimer: I haven't tested this example. However, Code Project has always
been a reliable source for me.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


".paul." wrote in message
news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
> how can i write to an excel .xls file using odbc?
> i've read in several places that its possible but i can't find an
> example.
>
> i have managed to read an excel file using odbc

Re: read/write excel using odbc

i couldn't translate that example at codeproject. i'm trying to use this code:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=" & Application.StartupPath & "\test.xls;"

Dim Con As New OdbcConnection(ConnectionString)

Dim strSQL As String = "insert into [Sheet1$] ([Quantity], [Code],
[Product]) values (0, 'testtext2', 'testtext3')"

Dim cmd As New OdbcCommand(strSQL)
cmd.Connection = Con
Con.Open()
cmd.ExecuteNonQuery()


it works ok until it gets to cmd.ExecuteNonQuery() when it causes an error:

ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an
updateable query.

any ideas?






"Andrew Faust" wrote:

> Pretty much the same way you would write data to any database using ODBC.
> Found this example on The Code Project of doing this in c++. The language
> is different, but the actual SQL statements would be the same for C#. One
> thing to notice in the example is that the connection string has the
> parameter READONLY=FALSE.
>
> http://www.codeproject.com/database/excel_odbc_write.asp
>
> Disclaimer: I haven't tested this example. However, Code Project has always
> been a reliable source for me.
>
> --
> Andrew Faust
> andrew[at]andrewfaust.com
> http://www.andrewfaust.com
>
>
> ".paul." wrote in message
> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
> > how can i write to an excel .xls file using odbc?
> > i've read in several places that its possible but i can't find an
> > example.
> >
> > i have managed to read an excel file using odbc
>

Re: read/write excel using odbc

On Sun, 21 Oct 2007 14:23:01 -0700, .paul.
wrote:

>how can i write to an excel .xls file using odbc?
>i've read in several places that its possible but i can't find an example.
>
>i have managed to read an excel file using odbc

Is there any particular reason you're using ODBC?

--
http://bytes.thinkersroom.com

Re: read/write excel using odbc

A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out.
This will help determine if your connection string is off, or you're ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about a
column having to have a "primary key" on it (yes, I"m talking about excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that one.

...

Please post your resolution when you're done so other can learn from the
experience.





".paul." wrote in message
news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
> how can i write to an excel .xls file using odbc?
> i've read in several places that its possible but i can't find an example.
>
> i have managed to read an excel file using odbc

Re: read/write excel using odbc

there was a problem with the connection string.
i changed it to this and it worked:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
Directory.GetParent(Directory.GetParent(My.Application.Info. DirectoryPath).ToString).ToString & "\joblog.xls;"


"sloan" wrote:

>
> A couple of things:
>
> 1. Try to avoid ODBC, and use OleDB if you can.
> Google "IDataReader Excel" and you'll find connection strings for that.
>
> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out.
> This will help determine if your connection string is off, or you're ONLY
> having an insert issue.
>
> 3. I believe (long term memory working here) there is something about a
> column having to have a "primary key" on it (yes, I"m talking about excel)
> before you can do update/insert commands on an excel spreadsheet.
> You'll have to search, I don't know any links for key phrases on that one.
>
> ...
>
> Please post your resolution when you're done so other can learn from the
> experience.
>
>
>
>
>
> ".paul." wrote in message
> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
> > how can i write to an excel .xls file using odbc?
> > i've read in several places that its possible but i can't find an example.
> >
> > i have managed to read an excel file using odbc
>
>
>

Re: read/write excel using odbc

Which is why I asked.

Getting Excel connection strings is ..... very very tricky sometimes.

; space quotes, they'll screw you.

..................

You still may want to look at the OleDB provider, there is no reason to use
ODBC anymore.

http://support.microsoft.com/kb/316934
(except dont hack together a presentation/datalayer spaghetti like they did
on the sample)





".paul." wrote in message
news:A8BABABA-DE62-435A-B5E9-D44939A42473@microsoft.com...
> there was a problem with the connection string.
> i changed it to this and it worked:
>
> Dim ConnectionString As String = "Driver={Microsoft Excel Driver
> (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
> Directory.GetParent(Directory.GetParent(My.Application.Info. DirectoryPath).ToString).ToString
> & "\joblog.xls;"
>
>
> "sloan" wrote:
>
>>
>> A couple of things:
>>
>> 1. Try to avoid ODBC, and use OleDB if you can.
>> Google "IDataReader Excel" and you'll find connection strings for that.
>>
>> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
>> out.
>> This will help determine if your connection string is off, or you're ONLY
>> having an insert issue.
>>
>> 3. I believe (long term memory working here) there is something about a
>> column having to have a "primary key" on it (yes, I"m talking about
>> excel)
>> before you can do update/insert commands on an excel spreadsheet.
>> You'll have to search, I don't know any links for key phrases on that
>> one.
>>
>> ...
>>
>> Please post your resolution when you're done so other can learn from the
>> experience.
>>
>>
>>
>>
>>
>> ".paul." wrote in message
>> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
>> > how can i write to an excel .xls file using odbc?
>> > i've read in several places that its possible but i can't find an
>> > example.
>> >
>> > i have managed to read an excel file using odbc
>>
>>
>>

Re: read/write excel using odbc

> You still may want to look at the OleDB provider, there is no reason to
> use ODBC anymore.

That's pretty extreme. If you happen to need to connect to the largest
number of different databases ODBC is still hugely valuable. While all the
major DBs support OleDB these days, there are a number I've needed to read
from that only had ODBC. Of course it's no big deal simple use
DBConnection, DBReader, etc and instantiate the appropriate class

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


"sloan" wrote in message
news:OmU%23$vXFIHA.2004@TK2MSFTNGP06.phx.gbl...
>
>
> Which is why I asked.
>
> Getting Excel connection strings is ..... very very tricky sometimes.
>
> ; space quotes, they'll screw you.
>
> .................
>
> You still may want to look at the OleDB provider, there is no reason to
> use ODBC anymore.
>
> http://support.microsoft.com/kb/316934
> (except dont hack together a presentation/datalayer spaghetti like they
> did on the sample)
>
>
>
>
>
> ".paul." wrote in message
> news:A8BABABA-DE62-435A-B5E9-D44939A42473@microsoft.com...
>> there was a problem with the connection string.
>> i changed it to this and it worked:
>>
>> Dim ConnectionString As String = "Driver={Microsoft Excel Driver
>> (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
>> Directory.GetParent(Directory.GetParent(My.Application.Info. DirectoryPath).ToString).ToString
>> & "\joblog.xls;"
>>
>>
>> "sloan" wrote:
>>
>>>
>>> A couple of things:
>>>
>>> 1. Try to avoid ODBC, and use OleDB if you can.
>>> Google "IDataReader Excel" and you'll find connection strings for that.
>>>
>>> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
>>> out.
>>> This will help determine if your connection string is off, or you're
>>> ONLY
>>> having an insert issue.
>>>
>>> 3. I believe (long term memory working here) there is something about
>>> a
>>> column having to have a "primary key" on it (yes, I"m talking about
>>> excel)
>>> before you can do update/insert commands on an excel spreadsheet.
>>> You'll have to search, I don't know any links for key phrases on that
>>> one.
>>>
>>> ...
>>>
>>> Please post your resolution when you're done so other can learn from
>>> the
>>> experience.
>>>
>>>
>>>
>>>
>>>
>>> ".paul." wrote in message
>>> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
>>> > how can i write to an excel .xls file using odbc?
>>> > i've read in several places that its possible but i can't find an
>>> > example.
>>> >
>>> > i have managed to read an excel file using odbc
>>>
>>>
>>>
>
>

Re: read/write excel using odbc

Let me rephrase.

There's no reason to add the extra layer of ODBC, when a more native
provider exists.

Aka, you can read excel without odbc.

But yeah, you want to keep the ODBC around for those who don't have a more
native provider.

...

Like , it would be kinda silly to use ODBC for Sql Server.

But for .. I know know.. Paradox or something , ODBC is a viable tool.

//Rephrased
You still may want to look at the OleDB provider, there isn't alot of good
reason to
use ODBC anymore WITH Excel because there is a better option FOR Excel.
Extra layers slow the performance slightly.




"Andrew Faust" wrote in message
news:C3C754EA-CFC6-425E-925A-69044EA81A0D@microsoft.com...
>> You still may want to look at the OleDB provider, there is no reason to
>> use ODBC anymore.
>
> That's pretty extreme. If you happen to need to connect to the largest
> number of different databases ODBC is still hugely valuable. While all the
> major DBs support OleDB these days, there are a number I've needed to read
> from that only had ODBC. Of course it's no big deal simple use
> DBConnection, DBReader, etc and instantiate the appropriate class
>
> --
> Andrew Faust
> andrew[at]andrewfaust.com
> http://www.andrewfaust.com
>
>
> "sloan" wrote in message
> news:OmU%23$vXFIHA.2004@TK2MSFTNGP06.phx.gbl...
>>
>>
>> Which is why I asked.
>>
>> Getting Excel connection strings is ..... very very tricky sometimes.
>>
>> ; space quotes, they'll screw you.
>>
>> .................
>>
>> You still may want to look at the OleDB provider, there is no reason to
>> use ODBC anymore.
>>
>> http://support.microsoft.com/kb/316934
>> (except dont hack together a presentation/datalayer spaghetti like they
>> did on the sample)
>>
>>
>>
>>
>>
>> ".paul." wrote in message
>> news:A8BABABA-DE62-435A-B5E9-D44939A42473@microsoft.com...
>>> there was a problem with the connection string.
>>> i changed it to this and it worked:
>>>
>>> Dim ConnectionString As String = "Driver={Microsoft Excel Driver
>>> (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
>>> Directory.GetParent(Directory.GetParent(My.Application.Info. DirectoryPath).ToString).ToString
>>> & "\joblog.xls;"
>>>
>>>
>>> "sloan" wrote:
>>>
>>>>
>>>> A couple of things:
>>>>
>>>> 1. Try to avoid ODBC, and use OleDB if you can.
>>>> Google "IDataReader Excel" and you'll find connection strings for that.
>>>>
>>>> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
>>>> out.
>>>> This will help determine if your connection string is off, or you're
>>>> ONLY
>>>> having an insert issue.
>>>>
>>>> 3. I believe (long term memory working here) there is something about
>>>> a
>>>> column having to have a "primary key" on it (yes, I"m talking about
>>>> excel)
>>>> before you can do update/insert commands on an excel spreadsheet.
>>>> You'll have to search, I don't know any links for key phrases on that
>>>> one.
>>>>
>>>> ...
>>>>
>>>> Please post your resolution when you're done so other can learn from
>>>> the
>>>> experience.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ".paul." wrote in message
>>>> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com...
>>>> > how can i write to an excel .xls file using odbc?
>>>> > i've read in several places that its possible but i can't find an
>>>> > example.
>>>> >
>>>> > i have managed to read an excel file using odbc
>>>>
>>>>
>>>>
>>
>>
>

Re: read/write excel using odbc

On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust"
wrote:

>> You still may want to look at the OleDB provider, there is no reason to
>> use ODBC anymore.
>
>That's pretty extreme. If you happen to need to connect to the largest
>number of different databases ODBC is still hugely valuable. While all the
>major DBs support OleDB these days, there are a number I've needed to read
>from that only had ODBC. Of course it's no big deal simple use
>DBConnection, DBReader, etc and instantiate the appropriate class

But for some applications performance is a critical issue -- ODBC is
slower than OLEDDB

--
http://bytes.thinkersroom.com

Re: read/write excel using odbc

Not disputing it. I totally agree you should use the other providers when
available. I was just pointing out that there are databases for which there
is only ODBC, thus there is sometimes a reason to use ODBC.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


"Rad [Visual C# MVP]" wrote in message
news:csavh3dp7samnc1rlhmuv6eavc0gmt7iis@4ax.com...
> On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust"
> wrote:
>
>>> You still may want to look at the OleDB provider, there is no reason to
>>> use ODBC anymore.
>>
>>That's pretty extreme. If you happen to need to connect to the largest
>>number of different databases ODBC is still hugely valuable. While all
>>the
>>major DBs support OleDB these days, there are a number I've needed to
>>read
>>from that only had ODBC. Of course it's no big deal simple use
>>DBConnection, DBReader, etc and instantiate the appropriate class
>
> But for some applications performance is a critical issue -- ODBC is
> slower than OLEDDB
>
> --
> http://bytes.thinkersroom.com

Re: read/write excel using odbc

Gotcha.

I should have had "With Excel" at the end of it to avoid the ambuguity.


"Andrew Faust" wrote in message
news:99312F0D-3F09-467D-BEBF-DF526AB0F064@microsoft.com...
> Not disputing it. I totally agree you should use the other providers when
> available. I was just pointing out that there are databases for which
> there is only ODBC, thus there is sometimes a reason to use ODBC.
>
> --
> Andrew Faust
> andrew[at]andrewfaust.com
> http://www.andrewfaust.com
>
>
> "Rad [Visual C# MVP]" wrote in message
> news:csavh3dp7samnc1rlhmuv6eavc0gmt7iis@4ax.com...
>> On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust"
>> wrote:
>>
>>>> You still may want to look at the OleDB provider, there is no reason to
>>>> use ODBC anymore.
>>>
>>>That's pretty extreme. If you happen to need to connect to the largest
>>>number of different databases ODBC is still hugely valuable. While all
>>>the
>>>major DBs support OleDB these days, there are a number I've needed to
>>>read
>>>from that only had ODBC. Of course it's no big deal simple use
>>>DBConnection, DBReader, etc and instantiate the appropriate class
>>
>> But for some applications performance is a critical issue -- ODBC is
>> slower than OLEDDB
>>
>> --
>> http://bytes.thinkersroom.com
>