Spaces between Column names

vkalyan · Post by **vkalyan** » Wed Jul 20, 2005 11:16 pm

Hi,

I have an Access Database input source. Most column names have spaces between them. When I import the table definition using Manager using an ODBC connection, all the columns import fine.

In my server job, I have an ODBC stage that connects to the Access Database through an ODBC DSN. I then load the imported table definition into that stage and that works fine.

However, when I do some mapping in a Transformation Stage, the columns that have spaces in between their names highlight red. Also, the job doesn't complie and it states that there is a general syntax error. I understand that column definitions in stages only allow alphabetical characters, periods, underscores but no spaces. I know that I could manually change the column names and replace any spaces with underscores.

Is there any solution to avoid changing the column names?

Any help would be greatly appreciated.

Thanks

Vinal.[/i]

ranga1970 · Post by **ranga1970** » Wed Jul 20, 2005 11:22 pm

You had answered your question as well

thanks

ray.wurlod · Post by **ray.wurlod** » Thu Jul 21, 2005 12:59 am

Make sure that the column names are correctly quoted. Click the GetSQLInfo button. At worst, insert the backquotes into the Derivation column on the Columns tab, or use user-defined SQL into which you've pasted the backquotes. Don't forget the Selection tab; any SQL you've placed there must also have delimited identifiers (backquoted column and table names).

vkalyan · Post by **vkalyan** » Thu Jul 21, 2005 4:00 am

ray.wurlod wrote:Make sure that the column names are correctly quoted. Click the GetSQLInfo button. At worst, insert the backquotes into the Derivation column on the Columns tab, or use user-defined SQL into which you've pasted the backquotes. Don't forget the Selection tab; any SQL you've placed there must also have delimited identifiers (backquoted column and table names).

Does that mean in any case I will have to change the column name and replace any spaces with some other character (like underscores) eg. Equipment ID must be changed to Equipment_ID

Thanks

Vinal.

roy · Post by **roy** » Thu Jul 21, 2005 4:25 am

Hi,
Other options include building views with proper column names hence bypassing the issue.
Another option would be to change hte table definition and use the original column names with aliases (i.e. column name = column_name , and the derivation would be

column name column_name

)

just pick the best way for you.

How about user defined select * and manually change the table definitions to have DS legal column names?

IHTH,

ray.wurlod · Post by **ray.wurlod** » Thu Jul 21, 2005 4:55 pm

It's not so much DS-legal as ODBC-legal. ODBC is very strict about adherence to standards. One of these is that column names containing "illegal" characters (including space) must be quoted. The standard specifies double-quote, but the Access driver ignores this and uses backquotes instead. Aren't standards wonderful?!

vkalyan · Post by **vkalyan** » Thu Jul 21, 2005 9:03 pm

ray.wurlod wrote:It's not so much DS-legal as ODBC-legal. ODBC is very strict about adherence to standards. One of these is that column names containing "illegal" characters (including space) must be quoted. The standard specifies double-quote, but the Access driver ignores this and uses backquotes instead. Aren't standards wonderful?!

Thanks for the reply.

So are there any other connection types that I can use to fix the problem.

Thanks

Vinal.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 21, 2005 9:13 pm

OLEDB