• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6186
  • Last Modified:

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
0
markej
Asked:
markej
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now