importing data from .csv file

Asked by treetaxi

I have a perl program that generates a csv file, & can import it to OO calc, but I'm not happy with the formatting options.
I tried to access it via writer, but writer seems to only access fields from the dbase package, and I can't find how to import data to the dbase either from csv or from calc. and just to make things interesting, the csv file uses special characters like " and ' (it's the result of font-mapping) so I have to use custom delimiters.

1 how do I access spreadsheet (CALC) data in OO writer?
2 how do I import data from other sources (csv files in particular) to OO base?
3 how do I define custom delimiters?

Question information

English Edit question
Ubuntu Edit question
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Tom (tom6) said :

In Windows with M$ Office i often used to go through the text-editor to use 'Find and Replace' to get rid of odd characters or make them something that i could decipher later.

Surely once either Writer or Base is opened it allows you to Open a new file and then easy to make the pop-up box see 'All Files' and just click on the csv. Otherwise i'd be hunting for an 'Import' option in File menu or in Edit or Tools. Doesn't right-clicking the file allow you to 'Open with ... (other application)' which then gives a range of insane suggestions but also useful ones?

Also have you tried asking in BugZilla and tried a google search?
If you do find the answer please let us know in here because i'm keen to know.

Good luck and have fun
Regards from
Tom :)

Revision history for this message
treetaxi (treetaxi) said :

M$ doesn't handle things like '%s/\t/\n/g' very well, (yay linux), and replacement wouldn't work anyway, because the odd characters are there as a result of font-mapping.

Explanation: I use a specialty font (in this specific case an astrology font) which maps special purpose glyphs to standard ascii -- like wingdings; for instance a capital N will look like an Aquarius sign, and if you want to print the symbol for an opposition (aspect) you would use char(34) -- the double quote(")

I solved this problem by installing fontforge (yay synaptic) and creating my own font simply by copy/pasting the glyphs from the old font to a re-mapped version -- now the 'opposition' glyph is mapped to the 'g'.

As for importing data, opening a .csv file activates ooCalc , pasting data into the table puts it all into one record/field, and there is NO menu item or help-file mention.

Luckily, a Google search led me to which tells about the INEXPLICABLY UN-DOCUMENTED feature that -with a ooDatabase open, right-clicking in the 'tables' area opens up a "paste" dialogue which allows you to either create a new table or append data to an existing table from data currently in the clipboard. (preferably copied from ooCalc, haven't tried other sources yet).

On the evidence of the look of some of the error messages, I believe that a basic 'bugaboo'
about ooDatabase is that it's a heavily disguised sql engine, creating unseen sql statements on the basis of input from a gui environment, and so it's subject to restrictions that a non-dbaser would be unaware of.

I guess I should re-define my question here to "how do you make a dynamic document in ooWriter?" That should be possible from my first question :"1 how do I access spreadsheet (CALC) data in OO writer?" but there may be some other approach I'm not aware of.

Revision history for this message
DrewJensen (atjensen) said :

HMMM - I don't think there is any disguise involved. Base is a front end to a bunch of different database stores - As a convienence the HSQLdb database eninge is included witht he package and is what is used when you create an embedded database. ( ie - if you just create a default Base file) HSQLdb is an SQL engine, yes.

OK - but for your specific questions.

(Before I go any further you might want to drop by the documentation site
and check our the user manuals...)

"how do I access spreadsheet (CALC) data in OO writer?"
 - Copy / Paste is one way.
 - Create a Base database that uses the Calc file as it's data store, with:
In the dialog choose 'Connect to existing database'
In the drop down for database type choose "Spreadsheet"
Browse to your spreadsheet file, save the file.

Now in Writer use the F4 key to display the datasource window.
Select your database (the one that reads from the Calc file)
Each sheet in the file will be displayed as a table.
You can drag the entire table to the writer document, our you can create queries and drag them to it, or you can display a dataview grid and drag individual rows to the's covered in the manual.

"how do I import data from other sources (csv files in particular) to OO base?"

Well, here you have found one of the weaknesses of Base quite honestly. But there is a nice feature in the HSQLdb engine that will allow you to link a table to an external CSV file. For this feature you would be wise to check out the HSQLdb documentation found at
Look at the section on TEXT TABLES. ( if you have specific questions on this section let me know here and I will try to help)

"how do I define custom delimiters?"
Again there is not a good answer in Base proper but the HSQLdb engine does allow this and the documentaiton will cover the specifics - not sure it will handle what you want, but then again it sounds as if you have this handled already.



Revision history for this message
DrewJensen (atjensen) said :

oops - wrong address on the documentaiton site - try this instead


Revision history for this message
Tom (tom6) said :

If you are still having trouble with this then please post it as a new question. Only the most recent questions tend to get looked at so posting/reposting a question just before america arrives online gives the best chance of getting a good few answers.

If the problem has been resolved then please follow the link to the forum thread and mark it as Solved.

Good luck and many regards from
Tom :)

Can you help with this problem?

Provide an answer of your own, or ask treetaxi for more information if necessary.

To post a message you must log in.