Groot text bestand opslaan in Database
Ik heb een vraag,
Ik zit met een text bestand wat vrij groot is die opgelagen moet worden in de database.
Maar voordat dit gebeurt moeten er eerst een aantal andere dingen verandert worden.
De text file bestaat uit dit
Code (php)
1
2
2
6 II_WEA_AXE_RODNEY IDS_PROPITEM_TXT_000124 1 1 IK1_WEAPON IK2_WEAPON_DIRECT IK3_AXE JOB_VAGRANT 1 = = 1200 8000000 100 180 HD_ONE = PARTS_RWEAPON = 0 = = 9 9 1 1 0 = = 25 33 _NONE 0 0 0 0 0 = WT_MELEE_AXE AS_DIAGONAL AS_VERTICAL AS_HORIZONTAL AS_THRUST = = = = = 0.06 = AR_SHORT = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = XI_HIT_SWORD01 = = = = = = = = = WEAPON_GENERAL = = = = = = 0 0 0 0 0 0 = = = = = = = = = = = = 9 = SND_PC_DMGSWDB SND_PC_DMGSWDC """itm_WeaAxeCurin.dds""" 0 """""" IDS_PROPITEM_TXT_000125 9 300 0 0 1 0 0 0 1 0 2 = = = = = = = = = = 1 1 1 1 1 0 ITEM_GRADE_NORMAL 1 TYPE1_WEAPON TYPE2_WEAPON_ONEAXE 1 0 1 1
6 II_WEA_AXE_MORROW IDS_PROPITEM_TXT_000128 1 1 IK1_WEAPON IK2_WEAPON_DIRECT IK3_AXE JOB_VAGRANT 1 = = 3300 8000000 100 180 HD_ONE = PARTS_RWEAPON = 0 = = 13 13 1 1 0 = = 31 39 _NONE 0 0 0 0 0 = WT_MELEE_AXE AS_DIAGONAL AS_VERTICAL AS_HORIZONTAL AS_THRUST = = = = = 0.06 = AR_SHORT = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = XI_HIT_SWORD01 = = = = = = = = = WEAPON_GENERAL = = = = = = 0 0 0 0 0 0 = = = = = = = = = = = = 13 = SND_PC_DMGSWDB SND_PC_DMGSWDC """itm_WeaAxeSteel.dds""" 0 """""" IDS_PROPITEM_TXT_000129 13 300 0 0 1 0 0 0 1 0 2 = = = = = = = = = = 1 1 1 1 1 0 ITEM_GRADE_NORMAL 1 TYPE1_WEAPON TYPE2_WEAPON_ONEAXE 1 0 1 1
6 II_WEA_AXE_MORROW IDS_PROPITEM_TXT_000128 1 1 IK1_WEAPON IK2_WEAPON_DIRECT IK3_AXE JOB_VAGRANT 1 = = 3300 8000000 100 180 HD_ONE = PARTS_RWEAPON = 0 = = 13 13 1 1 0 = = 31 39 _NONE 0 0 0 0 0 = WT_MELEE_AXE AS_DIAGONAL AS_VERTICAL AS_HORIZONTAL AS_THRUST = = = = = 0.06 = AR_SHORT = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = XI_HIT_SWORD01 = = = = = = = = = WEAPON_GENERAL = = = = = = 0 0 0 0 0 0 = = = = = = = = = = = = 13 = SND_PC_DMGSWDB SND_PC_DMGSWDC """itm_WeaAxeSteel.dds""" 0 """""" IDS_PROPITEM_TXT_000129 13 300 0 0 1 0 0 0 1 0 2 = = = = = = = = = = 1 1 1 1 1 0 ITEM_GRADE_NORMAL 1 TYPE1_WEAPON TYPE2_WEAPON_ONEAXE 1 0 1 1
zoals jullie zien zit er tussen elke getal/text een tab tussen.
Deze moet dus eerst ge replaced worden. Ik weet dat dit kan met str_replace dit is dan ook geen probleem.
Daarna moet elke regen in een array komen maar ook elke getal/woord na een ,
Dus het moet er uiteindelijk zo uit komen te zien
[1] => [1] = 6 [2] = II_WEA_AXE_RODNEY etc en voor de nieuwe regel
[2] => [1] = 6 [2] = II_WEA_AXE_MORROW etc
Hoe kan ik dit het beste doen.
Met vriendelijke groet,
Code (php)
Zo kan je elke kolom (want dat zijn het, neem ik aan?) gewoon benoemen ;)
Met explode(PHP_EOL, $string) kan je elke regel afwerken.
Gewijzigd op 07/12/2014 10:47:03 door Eddy E
Code (php)
1
LOAD DATA INFILE 'pad/naar/bestand.csv' INTO tablenaam
Gaat stukken sneller
Ger van Steenderen op 07/12/2014 14:19:59:
Gaat stukken sneller
Inderdaad en ook met deze loader kan je allerlei gewenste transformaties doen. Code voorbeeld:Code (php)
1
LOAD DATA INFILE 'pad/naar/bestand.csv' INTO tablenaam
Gaat stukken sneller
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> LOAD DATA LOCAL INFILE 'dbip-country-2014-11.csv'
-> INTO TABLE ip_country
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> IGNORE 0 ROWS;
Query OK, 238658 rows affected (44.99 sec)
Records: 238658 Deleted: 0 Skipped: 0 Warnings: 0
-> INTO TABLE ip_country
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> IGNORE 0 ROWS;
Query OK, 238658 rows affected (44.99 sec)
Records: 238658 Deleted: 0 Skipped: 0 Warnings: 0
Gewijzigd op 07/12/2014 14:53:35 door Aad B