[Example] Loading CSV into preformatted spreadsheets

[Villeroy]

The attached zip contains a directory with the following 5 files:

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.

Code: Select all

"ID"	"Date"	"Category"	"Value"
1000	2008-06-06	"F"	63.18
1001	2007-07-22	"D"	77.3

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.
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:

Code: Select all

ID	Date	Category	Value
1000	2008-06-06	F	63.18
1001	2007-07-22	D	77.30

You may edit the unsaved new file and save the result like a normal csv file with modified filter settings.
menu:File>SaveAs...
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".

[Villeroy]

Base's csv driver is very limited so you may end up with lots of text values instead of numbers, dates, times etc. Sometimes you end up with numbers where you actually want to import text (zip codes, ISBN numbers etc).
The attached document comes with a simple Basic macro and an import range "Import1" linked to the "Bibliography" dBase table that is shipped with your office suite. The shipped dBase table is set up with all fields being text.
Save the document to a trusted macro directory.
When you load the document, it shows a message that a listener has been added to Import1.
When you refresh the import range, it shows a message that Import1 has been refreshed and then it will convert the numbers in columns B, Q and X but not the ISBN numbers in column AE. Column AE has been preformatted as text.

In your Calc document:
Ensure that your database ranges are set up with option "Keep Formatting" (the spreadsheet's formatting).
Apply text formatting to numeric columns that should remain text (column AE in the example).
Apply any non-text formatting to columns you want to convert to numbers on every refresh.
Call Tools>Customize... tab:Events and assign the routine to your own document's "View Created" event.

In your macro editor:
Copy my document's module in library "Text2NumberOnRefresh" to your own document or import the whole "Text2NumberOnRefresh" library.
Adjust routine "addMyRefreshListeners" to your needs. Add one line per database range with the right range name in double-quotes.
Reload your document.
When everything works as expected, you can suppress all message boxes by setting Const cShowMsgBox = False.

2018-04-07: Added a Python version doing the same thing with all db-ranges whose names start with "NumberText_". A helper routine "renameCurrentRanges" prepends the "NumberText_" prefix to database ranges intersecting with the current selection of cells.
Just open the Writer document and click the button to install the Python code.
Call Tools>Customize... and assign pyCalc/DBRange_NumberText/addNumTextRefreshListeners to the "View Created" event.
Select your database range (or some cells in it) and call macro pyCalc/DBRange_NumberText/renameCurrentRanges.