Welcome, Guest. Please login or register.

Author Topic: location-information : import from csv, find/delete double-definitions (NeDiO45)  (Read 2316 times)

Steffen Scholz

  • Newbie
  • *
  • Posts: 48
    • View Profile
Hello community,

for a large and dynamic environment, I was looking for a way to support information in location-table faster and easier than manually fill-in Topology-Loced.php - and found it.  ;)

I did this:

  • 1st - export (from excel) values for region,city,building(and the description for this) in a csv-file ("locations-NeDi-import.csv" in "/var/nedi/") (separated with (3) semicolons).
This looks like this:
Code: [Select]
Region1;;;Description for Region 1
Region1;City1;;Description for City 1(in Region 1)
Region1;City1;Builduing1;Description for Building1 (in City1 in Region 1)
...


  • 2nd - start mysql from commandline (with option --local-infile=1) for example:
Code: [Select]
mysql -u nedi -p nedi --local-infile=1
  • 3rd - execute sql-command for import:
Code: [Select]
LOAD DATA LOCAL INFILE '/var/nedi/locations-NeDi-import.csv' INTO TABLE locations FIELDS TERMINATED BY ';' (region,city,building,comment);
  • 4th - because now maybe duplicates are in location-tables, I used following command to display this:
Code: [Select]
SELECT  A.id AS fordeletion ,A.region,A.city,A.building
 FROM locations AS A
 WHERE A.id NOT IN
 (SELECT MIN(X.id)
  FROM locations AS X
  GROUP BY X.region,X.city,X.building);

  • 5th - to delete the doubles, I used following commands:
create a temporarily table for deletable location-id's:
Code: [Select]
CREATE TEMPORARY TABLE deldoublelocid (id INT,delid INT);
insert ids from all doublets in this table:
Code: [Select]
insert into deldoublelocid(delid) select id FROM locations WHERE id NOT IN (SELECT MIN(X.id) FROM locations AS X GROUP BY X.region,X.city,X.building);
delete all doubles (which id's are saved in the temporary tabe:
Code: [Select]
DELETE FROM locations WHERE id IN (SELECT delid FROM deldoublelocid);
Because the table deldoublelocid are only generated temporarily (and will be deleted by exiting this mysql-session), this commands are optional:
Code: [Select]
delete from deldoublelocid;
Code: [Select]
select * from deldoublelocid;

If anyone has ideas to optimize this - please let me know!
(SQL isn't my native language... - and english,too ;D )

Bye
Steffen
« Last Edit: June 22, 2011, 01:15:22 PM by SteffenS »