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....
ODBC stage for CSV file - SQL syntax
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Why are you using an ODBC stage at all? A Sequential File stage is by far to be preferred for reading CSV files.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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...
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...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: