Re: Handling special characters in column names in excel

On May 7, 5:46 pm, RC <sri...@xxxxxxxxx> wrote:
On May 7, 3:33 pm, "nkg" <x...@xxxxxxxxx> wrote:



try:
command.CommandText = "INSERT INTO [Apples$]([Fruit Paste 1.1 (Apple)])
VALUES("1")"

"RC" <sri...@xxxxxxxxx> wrote in message

news:1178561905.154209.242770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi,

We are using oleDb to write data to excel work***. When creating
columns we are not having any problems with special characters, the
following statement executes fine.

command.CommandText = "CREATE TABLE Apples ([Fruit Paste 1.1 (Apple)]
char(255))";

while inserting a value, the below line is throwing an exception and
it is because of this 1.1..

command.CommandText = "INSERT INTO [Apples$](Fruit Paste 1.1 (Apple))
VALUES("1")"

Are there any alternative ways to get this to work..I did not find
much documentation on this.

Thanks in advance,

I get the following error when I implemented your solution
ex = {"Invalid bracketing of name '[Fruit Paste 1.1 (Apple)]'."}

Okay I figured out a way to get this working,

Step 1: set the HDR=NO in the connection string
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;""";

Step 2:
command.CommandText = "CREATE TABLE Apples ([Fruit Paste 1.1 (Apple)]
char(255))";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Apples VALUES ("1");
command.ExecuteNonQuery();

It basically now does not consider this a header field and microsoft
excel will let you put wutever the hell we want..I am giving myself a
pat in the back..lol

.