5

In MS office Access for instance there is always a way to import Excel data just right in Access ribben labeled Import External file and by following the wizard view, you can easily link to Excel data.

But I don't know whether it is possible in Libreoffice Base to import Calc data. Is there anyway to achieve that? At work most of the time my colleagues sent me files in xlsx so when I want to use them in base I don't know what to do.

I like base because it can deal with large quantity of data unlike Calc or excel.

1

This is possible quite easily. Just set up a database connection to your spreadsheet file:

DB wizard

After clicking Next >>, you will be prompted for the name and path of the spreadsheet file:

DB Wizard 2

| improve this answer | |
  • Thank you very much. That was a very fast respond. I tried and it works pretty nice. – Faska May 25 '12 at 14:28
8

My problem with the answered above method is that it seems creates a read-only database from the spreadsheet file, so you can't actually edit anything. The same problem occurs with data in text files and system address books. This probably makes the imported data less useful than when it was on the spreadsheet, as then primary keys and stuff can't be added, and the tables can't be edited as the options are greyed out due to it being read-only.

I eventually found a method here that works nicely - here's a summary:

  • Create a new database:

enter image description here

  • Create a table in the database with the same fields/columns/whatever as the spreadsheet being imported

enter image description here

  • Select the data in the spreadsheet to be placed in the database - if you include a first line with labels it can be automatically removed - and copy it. The go to the database, right-click on the entry for the table and select 'Paste':

enter image description here

  • A options thing will then appear, and you can use this to import the data into the spreadsheet. You can also select which fields will be copied by pressing 'Next':

enter image description here enter image description here

That should be another way you can import data into a database.

| improve this answer | |
0

There's a WAY easier way. Highlight the fields in calc and select them - do not highlight header fields, just highlight the data. Copy to the clipboard (edit - copy).

Make a backup of your Base file in case something goes wrong. Now open your Base file, but, this is important, do not open the table. Select Tables from the Database area on the left. Now, just click, once, on the table to which you want to import your data, only click one time to select it but don't open it. Right click. Select paste, and a wizard pops up that asks you how to import the data. Follow those instructions and the data will be imported into your table as specified. In the next screen, the wizard will show you the first row of what is being imported and will show you the fields from the table that each imported column will map to, and there you can make any changes so that the columns go into the proper database fields.

So for example, if the database fields are FirstName & Lastname, and the first row of your data is Smith, Tom, the wizard will tell you it's going to map the column containing Smith to FirstName and the column containing Tom to LastName, which is exactly backwards, so swap the field names and press OK. Your data will be imported. Double click on the table and there it will be.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.