Apache OpenOffice Community Forum

Hi,

I have a problem, I'm trying to find out if it's possible to import only part of a .csv file into mySQL without editing it myself.
A small example:

Title1 Title2 Title3

n.n.b. 123 n.n.b.
n.n.b. 456 n.n.b.
n.n.b. 789 n.n.b.
n.n.b. 111 n.n.b.

I want to import all 3 rows, without having to delete the titles myself. Is this possible?

This is because otherwise I have to keep deleting the top row each time shown in this video:
http://www.youtube.com/watch?gl=NL&hl=nl&v=diEwQk4uY14
(10:12)

This is only a small example, I need to import 3 huge excel/calc sheets into a database, but not everything has to be imported.

Thanks in advance

First create your database tables with fields, indices, keys and everything.
Connect a Base document to your database.
Then import raw data from spreadsheet. Copy a cell range in Calc including the field headers, select the icon of the target table in the database window and paste. A wizard pops up where you can map the corresponding field labels to each other.

P.S. Talking about a MySQL database, it should be much easier to let MySQL do the job of importing plain text files (csv).

Villeroy wrote:First create your database tables with fields, indices, keys and everything.
Connect a Base document to your database.
Then import raw data from spreadsheet. Copy a cell range in Calc including the field headers, select the icon of the target table in the database window and paste. A wizard pops up where you can map the corresponding field labels to each other.

P.S. Talking about a MySQL database, it should be much easier to let MySQL do the job of importing plain text files (csv).

This isn't what I meant exactly. Here's what I'm trying to do:

Lets say I have an calc document with 7 different rows: I've converted the calc document to an .csv file however I want only the first 4 Rows (Title 1 to Title 4) in my database. Is it possible to choose which rows you want to import into the database through a wizard because I dont want to do everything by hand since this is a small example and I need to do this with 3 huge documents.

Why don't you give a try? You should delete the empty second row.

Villeroy wrote:Why don't you give a try? You should delete the empty second row.

This is kind of my question, I have several calc documents filled with information I dont need in my database. I dont wan't to delete all that information by hand, I'm trying to figure out how to import only certain rows of a .csv document.

MySQL can do this without the help of a spreadsheet. In Calc you may try to copy the filtered list which should not copy any hidden rows. Under Linux you have also grep, sed, Perl, whatever at hand.

Someone at the MySQL Forums gave me the answer:

Posted by: Barry Galbraith ()
Date: November 14, 2012 02:47PM

Int the refman for LOAD DATA INFILE, about half way down theres a description of how to load some fileds, skip others, and discard some input.

refman says
You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);

So, you probably need something like this.

LOAD DATA INFILE 'my.csv'
INTO my_table
(Title_1, Title_2, Title_3, Title_4, @dummy5, @dummy6, @dummy7)


You'll need to add the FIELDS TERMINATED BY and LINES TERMINATED BY clauses to suit your file.

BTW, a row is one horizontal line in your table. The verticals are fields or columns.

Good luck,
Barry.

Thanks for the responses anyway!