[Solved ]Importing part of a .csv file

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

[Solved ]Importing part of a .csv file

Postby trinite » Wed Nov 14, 2012 10:31 am

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
Last edited by trinite on Thu Nov 15, 2012 2:26 pm, edited 1 time in total.
Open Office 3.2 - Linux RedHat
trinite
 
Posts: 4
Joined: Wed Nov 14, 2012 10:06 am

Re: Importing part of a .csv file

Postby Villeroy » Wed Nov 14, 2012 11:59 am

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).
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.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing part of a .csv file

Postby trinite » Wed Nov 14, 2012 3:39 pm

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:
1yo2kx.png
1yo2kx.png (8.61 KiB) Viewed 6256 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.
2na1tz6.png

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.
Last edited by Hagar Delest on Wed Nov 14, 2012 10:37 pm, edited 1 time in total.
Reason: pic embedded.
Open Office 3.2 - Linux RedHat
trinite
 
Posts: 4
Joined: Wed Nov 14, 2012 10:06 am

Re: Importing part of a .csv file

Postby Villeroy » Wed Nov 14, 2012 4:35 pm

Why don't you give a try? You should delete the empty second row.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing part of a .csv file

Postby trinite » Wed Nov 14, 2012 4:43 pm

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.
Open Office 3.2 - Linux RedHat
trinite
 
Posts: 4
Joined: Wed Nov 14, 2012 10:06 am

Re: Importing part of a .csv file

Postby Villeroy » Wed Nov 14, 2012 4:52 pm

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.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Importing part of a .csv file

Postby trinite » Thu Nov 15, 2012 2:28 pm

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! ;)
Open Office 3.2 - Linux RedHat
trinite
 
Posts: 4
Joined: Wed Nov 14, 2012 10:06 am


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest