url: http://paste.minad.de/index.php?nid=404
Notizname: - von: - Sprache:

  1. #!/usr/bin/php -f
  2. <?php
  3. // Einstellungen
  4. $tmpDir = 'wetter';
  5. $pdoString = 'mysql:host=127.0.0.1;dbname=wetterdaten';
  6. $dbUser = 'dwd';
  7. $dbPass = 'dwd123';
  8.  
  9. // prüfe übergebenen Dateinamen
  10. if ($argc < 2 || substr($argv[1], -4) != '.zip') {
  11. die("eine .zip Datei wird als 1. Paramter erwartet.\n");
  12. } else {
  13. printf('Analysire Archiv: ' . $argv[1] . "\n");
  14. }
  15.  
  16. if (!file_exists($argv[1])) {
  17. die("Datei nicht gefunden: " . $argv[1] . "\n");
  18. }
  19.  
  20.  
  21. // Tempdir leeren/erzeugen
  22. $tmpDir = '/tmp/' . $tmpDir . '/';
  23. if (file_exists($tmpDir)) {
  24. system("rm -rf " . escapeshellarg($tmpDir));
  25. }
  26. mkdir($tmpDir);
  27.  
  28.  
  29. // Ziparchiv ins Temporäreverzechnis entpacken
  30. $zip = new ZipArchive();
  31. $zip->open($argv[1]);
  32. $zip->extractTo($tmpDir);
  33.  
  34.  
  35. // Databank verbinden
  36. $db = new PDO($pdoString, $dbUser, $dbPass, array(PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE));
  37.  
  38.  
  39. // Ordner analysieren
  40. $dir = opendir($tmpDir);
  41. while ($fileName = readdir($dir)) {
  42. if (substr($fileName, 0, 1) == '.' || substr($fileName, -4) != '.txt') {
  43. continue;
  44. }
  45. printf(" Analysiere Datei: %s - ", $fileName);
  46.  
  47. $fileContent = file($tmpDir . $fileName);
  48. if (strpos($fileContent[0], 'Stations_ID, Mess_Datum, Qualitaets_Niveau, Struktur_Version, LUFTTEMPERATUR,REL_FEUCHTE,eor') !== FALSE) {
  49. // Historische Messwerte einlesen
  50. printf("Format: historisch Terminwerte\n");
  51. $csv = array_map('getCsvComma', $fileContent);
  52. unset($csv[0]);
  53.  
  54. printf(" Erzeuge Import-File\n");
  55. $fh = fopen($tmpDir . 'importfile', 'w');
  56. foreach ($csv as $line) {
  57.  
  58. unset($line[2]);
  59. unset($line[7]);
  60. array_unshift($line, 0);
  61. $line = array_map('trim', $line);
  62. fputcsv($fh, $line, ';');
  63. }
  64.  
  65. printf(" starte Import\n");
  66. /* load data infile */
  67. $db->query("LOAD DATA LOCAL INFILE '" . $tmpDir . "importfile' INTO TABLE temp_hum
  68. FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
  69. ;");
  70. /* */
  71.  
  72. printf(" Import beendet\n");
  73. } elseif (strpos($fileContent[0], 'Stations_id; Stationshoehe; Geogr.Breite; Geogr.Laenge; von_datum; bis_datum; Stationsname;') !== FALSE) {
  74. // Stationsdaten einlesen
  75. printf("Format: Stationsdaten\n");
  76. $csv = array_map('getCsvSemi', $fileContent);
  77. unset($csv[0]);
  78.  
  79. printf(" Erzeuge Import-File\n");
  80. $fh = fopen($tmpDir . 'importfile', 'w');
  81. foreach ($csv as $line) {
  82. $newLine = array(0, $line[0], iconv('ISO-8859-1','UTF-8',$line[6]), $line[1], $line[2], $line[3], $line[4], $line[5]);
  83. $line = array_map('trim', $newLine);
  84. fputcsv($fh, $line, ';');
  85. }
  86.  
  87. printf(" starte Import\n");
  88. /* load data infile */
  89. $db->query("LOAD DATA LOCAL INFILE '" . $tmpDir . "importfile' INTO TABLE stations
  90. FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'
  91. ;");
  92.  
  93. /* */
  94.  
  95. printf(" Import beendet\n");
  96. } elseif (strpos($fileContent[0], 'Stations_ID; Mess_Datum; Qualitaets_Niveau; Struktur_Version; LUFTTEMPERATUR;REL_FEUCHTE;eor') !== FALSE) {
  97. // Aktuelle Messwerte einlesen
  98. printf("Format: aktuelle Terminwerte\n");
  99. $csv = array_map('getCsvSemi', $fileContent);
  100. unset($csv[0]);
  101.  
  102. printf(" Erzeuge Import-File\n");
  103. $fh = fopen($tmpDir . 'importfile', 'w');
  104. foreach ($csv as $line) {
  105.  
  106. unset($line[2]);
  107. unset($line[7]);
  108. array_unshift($line, 0);
  109. $line = array_map('trim', $line);
  110. fputcsv($fh, $line, ';');
  111. }
  112.  
  113. printf(" starte Import\n");
  114. /* load data infile */
  115. $db->query("LOAD DATA LOCAL INFILE '" . $tmpDir . "importfile' INTO TABLE temp_hum
  116. FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
  117. ;");
  118. /* */
  119.  
  120. printf(" Import beendet\n");
  121. } else {
  122. printf("Format nicht erkannt!\n"
  123. );
  124. }
  125.  
  126. // das import-File löschen
  127. if (file_exists($tmpDir . 'importfile')) {
  128. unlink($tmpDir . 'importfile');
  129. }
  130. }
  131.  
  132. // Hilfsfunktionen
  133. function getCsvSemi($data) {
  134. return str_getcsv($data, ';');
  135. }
  136.  
  137. function getCsvComma(
  138. $data) {
  139. return str_getcsv($data, ',');
  140. }
  141.  
  142. // INSERT IGNORE INTO temp_hum VALUES(0, 107, 19901010, 5, 24, -8, 23);
  143.  
  144. // CREATE TABLE stations (stations_id int not null auto_increment primary key, stations_number int not null, stations_name varchar(100) not null, height int not null, geo_lat double not null, geo_long double not null, from_date int not null, to_date int null) ENGINE=inodb;
  145. // ALTER TABLE stations ADD UNIQUE `unique_index` (`stations_name`, `from_date`, `to_date`);
  146.  
  147. // CREATE TABLE temp_hum (temp_hum_id int not null auto_increment primary key, stations_id int not null, mdate int not null, qniv int not null, struct_version int not null, air_temp double null, rel_hum double null) ENGINE=inodb;
  148. // ALTER TABLE temp_hum ADD UNIQUE `unique_index` (`stations_id`, `mdate`);
  149.  
  150.  
  151. // normal in 1min: 28.5k
  152. // prepared in 1min: 28.5k
  153. // load infile ist theee shit!
Pastelog:
erstellt 2014.11.24 19:25:49
Hits
529




© 2009-2010 rellig