LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 09-17-2009, 03:02 PM   #1
kaplan71
Member
 
Registered: Nov 2003
Posts: 718

Rep: Reputation: 39
Importing a csv file into a mysql database


Hi there --

I recently created a database with one table in it. The table, in turn has three fields. Listed below are the characteristics of the fields:

Quote:
Field name Type Allow nulls? Key
Backup varchar(50) No Primary
TapeID varchar(20) No None
Description varchar(50) Yes None
I want to import into the database a csv file containing the pertinent information. An excerpt of the file is the following:

Quote:
LTO-4 RadOnc Servers DQH137L4 Radiation Oncology Servers
LTO-4 Research DQH141L4 Radiation Oncology Research Systems
LTO-4 Xio Systems DQH138L4 Xio Workstations
I ran a SQL command via the Webmin utility to import the csv file into the database, and there were no error messages displayed. However, when I try to view the data, there is none to be seen.

The version of MySQL that is being used here is 5.0.45. Does anyone have an idea as to why this is occurring, and how I can correct it? Thanks.
 
Old 09-17-2009, 03:07 PM   #2
forrestt
Senior Member
 
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288

Rep: Reputation: 99
There aren't any commas in the excerpt, so how is this a comma separated value (csv) file?

Forrest
 
Old 09-17-2009, 03:41 PM   #3
pliqui
Member
 
Registered: Feb 2007
Location: Caracas, Venezuela
Distribution: Debian x64
Posts: 156

Rep: Reputation: 17
CVS File

Example of a CVS file

Code:
Field	Type	
id	int(11)	
income  varchar(20)
time	int(11)
rate	double
min_ammount	int(11)
max_ammount	double
fc	int(11)
Code:
"2";"2.800,1 - 5.474";"30";"6.61";"0";"214055.75";"25"
"3";"5474,1 - 7.000";"30";"8.55";"0";"271858.95";"30"
"4";"7.000,1 - 10.120";"25";"10.5";"0";"321548.25";"30"
"5";"10.120,1 - 16.560";"25";"12.44";"0";"457509.38";"30"
"6";"16.560,1 - 23.000";"20";"14.39";"0";"542477.17";"30"
"1";"0 - 2.800";"30";"4.66";"0";"108477.55";"20"
Hope it helps
 
Old 09-17-2009, 04:04 PM   #4
forrestt
Senior Member
 
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288

Rep: Reputation: 99
Actually, that is a semi-colon separated value file (and I think you meant csv, not cvs which is a version control system). Also, int's and double's wouldn't have quotes. A cvs, or comma separated value, file would look like:

Code:
2,"2.800,1 - 5.474",30,6.61,0,214055.75,25
3,"5474,1 - 7.000",30,8.55",0,271858.95,30
4,"7.000,1 - 10.120",25,10.5,0,321548.25,30
5,"10.120,1 - 16.560",25,12.44,0,457509.38,30
6,"16.560,1 - 23.000",20,14.39,0,542477.17,30
1,"0 - 2.800",30,4.66,0,108477.55,20
Of course, the commas in the second field may cause issues.

HTH

Forrest
 
Old 09-17-2009, 04:14 PM   #5
pliqui
Member
 
Registered: Feb 2007
Location: Caracas, Venezuela
Distribution: Debian x64
Posts: 156

Rep: Reputation: 17
Yes, it's a semi-colon format, didn't change it in phpmyadmin when i did the export, but is the same logic.

And sorry, was CSV not CVS, typo there . I'm from venezuela so format number here is xxx.yyy,zzzz, meaning like one thousand would be 1.000,00

Last edited by pliqui; 09-17-2009 at 09:38 PM. Reason: typo
 
Old 09-17-2009, 09:03 PM   #6
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,247

Rep: Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025Reputation: 2025
@forrest
Quote:
A cvs (!), or comma separated value, file would look like:
What's that quote about correcting others for a mistake you also make
(do as I say, not as I do, iirc)

Anyway, to be serious:
@pliqui : show us a few lines of the actual(!) data file, the SQL cmd/msgs and use a desc cmd on the table.
If you had no errors(!), but no data appears, I'd say you used the wrong table name or schema (db) name.
 
