headers contains brackets

  • Thread starter Thread starter Kim Carlsen
  • Start date Start date
K

Kim Carlsen

Hi

How do I do a select from a excel sheet where the column header contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect, strConn);

Regards
Kim
 
Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
Patrick Molloy said:
have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

Kim Carlsen said:
Hi

How do I do a select from a excel sheet where the column header contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect, strConn);

Regards
Kim
Click to expand...
Click to expand...
 
do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Kim Carlsen said:
Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
Patrick Molloy said:
have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

Kim Carlsen said:
Hi

How do I do a select from a excel sheet where the column header contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect, strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I know the alias method, but the problem is not add the data to the
datatable, but to select the row from the excel file, where the header
contain brackets.

Kim

Patrick Molloy said:
do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Kim Carlsen said:
Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
Patrick Molloy said:
have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

Hi

How do I do a select from a excel sheet where the column header contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect, strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
did you get anywhere with this?
I wasn't able to get past discovering that the field name went from [ID] to
(ID) in the recordset which didn't really help as i had to use SELECT * FROM
...
and wasn't able to use the field name itself

Kim Carlsen said:
Thanks. I know the alias method, but the problem is not add the data to
the
datatable, but to select the row from the excel file, where the header
contain brackets.

Kim

Patrick Molloy said:
do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Kim Carlsen said:
Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
:

have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

Hi

How do I do a select from a excel sheet where the column header
contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect,
strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
Hi

No I couldn't figure it out.

Patrick Molloy said:
did you get anywhere with this?
I wasn't able to get past discovering that the field name went from [ID] to
(ID) in the recordset which didn't really help as i had to use SELECT * FROM
..
and wasn't able to use the field name itself

Kim Carlsen said:
Thanks. I know the alias method, but the problem is not add the data to
the
datatable, but to select the row from the excel file, where the header
contain brackets.

Kim

Patrick Molloy said:
do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
:

have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

Hi

How do I do a select from a excel sheet where the column header
contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect,
strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
I saved an excel workbook with [Instrument] as the column header, including
the square braces
I noiticed in SQL that Excel changed this to (Instrument) when I used SELECT
* FROM

so I tried this

SQL = "SELECT [(Instrument)] FROM [Instruments]"

and yes, it worked!




Kim Carlsen said:
Hi

No I couldn't figure it out.

Patrick Molloy said:
did you get anywhere with this?
I wasn't able to get past discovering that the field name went from [ID]
to
(ID) in the recordset which didn't really help as i had to use SELECT *
FROM
..
and wasn't able to use the field name itself

Kim Carlsen said:
Thanks. I know the alias method, but the problem is not add the data to
the
datatable, but to select the row from the excel file, where the header
contain brackets.

Kim

:

do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
:

have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

message
Hi

How do I do a select from a excel sheet where the column header
contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect,
strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
Thanks for helping this out.

that worked perfectly.

Kim

Patrick Molloy said:
I saved an excel workbook with [Instrument] as the column header, including
the square braces
I noiticed in SQL that Excel changed this to (Instrument) when I used SELECT
* FROM

so I tried this

SQL = "SELECT [(Instrument)] FROM [Instruments]"

and yes, it worked!




Kim Carlsen said:
Hi

No I couldn't figure it out.

Patrick Molloy said:
did you get anywhere with this?
I wasn't able to get past discovering that the field name went from [ID]
to
(ID) in the recordset which didn't really help as i had to use SELECT *
FROM
..
and wasn't able to use the field name itself

Thanks. I know the alias method, but the problem is not add the data to
the
datatable, but to select the row from the excel file, where the header
contain brackets.

Kim

:

do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
:

have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

message
Hi

How do I do a select from a excel sheet where the column header
contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect,
strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
excellent

Kim Carlsen said:
Thanks for helping this out.

that worked perfectly.

Kim

Patrick Molloy said:
I saved an excel workbook with [Instrument] as the column header,
including
the square braces
I noiticed in SQL that Excel changed this to (Instrument) when I used
SELECT
* FROM

so I tried this

SQL = "SELECT [(Instrument)] FROM [Instruments]"

and yes, it worked!




Kim Carlsen said:
Hi

No I couldn't figure it out.

:

did you get anywhere with this?
I wasn't able to get past discovering that the field name went from
[ID]
to
(ID) in the recordset which didn't really help as i had to use SELECT
*
FROM
..
and wasn't able to use the field name itself

Thanks. I know the alias method, but the problem is not add the data
to
the
datatable, but to select the row from the excel file, where the
header
contain brackets.

Kim

:

do you have the SQL?

you can change the column name

eg: select tablex from dbtable
will return tablex as the header

while
select tablex as [X] from dbtable
will return X as the header.



message
Hi

Yes it just gives me
"Syntax error in query expression '[[ID]]'."


regards
Kim
:

have you tried
String strSelect = "SELECT [[ID]] FROM [sheetname$]"

message
Hi

How do I do a select from a excel sheet where the column
header
contain
brackets? the actual name is [ID] ? I
i can't see how to escape correctly.
The code looks like:

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\test.xlsx;Extended Properties=Excel 12.0;";
String strSelect = "SELECT [ID] FROM [sheetname$]";
OleDbDataAdapter daMaster = new OleDbDataAdapter(strSelect,
strConn);

Regards
Kim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top