populate excel data into mysql tables

I have an excel sheet with data that I need to populate in "organization" and "organization_thematic" tables. I wonder if it can be done by sql script or need to use some programming language, what do you suggest? How to do achieve it.

See the database schema and the excel sheet structure below.

Thanks,
Table: country (structure)
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| countryId   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| countryName | varchar(45)      | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Table: country (data) 
+----------+-------------+
|countryId | countryName | 
+----------+-------------+ 
|1         |USA          |
|2         |Canada       |
+----------+-------------+

Table: thematic (structure)
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| thematicId   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| thematicName | varchar(256)     | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Table: thematic (data) 
+----------+-------------+
|thematicId| thematicName| 
+----------+-------------+ 
|1         | Dogs lovers |
|2         | Cats lovers |
|3         | Fish lovers |
+-+--------+-------------+

Table: organization
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| organizationId     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| fullName           | varchar(45)      | NO   |     | NULL    |                |
| countryId          | int(10) unsigned | YES  | MUL | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

Table: organization_thematic
+------------------------+------------------+------+-----+---------+----------------+
| Field                  | Type             | Null | Key | Default | Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| organizationThematicId | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| organizationId         | int(10) unsigned | NO   | MUL | NULL    |                |
| thematicId             | int(10) unsigned | NO   | MUL | NULL    |                |
+------------------------+------------------+------+-----+---------+----------------+

Excel sheet
+--------------+------------------+-------------------------------------+
| Organization | Country          | Thamtic                             |
+--------------+------------------+-------------------------------------+
| Org1         | USA              | Dogs lover, Cats lovers, Fish lovers|
| Org2         | Canada           | Cat lovers                          | 
+--------------+------------------+-------------------------------------+

Open in new window

phpmysqlcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nasirbestCommented:
1st. you need to convert you excel file into csv in linux "xlhtml" can do that
2nd. import csv file into database table with "LOAD DATA " query

google for more info
0
sushiguruCommented:
The alternative is to write an equation into your excel sheet to create all the INSERT statements, then copy and past into your favourite mySQL client and execute.

If you can upload your xls somewhere I can demo it for you.


Would probably be aacouple of passes through the data to extract the data for each table, get the ids, repopulate them in excel and go again.  The good news is that you only have to do it once, and then it's in the DB forever :)  It also gives you an opportunity to clean the data as you go and see errors before you INSERT into the db, so you only INSERT clean data.

sg.
0
phpmysqlcoderAuthor Commented:
Dear sushiguru, thats very interesting, I uploaded my excel sheet with two records, I would appreciate if you can guide me through your idea of generating the sql insert script from excel. The tables details are included in my original question.

org.xls
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

theGhost_k8Database ConsultantCommented:
0
phpmysqlcoderAuthor Commented:
Thanks theGhost_k8, the challenge, still,  is to update the master tables, especially, the cross table: organization_thematic (see the schema above).
0
theGhost_k8Database ConsultantCommented:
@phpmysqlcoder: in that case you will have to write code, may be a perl script which read through files and insert in to individual tables. mysql_insert_id will help you in maintaining relations.
0
sushiguruCommented:
Have a look at the sheet now - I've written the formula into VBA (allow VBA and ALT+F11 to view code) so it can split your values for you.  This is where you'll need to do a bit of tidy-up - your data (not consistent) and also how you want the SQL to look.

If you need more refinement, let me know.

Cheers,
sg.
org.xls
0
theGhost_k8Database ConsultantCommented:
@phpmysqlcoder:

Below are the queries that should run in sequence.

insert into country (countryName) values('');
insert into organization(fullName,countryId) values('','');
insert into thematic (thematicName) values('');
insert into organization_thematic set organizationId=(select max(organizationId) from organization),thematicId=(select max(thematicId) from thematic);

Check the attached xls. You just need to drag the formula along with the records.
You have to run the queries in sequence.

Check if http://kedar.nitty-witty.com/blog/mysql-stored-procedure-split-delimited-string-into-rows/ may come to rescue for your insertion requirements.

Otherwise you may create separate columns in excel for comma delimited thematicnames.

@sushiguru: I think it'll be difficult to write if else condition for each comma-separated values!?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
File now attached.
org.xls
0
sushiguruCommented:
@theGhost_k8 - naw - once you have a complete set of known values you can define them as an array, or build the array dynamically as you go.  No problem.

Much easier to see with real data...

sg.
0
phpmysqlcoderAuthor Commented:
Thank you @theGhost_k8 and sushiguru.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.