Old 09-18-2009, 07:19 AM   #7
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
pliqui is not the one who posted the question. I think he/she only tried to explain

I admit, I was also a bit confused
 
Old 09-18-2009, 08:29 AM   #8
forrestt
Senior Member
 
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288

Rep: Reputation: 99
chrism01, I guess typing cvs and csv so many times made my fingers confused (what's that thing about bedeg aale to rsed sasefkeig as ladg as tke fiast and ladt leasdrs are corsdesct?)

Anyway, pliqui isn't the one w/ the question or the csv (hey, I got it right that time) file, kaplan71 is. However, (s)he doesn't seem to be interested in the thread, so the entire exercise seems moot at this point.

Forrest
 
Old 09-18-2009, 09:45 AM   #9
kaplan71
Member
 
Registered: Nov 2003
Posts: 718

Original Poster
Rep: Reputation: 39
Hi there --

Sorry about the delay in getting back to you all. I am definitely interested in the thread, so if I was not prompt in replying, it was completely accidental.

I was using a web interface to do the job, and that did not seem to work.

The next step was to try the command listed below:

Code:
load data local infile '/home/ahk/August2009-1.csv' into table offsite_archive
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(Backup, TapeID, Description);
The end-result was the following:

Quote:
Failed to execute SQL : SQL load data local infile '/home/ahk/August2009-1.csv' into table offsite_archive fields terminated by ',' enclosed by '"' lines terminated by '\n' (Backup, TapeID, Description); failed : File '/home/ahk/August2009-1.csv' not found (Errcode: 2)
I am not readily with SQL commands, so I am guessing there is an error with my syntax.
 
Old 09-18-2009, 11:01 AM   #10
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Be aware that the filename is case sensitive. Further, because you're using local, the mysql client looks on the machine on which it's running for the file and it needs to be able to access it (permissions?).

Last edited by Wim Sturkenboom; 09-18-2009 at 11:02 AM.
 
Old 09-18-2009, 11:30 AM   #11
kaplan71
Member
 
Registered: Nov 2003
Posts: 718

Original Poster
Rep: Reputation: 39
Hi there --

I have made some progress. Once I changed the permissions on the file, I ran the commands again. This time, the first line of the file was imported into the database, but not the remaining lines.

Just to recap: The file is saved as a csv file, and can be opened within Microsoft Excel, or OpenOffice Spreadsheet. Any ideas? Thanks.
 
Old 09-18-2009, 12:08 PM   #12
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
The lines might not be terminated by '\n'. So it reads the whole file as one line and there's only space for a limited number of fields and the rest is ignored.
 
Old 09-18-2009, 01:20 PM   #13
kaplan71
Member
 
Registered: Nov 2003
Posts: 718

Original Poster
Rep: Reputation: 39
Hi there --

The removal of the "lines terminated by '\n'" code made the difference.

Thanks again to everyone for the help.
 
Old 09-18-2009, 06:13 PM   #14
pliqui
Member
 
Registered: Feb 2007
Location: Caracas, Venezuela
Distribution: Debian x64
Posts: 156

Rep: Reputation: 17
Sorry i couldn't answer before but had some production problems.

I'm at home right now and don't have the desc of the table, but what i did was an export of the table with phpmyadmin and saved the file as csv. Phpmyadmin allows to change the paramater for the separator, so could be ',', ';','|', etc. the default one is ';'.

But glad you could resolve your problem.

Isaac Abouganem
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Address CSV file Into Thunderbird Rog3236 Linux - Newbie 1 04-18-2009 10:52 AM
PHP Script for Importing vCards into a MySQL Database? amocjr Linux - Software 1 01-20-2007 03:54 PM
Problem importing database back to MySql shubb Programming 3 11-29-2005 01:36 PM
MySQL: Importing a database from an XML file concoran General 0 09-08-2003 02:58 AM
Importing FreeBSD mysql database to RedHat Linux 7.3 RKris Linux - Software 6 05-06-2003 04:07 AM


All times are GMT -5. The time now is 08:41 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration