EASY: importing excel to mysql

[oziman]

Well, I was using Navicat for ages (old copy from where I used to work) but this solution is much faster:

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.
You can then import it into a MySQL table by running:
load data local infile 'uniq.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)
The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

Originally from - the jackol’s den » Import Excel/CSV into MySQL - Mikhail Esteves

Props to this guy, whoever he is - saved me hours today (something like 100k records from excel to mysql in a few minutes, thanks to DigDB and his trick)

 

[RaginBajin]

That's a pretty good way. I'm a perl guy, so I write a lot of my scripts especially to work with data from a database with perl.

 

[Avalanche]

Do you guys know how to do the reverse? There are some dbases that I need to pull into spreadsheets

 

[audax]

If you have phpmyadmin setup, there is a tab to "Export" any of the tables into varying formats, including CSV.

 

[Stanley]

if anyone happens to know how to import CSV into MySQL through shell please pm me.

 

[Stanley]

Do you guys know how to do the reverse? There are some dbases that I need to pull into spreadsheets

SELECT ... INTO OUTFILE

MySQL AB :: MySQL 5.0 Reference Manual :: 11.2.7 SELECT Syntax

 

[Stanley]

The "LOCAL" clause should only be used if you don't have the FILE privilege.
Getting rid of the LOCAL clause speeds up the query because the MySQL server will read the files directly instead of sending the file's content as part of the query.
To grant the FILE privilege, update the File_priv field in the user table within the main mysql db.


Okay, I'll stop harassing this thread now.

 

[tunde]

i only know of microsoft word.u may teach me this if its a modern technology.

 

[Supergeek]

i only know of microsoft word.u may teach me this if its a modern technology.

Huh?

 

[Scrabbler]

i only know of microsoft word.u may teach me this if its a modern technology.

You have no chance to survive make your time.