How do I import CSV data into MySqL

I have and Excel/CSV file and I need to import this into MYSQL.

I use asp.net (VB) version 2.0, I also have MySQL Administrator version 1.2.12

I DO NOT have access to the MySql database file directory it is on a hosted environment and I cannot upload files into the database directory.

I have tried using the MySQL migration tool kit BUT that doesn't like my verified version of java (version 6 update 3) it requires 5.0 update 8 which I can't find (I also not sure if it is necessarily the correct route anyway)

Mark
markejAsked:
Who is Participating?
 
Aleksandar BradarićSoftware DeveloperCommented:
Silly me - if you have text values (as you have), you'll need the quotes:
---
INSERT INTO NewsLetterMembers(name, email)
VALUES("$RegEx1.1", "$RegEx1.2");
---
0
 
duckbertCommented:
Try using PHPMySQLAdmin, there is an import feature that allows you to directly import your CSV file
0
 
SvenTech Lead Web-DevelopmentCommented:
Try using "SQLyog MySQL GUI - Community Edition" to import CSV:
http://webyog.com/en/

It is a freeware tool! I can not tell for sure it can import CSV but I think so. If not you could search for some other MySQL GUI Tools. There are plenty of them in the web.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Aleksandar BradarićSoftware DeveloperCommented:
Go to MySQL Query Browser. In the `Tools` menu, go to `RegEx Text Importer`. Use the `Load Source Text` button to load you CSV file.

Now you have to construct the `Regular Expression`. For most CSV files, it will go like this (e.g. if you have 3 fields separated by `;`):
---
^(.*?);(.*?);(.*?)$
---

Click `Execute RegEx` to test it. If it's OK, on the left (in `Parse Structure`) you should see your data parsed, like:
---
RegEx1
  Matches
    $0 [xx-xx]: value1;value2;value3
    $1 [xx-xx]: value1
    $2 [xx-xx]: value2
    $3 [xx-xx]: value3
---

Now, take a look at the `INSERT INTO` statement at the bottom right. Modify it to correcpond to your table and add the values from the RegEx, e.g.
---
INSERT INTO yourTable(fieldName1, fieldName2, fieldName3)
VALUES($RegEx1.1, $RegEx1.2, $RegEx1.3)
---

You can test it with `Preview`. When all OK you can either `Store SQL to Script` and execute it later from the Browser of from the command line. Or you can execute it right away (if you;re in the correct DB already) by clicking the `Execute SQL` button.

It's a bit tricky when you have no access to the DB, but this should do the job.

Let me know if you run into any problems :)
0
 
markejAuthor Commented:
Thanks for the comments and so quick!

leannonn: I have followed your script and found it very helpful  I have TWO fields to insert and have the following regular expression:

^(.*?),(.*?)$
This gives me the regular expression similar to yours and I then created the insert statement
:-
INSERT INTO NewsLetterMembers(name, email)
VALUES($RegEx1.1, $RegEx1.2)

Which gives me the following error for ALL records and doesn't import:-

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

What am I doing wrong?

Mark

0
 
Aleksandar BradarićSoftware DeveloperCommented:
> What am I doing wrong?

Are you in the correct database? Does the `NewsLetterMembers` table exist? If not you'll have to create it first.

What does it say when you hit the `Preview` button?

Maybe, it's just the missing `;` at the end:
---
INSERT INTO NewsLetterMembers(name, email)
VALUES($RegEx1.1, $RegEx1.2);
---
0
 
ygouthamCommented:
if you can upload the csv file to a particular location where it is accessible to run a query, then read the page

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

LOAD DATA INFILE 'file.csv'   INTO TABLE t1    (column1, @dummy, column2, @dummy, column3) fields terminated by ",";
0
 
markejAuthor Commented:
Thanks for all your help leannonn and everyone else for their suggestions

Mark
0
 
Aleksandar BradarićSoftware DeveloperCommented:
I'm glad I could help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.