1. Table1.csv contains all the data. It is the only source of data. All data in the other files are linked to this one.
It is a tab-delimited text table with double-quoted text, ISO-dates and dotted decimals in random row order. The first 3 rows look like this.
2. Database document CSV_Editor.odb demonstrates how to create a whole graphical user interface for specific types of csv files. However, the graphical user interface for the database developer does not allow linking a true editable database table to a text file. Luckily, the SQL to create such a link is not too sophisticated. Everything is documented in the contained README, including links to other resources.
Code: Select all
"ID" "Date" "Category" "Value" 1000 2008-06-06 "F" 63.18 1001 2007-07-22 "D" 77.3
The database's input form "q_csvLink" lets you edit the text table in a database form with calendar control, drop down box, filters, sorting, default values and some basic aggregation (show sum and average for the current record's quarter and category).
The form is loaded with latest dates on top and the user simply edits the 3 editable boxes. Each edited row is written directly to the disk as soon as you go to another row.
A semi-automatically created report shows the aggregated contents grouped by quarters and group-letters in a pretty printable Writer layout.
3. CSV_Database.odb shows all similar csv-files within the same directory as read-only tables of a pseudo-database (currently our Table1.csv only).
This has the advantage that you can dump many similar text files and they will be available instantly after refreshing the view on the database.
This simplistic pseudo-database provides read-only access and some limited options to rearrange and report table data. The database comes with a README document in the forms section. It is supposed to be used with the two spreadsheets and requires a little bit of setup.
Installing the CSV_Database.odb ( no longer needed since v3.2):
Open the document and call menu:Edit>Database>Properties...
Hit the [Browse] button and specify the extracted directory "TextDB" as path to the text files. This step will be obsolete when the path is stored relatively to the database document (OOo 3.2?).
With any version: Call menu:Tools>Options...OOoBase>Databases and register the database under the name "CSV_Database". This makes the database accessible from arbitrary ODF documents.
Since version 3.3 there is a new attribute if the imported text data have column headers in the first row. Set menu:Edit>Database>Properties:"Text contains headers" and save the database in order to make it work properly.
If your text files do not include a first row of column labels, leave that option unchecked. The columns will be labeled C1, C2, C3,... and simple queries can rename them any way you want.
4. Spreadsheet template CSV_Report.ots serves as a report engine for the csv table.
Open the template. All the details of this reporting spreadsheet are documented on the first sheet "README".
Activate the sheet named "Sheet Report", get the beamer (key F4), browse CSV_Database>Queries>Ordered and drag the query icon into the preformatted import range A4:C6. Notice the conditional formattings, the chart and the formulas adjusting to the size of the imported row set. Replace the imported query with the other query. Play with the data pilot on the third sheet.
Basically, you load csv by drag&drop into a preformatted cell range. This is far easier and more powerful than the usual csv import routine.
5. Spreadsheet template CSV_Editor.ots serves as a preformatted import sheet for "Table1.csv" or any equally structured csv table.
Open the template, get the beamer (key F4), browse CSV_Database>Tables>Table1 and drag the table icon onto A1 of the preformatted sheet. Notice the number formatting which reflects the csv-data (ISO-date and dotted decimals with 2 decimal digits). Notice also the cell validation which can be a useful for editing csv
The first 3 rows look like this:
You may edit the unsaved new file and save the result like a normal csv file with modified filter settings.
Code: Select all
ID Date Category Value 1000 2008-06-06 F 63.18 1001 2007-07-22 D 77.30
File type: Text (*.csv)
[X] Edit filter settings.
Confirm everything and make sure that the filter settings use a tab delimiter, quoted strings and "values as displayed".