[Solved ]Importing part of a .csv file

[trinite]

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

[Villeroy]

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).

[trinite]

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:

1yo2kx.png (8.61 KiB) Viewed 7819 times

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.

[Villeroy]

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

[trinite]

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.

[Villeroy]

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.

[trinite]

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!