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
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
Try using PHPMySQLAdmin, there is an import feature that allows you to directly import your CSV file
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.
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.
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 :)
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 :)
ASKER
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
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
> 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);
---
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);
---
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ",";
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 ",";
ASKER
Thanks for all your help leannonn and everyone else for their suggestions
Mark
Mark
I'm glad I could help :)