CSV Operations with Base

[famo]

Hello,
I have a folder with varying .csv-files, which I only need to evaluate (no editing) with the datapilot.
Currently I have created a base file which is linked to the .csv files and provides them as global data source. In calc then I can evaluate each of the csv's with the datapilot.
So far so good, but:
1.
It would be (way) more convenient, if there would be one big .csv file which I could evaluate with the datapilot. Instead of having to create a new datapilot for every new csv. The csv's have all unique entries (date and time), so there won't be identical rows after concatenation. This doesn't need to be a real .csv, but just one source which I could address in the datapilot.
2.
For better evaluation I'm missing two columns in the csv's, which I would like to have added before I evaluate them in the datapilot.
a) One column content is based on the content in of one the other columns ("If (col1) eq "abc" then "A_BC", elseif ... ).
b) The other column must contain a part of the file name.

So is this somehow possible with calc/base? Maybe even with a complete different way, any help is appreciated.
Most important is:
- maximum automating, i.e. no repetitive steps upon changing csv's (except for "Refresh datasource" or similar easy tasks)
- no macros (if possible)

Thanks
famo

[rudolfo]

My first approach would be to use a UNION query for this. Unfortunately this is not working with csv files as backend tables. Okay, I could switch to "direct SQL" to at least manage to save the union query. But if I open then the query OOo Base 3.1 dies.

And I guess you will have the same issue for your additional columns. With other database backends that support the SQL 90 standard you would use:

Code: Select all

CASE WHEN col1 = 'abc' THEN 'A_BC' ELSE 'xxx' END

For this again you would need "Direct SQL mode", but with csv there is no backend that understands the CASE statement.

Seems like you need to import your csv files into a database. Some databases, not sure about the embedded HSQLDB, support external tables and can treat csv files as readonly tables without duplicating the content in internal tables.

[Villeroy]

It is possible to treat csv tables just like ordinary database tables when you use a real database engine for the import.
Using the built-in HSQLDB you prepare a text table with the right fields which gets populated with data from the text file.
[Tutorial] Using csv/text files as editable data source.

Then you can use views (with UNION or from some NewData.csv) and merge them into an existing table.
INSERT INTO "table" (SELECT ... FROM "NewData" WHERE ...) or something like that.
Dragging a view onto a table works as well. Then choose "Append Data" and follow the wizard.

[famo]

Thanks, for your answers. So I guess this means none of my points can be accomplished with my current setup. :-/

When I refer to the tutorial ([Tutorial] Using csv/text files as editable data source.), I must say its quite hard to understand.
However before I try to fully understand it:

Allow me to mention a few important restrictions:

• The text file has to be in the same directory as the database file.
• All dates need to be ISO dates 1999-12-31
• All decimal figures need the dot as decimal delimter.
• There is no option to treat subsequent delimiters as one as you can do in Calc's csv import.
• HSQLDB does not support text tables with fields of fixed width. Only character separated text tables are supported.

My files:

• Text files are in the same directory as the database file.
• Dates are NOT ISO dates, but DD.MM.YYYY
• Decimal figures use COMMA as decimal delimter.
• Values are separated by semicolons.

Is it still possible to use them with your suggested solution?

Also I'm not sure if UNION is the right command, I need to concatenate the files.

[Villeroy]

My files:

* Text files are in the same directory as the database file.
* Dates are NOT ISO dates, but DD.MM.YYYY
* Decimal figures use COMMA as decimal delimter.
* Values are separated by semicolons.

Is it still possible to use them with your suggested solution?

I think there are better database backends than the default HSQLDB in Base. HSQLDB can not use German numbers and dates.
For a Windows user this may be like black magic but it should be possible to convert your text files by means of simple scripts even on that platform. And while you are in it you can merge all the lines below the header row into one file.

HSQLDB provides some useful functions to convert strings to numbers and dates.
http://hsqldb.org/doc/guide/ch09.html
It's all there: SUBSTR, CONCAT, CAST, CONVERT and UNION (which eliminates duplicate rows by default).

[famo]

For a Windows user this may be like black magic but it should be possible to convert your text files by means of simple scripts even on that platform.

lol , ... but jokes aside, thing is the files are on a network folder and AFAIK Windows DOS commands doesn't work there (at least cmd prompts with "... CMD doesn't support UNC-Paths ...").
The reason they are on a network folder, is that they are shared with other users (which are not so tech-savvy), which I would also have to teach the solution (or at least how to use it)- thats why I want it to keep it as simple as possible.

On the other hand the csv files already need to be edited (delete the first 5 rows), so a script would be indeed handy...

When you write (Villeroy) "I think there are better database backends than the default HSQLDB in Base." Do you mean other backends within OOo or outside of OOo?

So I'm still unsure how to proceed, any ideas are still appreciated...

[RoryOF]

If you mapped the network drive to a local drive I think your command prompt would work.

[Villeroy]

When you write (Villeroy) "I think there are better database backends than the default HSQLDB in Base." Do you mean other backends within OOo or outside of OOo?

All database servers are "outside" OOo, even the HSQLDB which gets extracted and connected through a server every time you "open your database document" (I'd rather say: "install your database add-on"). I mean, some database engine might support German text values given some extra arguments.

Without coding macros nor other scripts, you can prepare a database table, a text table and a view. The view converts text table's German values to regular values.
When you get a new file, drag the view over the table and confirm that you want to append data.
Next time you get a new file you replace the old file with the equally named new file and drag the view over the table.
new.csv >> text table >> view >> collecting table

Oh, of course you can connect the German text to a csv-database while your application locale is German.
Then it should be possible to drag any of the listed text file from the csv-database onto a writable table of a server database.
I use to import German text from a csv-db into Calc. It imports dates and decimal figures correctly when my locale is set to German.

[MrProgrammer]

the files are on a network folder and AFAIK Windows DOS commands doesn't work there

The PUSHD command can connect to a network-attached folder and COPY will work there. I've done it hundreds of times.

If you mapped the network drive to a local drive I think your command prompt would work.

That will work, too, but it's a manual step in Windows Explorer.

[There are] other users (which are not so tech-savvy), which I would also have to teach the solution …

For that situation, I would have written a Windows "batch file" script to locate the folder and perform the merge for them. However most people don't realize that a "batch file" (file.BAT or file.CMD) can do that and other sophisticated operations. However, I'm not in a great position to help with this, since (A) I no longer have a Windows machine (yeah!) and (B) this isn't a Windows support forum. So, you may want to explore other alternatives to merging the files.

[RoryOF]

If you mapped the network drive to a local drive I think your command prompt would work.

That will work, too, but it's a manual step in Windows Explorer.

I'm a great believer in "quick and dirty". Get the job finished and out of your hair.

Not thinking of this or any user in particular, I often wonder how much time and effort is put into writing macros or complex formatting for one-off cases. Sometimes one-off cases might be better handled by doing whatever it is by hand!