ODBC stage for CSV file - SQL syntax

metabill · Post by **metabill** » Mon May 16, 2005 8:34 am

Greetings. I am trying to correctly define an ODBC stage to read data from a CSV file. The CSV file includes field names in the first record, and I have generated an associated schema.ini file during creation of a system DSN. I've verified that I can access the data vis DSN outside of DataStage using WinSQL.

The generated SQL in the ODBC stage causes errors when I try to view the data from the properties dialog of the stage. This seemingly is caused by my failure to correctly define the quote character and/or schema delimiter character. The main problem seems to be that the physical filename includes a ".csv" extension, as in "mydata.csv"; this becomes the table name in the SQL. My various attempts to enquote this table name have been unsuccessful, resulting in various SQL syntax errors.

I'd certainly appreciate any ideas. Thanks....

ray.wurlod · Post by **ray.wurlod** » Mon May 16, 2005 6:15 pm

Are you using an ODBC driver for text files? What happens when you click "Get SQL Info" in the stage properties of the ODBC stage?

Why are you using an ODBC stage at all? A Sequential File stage is by far to be preferred for reading CSV files.

chulett · Post by **chulett** » Mon May 16, 2005 8:50 pm

ray.wurlod wrote:Why are you using an ODBC stage at all? A Sequential File stage is by far to be preferred for reading CSV files.

I'm assuming because the file is not local to the DataStage server.

metabill · Post by **metabill** » Tue May 17, 2005 11:29 am

First, thanks for suggesting that I use a Sequential stage instead of an ODBC stage; I can certainly do that. I've become accustomed to using ODBC access to CSV files outside of DataStage, so I just assumed that would also be the way to go within a DataStage job. The data file in question is actually local to the server. Can you please explain why the Sequential stage approach is preferred?

I am using the MS text driver for this CSV file. When I click on Get SQL Info in the ODBC stage, I get a backtick (`) for Quote character and an escaped period (\.) for Schema Delimiters. The generated SQL then looks something like:

SELECT `area_2000`.`csv`.HOSPSTCO, `area_2000`.`csv`.AGECAT FROM `area_2000`.`csv`;

Attempting to View Data results in an error that is "Syntax error in the FROM clause".

Thanks...

ray.wurlod · Post by **ray.wurlod** » Tue May 17, 2005 11:00 pm

Looks like there's something about the table name that the ODBC driver or JET doesn't like. Can you post the generated SQL?