[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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

0
phpmysqlcoder
Asked:
phpmysqlcoder
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now