Copy/paste Calc info into Data

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Copy/paste Calc info into Data

Postby eNG1Ne » Sun Mar 28, 2010 11:54 am

So ... I have a spreadsheet, and I have a new database with a table containing a primary key plus a column for each column in the spreadsheet. Following the Helps, I select and copy the information I want to import from Calc into Data, then go to the table and select Edit > Paste

No sign of any dialogue, just an attempt to paste all (several hundred) spreadsheet cells into the first field of the first record. Is this enough to help someone tell me what I'm doing wrong?

Thanks in advance.
OOo 3.0.X on Ubuntu 10.04 and (also-ran) XP, Libre Office on OS X Snow Leopard
25 years' experience as technical author not always enough ...
eNG1Ne
 
Posts: 60
Joined: Fri Oct 31, 2008 5:26 pm

Re: Copy/paste Calc info into Data

Postby r4zoli » Sun Mar 28, 2010 1:03 pm

What OS and, what OOo version?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Copy/paste Calc info into Data

Postby Villeroy » Sun Mar 28, 2010 2:23 pm

Register your database (Tools>Options...Base>Databases).
In Calc hit F4 and drag the range selection onto the "Tables" icon (no, not a particular table unfortunately) or try a normal copy&paste
You can also drag the range onto the tables icon in a database window without registration.

The import wizard pops up where you specify the name of the target table and that you want to append data.
If your input data have the right order of columns, that's all. If you want to reorder or skip columns, do so.
In OOo3.2 you can drag data without column label, in previous verions a column label is always assumed.
When some error message pops up due to invalid/incomplete/duplicate rows (quite likely with spreadsheet data) you can tell it to continue the import which will skip rows.

A set of formulas I use at the bottom of an import range:
Code: Select all   Expand viewCollapse view
NULL?   =COUNTA(P$2:P$59654)<ROWS(P$2:P$59654)
Entirely numeric?   =COUNT(P$2:P$59654)=COUNTA(P$2:P$59654)
Entirely textual?   =COUNT(P$2:P$59654)=0
Min number   =MIN(P$2:P$59654)
Max number   =MAX(P$2:P$59654)
Max Text Length   {=MAX(LEN(P$2:P$59654))} {entered as array formula}

X2 =countif(P$2:P$59645;$P2) counts occurences of this rows value in P in the entire column. Should be 1 for all cells in a column of unique values (but mind the horrible Calc options when you try to count text).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy/paste Calc info into Data

Postby eNG1Ne » Fri Apr 02, 2010 1:56 pm

I'm cursed ... software hates me ... sniff

Yes, I selected the content in Calc, hit F4 and dragged it on to the Tables icon that suddenly appeared. The import wizard appeared, as you'd said it would, and I set up the columns ‒ nothing fancy, just replacing spaces in column names with underscores and shortening the default length on a few var(char) columns.

"Insert statement too long!" said the wizard, without farther explanation. But I clicked on [Continue] anyway, and then tried to look at the data I'd just imported. When the cursor is actually in a field, then I can see just one field's worth of data, white on blue; otherwise, all I see is an empty grid. Queries that ought to return twenty or thirty entries return nothing.

Might just go back to MySQLAdmin, but I had been looking forward to integration and OpenOffice.Base :-{
OOo 3.0.X on Ubuntu 10.04 and (also-ran) XP, Libre Office on OS X Snow Leopard
25 years' experience as technical author not always enough ...
eNG1Ne
 
Posts: 60
Joined: Fri Oct 31, 2008 5:26 pm

Re: Copy/paste Calc info into Data

Postby Villeroy » Fri Apr 02, 2010 2:59 pm

Just one minute ago I finished a lengthy description of the process in the old forum: http://www.oooforum.org/forum/viewtopic ... 218#368218
As always, this is a question of data processing by means of a computer. It is completely impossible to help without the faintest idea about the data you are trying to move from a non-structure (heap of sheet cells) into the fields of a database.
Calc knows only 2 different types of columns: number or text (there are no dates nor booleans actually)
A database has some dozend of types to offer, some with different lenghts.

Using MySQLAdmin you could import the spreadsheet data from plain text(csv) facing the same problems when the data do not match the structure of your database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy/paste Calc info into Data

Postby eNG1Ne » Mon Apr 12, 2010 10:09 am

I'll take a look at your new posting. However, I'm still disappointed that integration between two components of the same suite seems a little poor. It ought to be possible to tell Base "import the content of this spreadsheet, using column headers as field names and cell formats as data types", so I can then tidy things up by hand. I do have a fair idea of consistent structure; the only thing I can't tell is how the import will handle empty cells.

When Base complains "Insert statement too long!" after creating a statement itself ... this is not encouraging!

Yes, I can quite happily handle queries and exports in MySQLAdmin: but I wanted to benefit from the more user-friendly forms interface that Base seems to offer.
OOo 3.0.X on Ubuntu 10.04 and (also-ran) XP, Libre Office on OS X Snow Leopard
25 years' experience as technical author not always enough ...
eNG1Ne
 
Posts: 60
Joined: Fri Oct 31, 2008 5:26 pm

Re: Copy/paste Calc info into Data

Postby Villeroy » Mon Apr 12, 2010 11:13 am

Pushing the round thing into the square can not be easy. Almost every "organically grown" spreadsheet with collections of data is inconsistent. A spreadsheet has number, error and text as the only 3 types of data and each single cell can take any type independent from the column or row. There are no fields, not even tables since any rectangle of cells may be organized like a table -- or not. This "anarchy" is quite the opposite of a database.
Base data do import and link easily from a database to the spreadsheet as long as the column types are numeric or textual (no binary data). Calc has no problem with the strict table design of a row set.

It ought to be possible to tell Base "import the content of this spreadsheet, using column headers as field names and cell formats as data types", so I can then tidy things up by hand.

No, you can not tidy things by hand since no database accepts untidy data. You've got to do this in a spreadsheet and I provided a set of formulas and an add-on to help you with that.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 3 guests