Apache OpenOffice (AOO) Bugzilla – Issue 78634
Cannot add multiple tables in CSV query
Last modified: 2007-06-26 14:06:17 UTC
When building a query based on a CSV database only one table can be queried at a time. Cannot build a query that joins multiple tables. Also, by default CSV files assume a semi-colon (;) as a seperator - not a comma (,) as the name of the format indicates!??!?!?! I have not yet tried this with MS Access but it does work with MS Query - using the Microsoft CSV ODBC driver. The SQL statement generated by OOo Base is rejected by the MS ODBC driver for CSV files.
For the inbuilt text driver, a query is limited to one table. But using the MS ODBC driver I have no problem querying two tables. Perhaps something is wrong in the odbc settings. Please generate a tiny database with two tables and attach the whole folder. It should contain the two csv-files, the file schema.ini, and the odb-file. Please define the query too and post the desired SQL-statement here in addition. Please cite the error message, which you get. There is no norm, which says what delimiter a csv-file must have. For most of the languages a comma is a bad choice, because it is either the decimal delimiter or the thousand delimiter. Therefore a semicolon is a good default.
Created attachment 46048 [details] 2 table sample with queries
No schema.ini because it work better without it. Doing a "select *" type query gave me this error: The data content could not be loaded. SQL Status: HY000 Error code: -3029 [Microsoft][ODBC Text Driver] The Microsoft Jet database engine does not recognize 'Investments.csv.*' as a valid field name or expression. The SQL command leading to this error is: SELECT `Investments.csv`.*, `MonthlyStatement.csv`.* FROM `MonthlyStatement.csv`, `Investments.csv` WHERE ( `MonthlyStatement.csv`.`InvestmentID` = `Investments.csv`.`InvestmentID` ) Selecting the columns individually gave me this error: The data content could not be loaded. SQL Status: 42000 Error code: -1002 [Microsoft][ODBC Text Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. The SQL command leading to this error is: SELECT `Investments.csv`.`InvestmentID`, `Investments.csv`.`Symbol`, `Investments.csv`.`Name`, `MonthlyStatement.csv`.`InvestmentID`, `MonthlyStatement.csv`.`Month`, `MonthlyStatement.csv`.`Units`, `MonthlyStatement.csv`.`AdjCostBase`, `MonthlyStatement.csv`.`BookValue`, `MonthlyStatement.csv`.`MktPrice`, `MonthlyStatement.csv`.`MktValue` FROM `MonthlyStatement.csv`, `Investments.csv` WHERE ( `MonthlyStatement.csv`.`InvestmentID` = `Investments.csv`.`InvestmentID` ) But pasting the SQL statement created by MS Query works: SELECT Investments.InvestmentID, Investments.Symbol, Investments.Name, MonthlyStatement.InvestmentID, MonthlyStatement.Month, MonthlyStatement.Units, MonthlyStatement.AdjCostBase, MonthlyStatement.BookValue, MonthlyStatement.MktPrice, MonthlyStatement.MktValue FROM Investments.csv Investments, MonthlyStatement.csv MonthlyStatement WHERE Investments.InvestmentID = MonthlyStatement.InvestmentID But I cannot return to the Design View with this SQL statement. Also, Copy Ctrl-C seems to take a long time (there's a delay before I can Paste the selection) and I think it's related to the MS Office clipboard - but I don't know how turn off the MS Office clipboard so I can't test.
Created attachment 46049 [details] Updated ODB that contains the query created by MS Query SQL statement as well
Regarding the choice of delimiters - I agree with your logic but reality does not. I created the tables using OOo Calc and saved the sheets as CSV files. Even Calc assumes CSV means COMMA separated values and did not give me the option to use anything else!
I must use a schema.ini otherwise only the first column is there. Please make sure, that all columns are there in OOo in the tables. You have to use table aliases which gives the engine a table name without fullstop. To use alias goto Edit > Database > Advanced Settings > Special Settings. Please have look at your field names. Are they in capitals? Then you must use capitals in the query too. For example the statement SELECT `Investments`.*, `MonthlyStatement`.* FROM `MonthlyStatement.csv` AS `MonthlyStatement`, `Investments.csv` AS `Investments` WHERE ( `MonthlyStatement`.`INVESTMENTID` = `Investments`.`INVESTMENTID` ) works for me. Using the SQL statement created by MS Query can only work as unparsed SQL, because it is the wrong syntax for OOo SQL interpreter. You can use delimiters as you like when saving from calc as csv. You have to check "Edit filter settings". All this things should be discussed in a forum or mailinglist before submitting an issue. A good place would be users@dba.openoffice.org
You cannot design queries containing two flat csv tables. The same problem for dBase and Calc is covered by issue 8949. If csv tables need the same code, this issue can be set as duplicate, otherwise it is a valid feature request.
it's the same code, thanks for pointing out. *** This issue has been marked as a duplicate of 8949 ***
close duplicate