Crummy Characters in Access Table name (DBI)

Blue_eyed_son has asked for the wisdom of the Perl Monks concerning the following question:

Hi--I'm trying to convert a bunch of .mdb tables into tab delimited, and am running in to trouble with the table names (I think). The table names have commas in them, for example: 1,1,1-Trichloroethane_Chem2981 Both using commercial software (StatTransfer) and now using DBI, I get the error "Sytax error in FROM clause." Here's DBI code:

my $db = "1,1,1-Trichloroethane_Chem2981.mdb";
my $table ="1,1,1-Trichloroethane_Chem2981";
$dbh = DBI->connect( "dbi:ODBC:driver=Microsoft Access Driver (*.mdb, 
+*.accdb); dbq=$db","","");
$sql = "SELECT * FROM $table";
$sth = $dbh->prepare( $sql ) or die;
[download]

Any idea how I can get around the problem loading the table?
Thanks.

Comment on Crummy Characters in Access Table name (DBI) Download Code

Replies are listed 'Best First'.
Re: Crummy Characters in Access Table name (DBI) by mje (Curate) on Jul 08, 2011 at 08:23 UTC
You need to quote the table name - use quote_identifier	[reply]
Re^2: Crummy Characters in Access Table name (DBI) by Blue_eyed_son (Sexton) on Jul 08, 2011 at 15:27 UTC
Thanks, mje. This did the trick: `$qtable=$dbh->quote_identifier($table); $sql = "SELECT * FROM $qtable"; $sth = $dbh->prepare( $sql ) or die; $sth->execute();` [download]	[reply] [d/l]
Re: Crummy Characters in Access Table name (DBI) by Anonymous Monk on Jul 08, 2011 at 02:24 UTC
Its as if you wrote `my $MoneyBagFullOf$$$Dollars = 4;` [download] You can't have the sigil $ as part of a variable name, its a syntax error You need to use placeholders or `$dbh->quote`, you an read about it in DBI docs http://w3schools.com/sql/	[reply] [d/l] [select]