Issue 78634 - Cannot add multiple tables in CSV query
Summary: Cannot add multiple tables in CSV query
Status: CLOSED DUPLICATE of issue 8949
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.2.1 RC3
Hardware: Other Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@dba
Keywords: oooqa
Depends on:
Reported: 2007-06-18 21:27 UTC by schultzter
Modified: 2007-06-26 14:06 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

2 table sample with queries (1.63 KB, application/x-compressed)
2007-06-19 04:38 UTC, schultzter
no flags Details
Updated ODB that contains the query created by MS Query SQL statement as well (2.77 KB, application/vnd.sun.xml.base)
2007-06-19 04:47 UTC, schultzter
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description schultzter 2007-06-18 21:27:55 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.
Comment 1 Regina Henschel 2007-06-18 23:40:09 UTC
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.
Comment 2 schultzter 2007-06-19 04:38:24 UTC
Created attachment 46048 [details]
2 table sample with queries
Comment 3 schultzter 2007-06-19 04:45:31 UTC
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.
Comment 4 schultzter 2007-06-19 04:47:16 UTC
Created attachment 46049 [details]
Updated ODB that contains the query created by MS Query SQL statement as well
Comment 5 schultzter 2007-06-19 04:49:38 UTC
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!
Comment 6 Regina Henschel 2007-06-19 14:23:57 UTC
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
Comment 7 Regina Henschel 2007-06-22 09:34:11 UTC
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.
Comment 8 Frank Schönheit 2007-06-22 20:25:27 UTC
it's the same code, thanks for pointing out.

*** This issue has been marked as a duplicate of 8949 ***
Comment 9 christoph.lukasiak 2007-06-26 14:06:17 UTC
close duplicate