0

How do I populate data (.csv files) into MySql (Windows 5.1) without using the load data command?

Improve this question
4
  • 1
    Are you supposed to write a program which parses the file and then executes INSERT statements for each line? Commented Oct 9, 2010 at 23:35
  • @RyanHennig: No, he's supposed to write a program that parses the file into INSERT statements and writes them to an SQL script. Then he's supposed to execute the script. Commented Oct 10, 2010 at 1:42
  • It would be hilarious to use LOAD DATA to populate the database, and then mysqldump to construct the SQL script. Commented Oct 10, 2010 at 2:51
  • ...and that's what the code monkey did. *Sigh* stackoverflow.com/questions/3899081/… Commented Oct 10, 2010 at 21:06

3 Answers 3

Reset to default
1

Are they trying to get you to learn a scripting language?
If so, how about Python...

I just tested that the following code does what you require.
Please note: You'll need to download the 3rd-party MySQLdb module.

import csv
import MySQLdb

def populate_mysql_db():
    conn = MySQLdb.connect(host="localhost",
        user="myusername",passwd="mypassword",db="mydb")
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS mytable 
        (mycol1 varchar(100), mycol2 integer);
        ''')
    csv_reader = csv.reader(open('mycsvfile.csv', 'rb'), delimiter=',')
    for line in csv_reader:
        c.execute('''INSERT INTO mytable (mycol1, mycol2) 
            VALUES (%s, %s)''', (line[0],line[1]))
    c.close()
    conn.close()

if __name__ == "__main__":
    populate_mysql_db()
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

well, I dont need to use any scripting language. I have the data and I just need to make a file populatedb.sql which is a text file.The TA said to me " I'm sure you can think of some clever trick so that you don't have to actually type all of the statements manually into the populate.sql file you need to submit. In fact, once you have made the necessary populate.sql file, you can pass it to the command line .So you don't need to type everything manually into the sql command line either.
@shilps, since it's tagged "homework" and you're working with MySQL, I think we can assume that at least some programming is involved? What's the class you're taking? What are your learning goals? What does your textbook say?
The text book doesnt say anything regarding this thing. It has nothing to do with my subject topics. I just need to make a file that contains all the data which is present in .csv files. I cannot put it manually because it is going to take along time. So i need some better way to solve it.
@shilps: there are many ways to accomplish your task. Pick one and get it done. Maybe you'll learn a couple of things along the way.
@shilps: "Some clever trick so that you don't have to actually type all of the statements manually" either means a program, a script, or magic.
|
0

Try this:

grant file on *.* to youruser@localhost identified by 'yourpassword';

Improve this answer

Comments

0

What are the tools you have? Any kind of scripting language? Any management interface, like MySQL Admin?

There are a lot of ways to load data into database. But we need to know what tools you have at hand.

EDIT

One of your options is to make a .sql batch file. The quickest to load approach would be using prepared statements. Check below how.

PREPARE massInsert 'INSERT INTO mytable (field1, field2, field3...) VALUES (?,?,?,?...)';
EXECUTE massInsert (value1, value2...);
many lines
...
Improve this answer

2 Comments

use the comments feature to ask questions, not the answers feature
I have to make a populate.sql file that has all tha data. And then I insert that data into sql command line by some commands given in the manual. But how do i make a populate.sql file ? Do i need to use some macros?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.