Issue 51904 - Wizard to import table to database
Summary: Wizard to import table to database
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 680m113
Hardware: All All
: P3 Trivial with 123 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
: 56500 59766 67631 67913 (view as issue list)
Depends on: 42464
Blocks:
  Show dependency tree
 
Reported: 2005-07-13 18:07 UTC by aziem
Modified: 2014-01-16 02:54 UTC (History)
9 users (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description aziem 2005-07-13 18:07:35 UTC
There should be a wizard to import tables from various data sources (e.g. .sxc,
.xls, .csv, .dbf) to the new OOo Database component.

For loosely defined data sources (spreadsheets), the wizard would clarify data
types, column names, and the primary key.

I suggest the feature be accessible from File menu->Import or File menu->Get
external data.
Comment 1 marc.neumann 2005-07-22 13:07:23 UTC
Hi,

I reassign this enhancement to the User Experience team for evaluating.

Bye Marc
Comment 2 aziem 2005-09-01 21:18:14 UTC
Here are some reasons how feature would help:

1. To change import properties such as following.  All of these apply to simple
formats such as CSV, but not all apply to more structude formats such as DBF.
a. character set
b. destination (new or existing table)
c. column types (e.g. integer, date, currency)
d. index
e. fixed width or delimiter character (e.g. comma or tab)
f. text qualifer (usually " with CSV)
g. column types
h. column index
i. primary key

2. Import files larger than 65,536 lines (compared to copy from spreadsheet via
clipboard)

3. Import multiple files as multiple tables into one Base file 
Comment 3 chalo 2005-10-17 02:27:03 UTC
I've struggling to import data from a spreadsheet into an existing database... 
Copying and dragging seems to be bugged, so will really appreciate that the 
import wizard is implemented. Suggest to have it working as in Access Database 
but using the connection facilities Base seems to have.
Comment 4 aziem 2005-12-09 14:40:22 UTC
*** Issue 56500 has been marked as a duplicate of this issue. ***
Comment 5 alunroberts 2005-12-21 14:43:04 UTC
Adding a data import button similar to the one in Microsoft Access would greatly
improve the usability of Openoffice Base.

I tried for an hour with Openoffice (and failed) to import a csv file with 87600
rows and 20 columns using the first line in the columns as the field names.
Although after a bit more research I could probably have written a script to
create a text table first then copy the data across to a native Base table my
ordinary users certainly could not do this. As each file we want to import has
different column headers this could become tedious.

MS Access did this task in a couple of minutes, correctly naming the fields and
correctly determining the field data type from the format of the text data (some
of the fields are date / time etc...).

The other alternative would be to remove the restriction on the maximum number
of rows in the OOo Calc component. Some of our data sets have 250000 rows and
1000 columns.
Comment 6 alunroberts 2005-12-21 14:48:02 UTC
Adding a data import button similar to the one in Microsoft Access would greatly
improve the usability of Openoffice Base.

I tried for an hour with Openoffice (and failed) to import a csv file with 87600
rows and 20 columns using the first line in the columns as the field names.
Although after a bit more research I could probably have written a script to
create a text table first then copy the data across to a native Base table my
ordinary users certainly could not do this. As each file we want to import has
different column headers this could become tedious.

MS Access did this task in a couple of minutes, correctly naming the fields and
correctly determining the field data type from the format of the text data (some
of the fields are date / time etc...).

The other alternative would be to remove the restriction on the maximum number
of rows in the OOo Calc component. Some of our data sets have 250000 rows and
1000 columns.
Comment 7 aziem 2005-12-27 14:20:32 UTC
*** Issue 59766 has been marked as a duplicate of this issue. ***
Comment 8 ibarnard 2006-05-03 12:47:07 UTC
Is this not a duplicate of issue 42464?
Comment 9 bert.meersma 2006-05-03 14:23:54 UTC
@ibarnard:
In my opinion this is not a duplicate of issue 42464. That issue is about 
linking an another database into the current database, similar to functionality 
MS Access offers.
This issue is about importing files directly into tables of the current 
database.
Comment 10 ibarnard 2006-05-03 15:53:19 UTC
Yes I understand, but this may be related in the sense that the same subsystem
(if created at any time) will perform both functions. Meaning OOo has to load
the other database before it can import its data - thus having to link the two
temporarily.
By the way this is how MS Access accomplishes both. Why write two sets of code
where one will do?
Comment 11 jondoe 2006-05-03 17:38:30 UTC
There is a difference between a duplicate issue and a dependent issue. 
Duplicate issues come to the same thing.  Resolving issue 42464 will not
automatically resolve this issue.

The point of this issue is to create simple functionality to allow even the most
novice user to be able to import tables from other data sources in one action
without being bothered with explicitly linking and unlinking a data source.

It might be a very good idea to show this issue depending on 42464, but it would
not be a good idea to dismiss this issue once the other has been resolved.
Comment 12 marc.neumann 2006-07-31 14:44:58 UTC
*** Issue 67913 has been marked as a duplicate of this issue. ***
Comment 13 christoph.lukasiak 2006-08-16 16:30:04 UTC
*** Issue 67631 has been marked as a duplicate of this issue. ***
Comment 14 thegoldenear 2007-11-23 16:31:49 UTC
Until this is available, here's how to do this with help from Solveig Haugland:

- Exporting data from an OpenOffice.org Base database:
http://openoffice.blogs.com/openoffice/2007/04/farrrrrr_simple.html. Other
methods seem plausible but are broken.

- Importing data into an OpenOffice.org Base database:
http://searchenterpriselinux.techtarget.com/tip/0,289483,sid39_gci1222186,00.html

What she doesn't make explicit enough is you can paste from a spreadsheet but it
won't work if pasting from an OpenOffice database.

- Copying from one OpenOffice Base databse to another (the following assumes
you've read the documents above):
	- paste the database contents into a blank spreadsheet

	- copy the contents of the spreadsheet

	- paste into the database

(You get prompted for the table name, don't use <table name> use <db
name>.<table name> format)

Pete Boyd
Comment 15 Mechtilde 2007-11-23 16:41:17 UTC
add as CC
Comment 16 irneb 2007-11-26 04:59:09 UTC
Yep, that's a very easy way of doing it ... unfortunately it only works for
small amounts of data due to the restriction on columns & rows in the
spreadsheet. Any well used database soon goes over that boundary (especially the
rows).

Now if you could directly copy data between 2 OOo Base databases (irrespective
of the backbone driver) this would accomplish the task quite easily. I'm just
thinking that placing all the data on the clipboard & then pasting into a new
table or appending, will use a lot of ram whereas a link shouldn't be that
wasteful. The user doesn't need to know that a link is created (even a temporary
link) - this could be done in the background as OOo should be able to notice
that you're importing data from one open database to another open database -
thus no user intervention except for the Ctrl+C and Ctrl+V key presses.

If the "linking" idea can be done as described above, this will make even the
linked tables (as per issue 42464) as seamless as a copy-n-paste operation.
Maybe just say add an option in the Edit menu. E.g. Paste-Special --> Paste as
Link. This is also much closer to what inexperienced users would expect, as most
other programs offer this type of functionality, e.g. Paste a link to a Calc
spreadsheet into a Writer document.
Comment 17 tictactow 2013-11-22 19:36:38 UTC
Running into the need for this now.  Has any progress been made?
Comment 18 tictactow 2013-11-23 13:11:11 UTC
Please ignore previous comment.  Found exactly what I was looking for, but wow is there a need to simplify the usability, either through documentation and/or a clearly marked "Import" button or menu option.
Comment 19 irneb 2014-01-15 07:19:08 UTC
(In reply to tictactow from comment #18)
> Found exactly what I was looking for
Could you possibly point out your solution?
Comment 20 tictactow 2014-01-16 02:52:57 UTC
Import into OOcalc using "file open" or drag and drop, you then get the Import Wizard to parse your data.  Once in Excel format, cut and paste into Base.  Hope this helped.
Comment 21 tictactow 2014-01-16 02:54:32 UTC
Spent ages looking for an "Import" menu option though.  ;-)