Přidat otázku mezi oblíbenéZasílat nové odpovědi e-mailem Jak převést rozsáhlý *xml soubor do čitelnější formy?

Pro své potřeby dokážu z rozsáhlých tabulek v Excelu vytvořit požadované *xml soubory, třeba pro odesílání na úřady (typicky kontrolní hlášení k DPH).

Zajímal by mě opačný postup, tak dostat data z hotového *xml třeba do Excelu, případně databáze. K jednotlivým *xml souborům existuje i *xsd schéma. Netuším jak (jednoduše) na to, zda je nějaká možnost, jak data z toho vydolovat třeba přímo v Excelu - či je nutné použít jiný (programátorský?) postup. Respektive přesněji: vůbec tomu nerozumím.

Ta data jsou teď poněkud výbušné zboží (náhle se probudili rádoby bojovníci za GDPR, přitom VŠECHNY tyto údaje může KDOKOLIV získat - byť méně pohodlně - už řadu let), vše je k mání tady: https://www.mojedatovaschranka.cz/sds/welcome?part=opendata

Předmět Autor Datum
Tak jsem zkusil ten nejmenší soubor naimportovat do Excelu (2007), menu Data - Načíst externí data -…
L-Core 25.03.2023 10:17
L-Core
Novější Excely s tím nemívají problém, doporučen je samozřejmě 64b Office. Jinak bych to zkusil nai…
touchwood 26.03.2023 10:33
touchwood
Zkusil jsem ten největší soubor do Accessu (2007) dostat, šlo to, ale nenačetlo se to tam celé (jen…
L-Core 26.03.2023 10:43
L-Core
Vypsat data do tabulky v php jde např. takto (vypíše všechny id s tradeName): <table> <tr><th>id</t…
kacikac 26.03.2023 13:14
kacikac
Kdo umí, umí. Kdo neumí, čumí. :-)
L-Core 26.03.2023 15:22
L-Core
Nezkousel jsi to v Google docs nebo MS online?
Jan Fiala 26.03.2023 16:15
Jan Fiala
Nedávno jsem převáděl soubor velký (téměř 2GB) XML soubor do Excelu. Protože výsledek měl více řádků…
NoReg 26.03.2023 18:38
NoReg
Teď když to po sobě čtu, tak vidím, že jsem zapomněl zdůraznit, že po onom mechanickém rozdělení XML…
NoReg 26.03.2023 18:58
NoReg
Neni lepsi pouzit ty webove sluzby, ktere tam maji? Nez stahovat gigove fajly k sobe?
MaSo 27.03.2023 09:02
MaSo
Webové služby ti ale nedají celý seznam k iteraci. Jsou to jen čistě SOAP pro vyhledávání.
tomas.kulhanek 27.03.2023 14:33
tomas.kulhanek
Predpokladam, ze v tom chce vyhledavat, proto to stahuje. A ktomu se daji krasne pouzit ty ws...
MaSo 27.03.2023 17:45
MaSo
Ten web používá GZIP kompresi, tak je to stáhlé hned. Pokud to někdo nechce ukládat na disk, tak je…
kacikac 27.03.2023 18:11
kacikac
Pokud bys to potreboval, napisu ti nejakou malou utilitku, která to přehrne do Access nebo co si řek…
Jan Fiala 27.03.2023 11:56
Jan Fiala
Já to zas tak nepotřebuju, zajímalo mě to spíše teoreticky :) Asi nejjednodušší by byl převod do *cs…
L-Core 27.03.2023 12:23
L-Core
Pozadal jsem chatGPT, předhodil mu jeden node a on mi vratil kod na nacteni a zapis do souboru. Schv…
Jan Fiala 27.03.2023 13:43
Jan Fiala
Načtení, rozparsování toho největšího 1.65 GB xml pfo souboru a uložení do csv mi trvá celkem asi 45…
kacikac 27.03.2023 14:23
kacikac
Pro Excel je lepší TAB delimited. Ušetříš velikost, a Excel to má mnohem jednodušší na parsování než…
Jan Fiala 27.03.2023 15:40
Jan Fiala
Tak ono je to v tom Excelu rychlé i tak. Teď zkouším převod xml do csv tímto skriptem, dělá to valid…
kacikac 27.03.2023 18:18
kacikac
Jen taková poznámka, bez PDO::ATTR_PERSISTENT => true mi to dělalo na AMD 5600G časy OVM 0.5 s, FO 1…
kacikac 27.03.2023 21:14
kacikac
Já bych na toho ChatGPT moc nespoléhal. Viz mé tristní zkušenosti popsané na 3183282-ms-potvrdil-pou…
NoReg 27.03.2023 15:19
NoReg
Jde o kostru. Nemusíš se pak drbat s mechanickym opisovanim nodu. Zbytek si upravis, ale spoustu mec…
Jan Fiala 27.03.2023 15:38
Jan Fiala
Ahoj Honzo (zdraví Paullus), Tobě je hej, umíš programovat, ale co my lamy (ejhle rým). Jinak nemo…
NoReg 27.03.2023 16:05
NoReg
Musel bys vzit 64b verzi PSPadu, tam není omezení na 2GB RAM jako u 32b verze. Ale pracovat s tím by…
Jan Fiala 27.03.2023 16:58
Jan Fiala
Já samozřejmě mám 64b verzi (5.0.7). Ale když chci otevřít ten XML soubor, tak mi to hlásí: Soubor X…
NoReg 27.03.2023 17:09
NoReg
Proverim
Jan Fiala 27.03.2023 18:33
Jan Fiala
Já samozřejmě mám 64b verzi (5.0.7). Ale když chci otevřít ten XML soubor, tak mi to hlásí: Soubor X…
Jan Fiala 28.03.2023 12:16
Jan Fiala
Tím "zprasene UTF-8 kodovani" myslíš co konkrétně?
kacikac 28.03.2023 13:18
kacikac
Jo, máš pravdu; já zapomněl dodat, že při pokusu o načtení jsem dostal celkem tři hlášky. První se m…
NoReg 28.03.2023 13:25
NoReg
Pokud by byl ten soubor v poradku, tak by se otevrel. Protoze je tam zprasene UTF-8 (zrejme namichan…
Jan Fiala 28.03.2023 14:00
Jan Fiala
Můžu vědět kde konkrétně je ten soubor utf-8 nevalidní? Zkusil jsem mb_check_encoding v php na utf-8…
kacikac 28.03.2023 15:05
kacikac
Me to pri prevodu hlasilo invalid char mapping. Dal jsem to nezkoumal.
Jan Fiala 28.03.2023 16:10
Jan Fiala
Takže zprasené je to, co vyhazuje tu chybu. Ne utf-8 v xml.
kacikac 28.03.2023 19:51
kacikac
Asi máš pravdu. Já ten soubor oprostil od diakritiky, poté pro jistotu v PSPadu po rozdělení nechal…
NoReg 28.03.2023 20:57
NoReg
(zdraví Paullus), Bývalý místní Paullus? ::)
čumil old 27.03.2023 19:27
čumil old
Až neuvěřitelně bývalý. Když jsem tady působil, tak to jsi býval ještě čumil very young...
NoReg 28.03.2023 10:49
NoReg
Vítej, matně si vzpomenu, že jsi chtěl cídit nějaký mosazný přemět s minimem fyzického nasazení, pak…
čumil old 28.03.2023 19:37
čumil old
Teda ty máš paměť jako slon! ::)https://kutilska.poradna.net/questions/2224613-jak-odstranit-zelenou…
host 28.03.2023 19:57
host
Ahoj hoste, víš co je nejhorší? Já si ta vlákna vůbec nepamatuji; jediné, v čem se tam poznávám je,…
NoReg 28.03.2023 20:51
NoReg
Čumile, tvoje hlava je stříbrný zvonek. Když si představím, že je ti jen o pár desítek let méně než…
NoReg 28.03.2023 20:53
NoReg
Ono to asi nebude tak horké s těmi desítkami. Jsem ročník 56. Ale zamlouvá se mi Tvůj smysl pro humo…
čumil old 29.03.2023 19:37
čumil old
1956? Tak to byl tuším rok, kdy mému nejmladšímu pravnoučeti diagnostikovali stařeckou demenci.
NoReg 29.03.2023 21:38
NoReg
Ano, ano, pro Tvůj smysl pro humor si tě pamatuju.:beer: poslední
čumil old 30.03.2023 19:23
čumil old
Paullus! Zdravim te! :-):beer:
MaSo 28.03.2023 08:13
MaSo
I já tebe, MaSo. Vidím, že i jako svědkovi historických epoch ti paměť stále slouží...
NoReg 28.03.2023 10:54
NoReg
Zatim slouzi ale je to cim dal tim horsi. Uz to neni jako pred patnacti lety, kdy jsme tu analyzoval…
MaSo 29.03.2023 08:54
MaSo
Zda chceš, tak to celé mám aktuální v MySQL. ;)
tomas.kulhanek 27.03.2023 14:32
tomas.kulhanek
Já si převádím denně (už 11let) XML do čitelné podoby tak, že si ho nastyluji. Ale já ho potom jen č…
Kráťa 29.03.2023 09:54
Kráťa

Tak jsem zkusil ten nejmenší soubor naimportovat do Excelu (2007), menu Data - Načíst externí data - Z jiných zdrojů, po pár minutách hotovo, uloženo jako *xlsx, opravdu pěkná tabulka.

Zkusil jsem stejný postup na další, půlgigový soubor, běží to (kolečko se točí) už dobře 20 minut, zatím nic. Nechám to klidně běžet hodinu, dvě, ale trochu se obávám, že to tenhle Excel nezvládne. Na PC je 16GB RAM, ve Správci úloh vidí, že Excel (32bit) žere kolem 890MB paměti.

Zkusil jsem ten největší soubor do Accessu (2007) dostat, šlo to, ale nenačetlo se to tam celé (jen něco přes mio záznamů, možná nějaké omezení starší verze?). Výsledek je v 5 tabulkách, bohužel bez nějakého společného ID. Vytvořil jsem tedy vlastní, šestou, připravil chlívečky s tím, že to tam z těch původních tabulek copy/pastnu. A tam jsem skončil, schránka vezme jen nějakých 64 tisíc řádků, s tím se drbat nemíním ;-)

Zkusím to případně ještě v Office 2019 (manželka). Není to nic důležitého, spíše mě to zajímalo, z how_to pohledu

Vypsat data do tabulky v php jde např. takto (vypíše všechny id s tradeName):

<table>
<tr><th>id</th><th>tradeName</th></tr>
<?php
$xml = simplexml_load_file('datafile-seznam_ds_ovm.xml');
foreach($xml as $box) {
	echo "<tr><td>" . $box->id . "</td><td>" . $box->name->tradeName . "</td></tr>\r\n";
};
?>
</table> 

Vypsání je prakticky okamžité u toho 16 MB souboru datafile-seznam_ds_ovm
Vypsat se dají všechna data nebo si to klidně můžeš místo vypsání do tabulky hodit do databáze.

Zkusil jsem vypsat id, jméno a příjmení do tabulky z toho půlgigového souboru datafile-seznam_ds_fo, vypsané v prohlížeči to bylo odhadem asi za 20 sekund.

<table>
<tr><th>id</th><th>Jméno</th><th>Příjmení</th></tr>
<?php
$xml = simplexml_load_file('datafile-seznam_ds_fo.xml');
foreach($xml as $box) {
	echo "<tr><td>" . $box->id . "</td><td>" . $box->name->person->firstName . "</td><td>" . $box->name->person->lastName . "</td></tr>\r\n";
};
?>
</table> 

A v případě potřeby ten xml může být v zip archivu (místo 500 MB má soubor 30 MB), v tom případě se to xml načítá takto:

$zip = new ZipArchive;
if ($zip->open('datafile-seznam_ds_fo.zip') === TRUE) {
    $xml = simplexml_load_string($zip->getFromName('datafile-seznam_ds_fo.xml'));
    $zip->close();
}

Nedávno jsem převáděl soubor velký (téměř 2GB) XML soubor do Excelu. Protože výsledek měl více řádků, než činí limit pro Excel, tak ho jmenovaný program nebyl schopen importovat. Následně jsem tedy vyzkoušel tři metody:

1) Online programy na webu; ani v jednom z více případů se mi to nepovedlo.

2) Script Power Shellu, který jsem našel na webu:
$xlsSpreadsheet = 51
$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("c:\Test.xml")
$WorkBook.SaveAs("c:\Test.xlsx", $xlsSpreadsheet)
$Excel.Quit()

Tento script sice fungoval na většinu souborů, ale na tom mém gigantickém pracoval více jak hodinu, než jsem se nad ním smiloval a proces ukončil. A to jsem ho nechal zpracovávat na RAM Disku.

3) Rozdělení XML souboru na vícero menších, které by pak Excel pobral a mohl je nabídnout MS Accessu, kde by nebyl problém vytvořit jakkoli velkou tabulku databáze. V tomto směru jsem zjistil, že na webu se vyskytuje řada programů, které nabízejí rozdělení XML souborů zejména do formátu CSV. Některé jsou perverzně drahé, jiné zase na můj soubor nefungovaly. Tak jsem to zkusil primitivně, že jsem si ten velký soubor rozdělil v Total Commanderu na několik menších a každý z nich opatřil hlavičkou a koncovkou z toho původního.

Kupodivu to fungovalo. Protože jsem ale naprostý laik a ve struktuře souborů se vůbec nevyznám, tak nevím, zda by tato metoda rozdělení účinkovala na všechny typy XML.

Pokud bys to potreboval, napisu ti nejakou malou utilitku, která to přehrne do Access nebo co si řekneš (třeba DBF).
Access má limit 2GB na databázi, pokud tam bude jedna tabulka, pak 2GB na tu tabulku - nějaká režie.
Pokud si tam přidáš indexy, bude to méně.

Načtení, rozparsování toho největšího 1.65 GB xml pfo souboru a uložení do csv mi trvá celkem asi 45 sekund v php (kód je podobný jako ten výše uvedený) a zpracovává to jedno vlákno na AMD 5600G. Zpracováno je skoro 1.9 miliónu řádků.
520 MB xml fo soubor mi trvá asi 13 sekund, v něm je přes 600 tisíc řádků.
Nijak neoptimalizuju, dělám tím nejjednodušším způsobem.
Ještě poznámka, načtení toho csv fo v excelu je celkem fofr, zhruba do půl minuty (včetně 68 errorů, kdy sloupce cp a zip se vytvoří jako číselné, ale v některých případech tam není číslo), excel si daleko líp poradí s csv než s xml, s xml má asi problém.
pfo csv jsem v excelu ani nezkoušel, 1.9 milion řádků excel nepobere :-)
Možná to zkusím nahodit i do mysql do tabulky v RAM :-) nebo do sqlite

Pro Excel je lepší TAB delimited. Ušetříš velikost, a Excel to má mnohem jednodušší na parsování než se drbat s uvozovkami, takže je to rychlejší než CSV
Já dělal ten 640MB, odskočil jsem si na malou a když jsem se vrátil, bylo hotovo. Musím přidat timer, abych věděl, jak dlouho mu to trvalo. Skončil jsem s asi 761 000 řádky.

Tak ono je to v tom Excelu rychlé i tak.
Teď zkouším převod xml do csv tímto skriptem, dělá to validní csv pomocí php funkce fputcsv a je to celkem rychlé (0.4 s OVM, 17 s FO, 21 s PO, 56 s PFO), ukládám nejdřív do paměti a pak naráz uložím do souboru.

<?php
//$t = "po";
//$t = "pfo";
//$t = "fo";
$t = "ovm";
$timer = -microtime(true);
$csv = fopen('php://memory', 'r+');
fputcsv($csv, array("id","type","subtype","firstName","lastName","tradeName","ico","city","district","street","cp","co","ce","zip","addressPoint","state","fullAddress","pdz","ovm","isMaster","idOVM"));
$xml = simplexml_load_file("datafile-seznam_ds_{$t}.xml");
foreach($xml as $box) {
	fputcsv($csv, array(
		$box->id,
		$box->type,
		$box->subtype,
		$box->name->person->firstName,
		$box->name->person->lastName,
		$box->name->tradeName,
		$box->ico,
		$box->address->city,
		$box->address->district,
		$box->address->street,
		$box->address->cp,
		$box->address->co,
		$box->address->ce,
		$box->address->zip,
		$box->address->addressPoint,
		$box->address->state,
		$box->address->fullAddress,
		$box->pdz,
		$box->ovm,
		$box->hierarchy->isMaster,
		$box->idOVM
	));
};
rewind($csv);
file_put_contents("datafile-seznam_ds_{$t}.csv", stream_get_contents($csv));
echo "Execution time: " . $timer + microtime(true) . " s";
?>

... a kdyby se někomu hodil převod z xml do sqlite pomocí php:
(na AMD 5600G: OVM 0.7 s, FO 26 s, PO 31 s, PFO 71 s)

<?php
$timer = -microtime(true);
//$t = "po";
//$t = "pfo";
//$t = "fo";
$t = "ovm";
$xml = simplexml_load_file("datafile-seznam_ds_{$t}.xml");
$pdo = new PDO('sqlite:ds.db', null, null, array(PDO::ATTR_PERSISTENT => true));
$pdo->beginTransaction();
foreach($xml as $box) {
	$dotaz = $pdo->prepare("INSERT into {$t} (id,type,subtype,firstName,lastName,tradeName,ico,city,district,street,cp,co,ce,zip,addressPoint,state,fullAddress,pdz,ovm,isMaster,idOVM) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
	$vysledek = $dotaz->execute(array(
		$box->id,
		$box->type,
		$box->subtype,
		$box->name->person->firstName,
		$box->name->person->lastName,
		$box->name->tradeName,
		$box->ico,
		$box->address->city,
		$box->address->district,
		$box->address->street,
		$box->address->cp,
		$box->address->co,
		$box->address->ce,
		$box->address->zip,
		$box->address->addressPoint,
		$box->address->state,
		$box->address->fullAddress,
		$box->pdz,
		$box->ovm,
		$box->hierarchy->isMaster,
		$box->idOVM
	));
};
$pdo->commit();
echo "Execution time: " . $timer + microtime(true) . " s";
?>

Ahoj Honzo (zdraví Paullus),

Tobě je hej, umíš programovat, ale co my lamy (ejhle rým).

Jinak nemohl bys PS Pad přizpůsobit, aby otevíral i velké soubory? Třeba ten největší XML soubor z DS (PFO) nezvládne.

Pak bych mohl ve svém oblíbeném texťáku používat tu svoji primitivní metodu rozdělovat XML mechanicky.
Takhle musím se skřípěním zubů používat různé Notepady ++, 010Editory a jiné paskvily...

Já samozřejmě mám 64b verzi (5.0.7). Ale když chci otevřít ten XML soubor, tak mi to hlásí: Soubor XX nelze otevřít. Integer overflow.

Tak jsem se dival. Ten nejvetsi soubor datovych schranek ma zprasene UTF-8 kodovani. Proto se v PSPadu dostane do vetve, kde se pokousis opravit UTF-8 a tam ten rezim uz ty velke soubory nezvladne.
Zkusim to nejak obejit.

Jo, máš pravdu; já zapomněl dodat, že při pokusu o načtení jsem dostal celkem tři hlášky.
První se mne ptala, zda chci soubor opravdu otevřít, druhá oznámila, že obsahuje chybné UTF-8 kódování a opět se tázala, jestli ho chci přesto otevřít a až ta třetí byla definitivně zamítavá.

Pokud by byl ten soubor v poradku, tak by se otevrel.
Protoze je tam zprasene UTF-8 (zrejme namichane UTF-8 s CP1250), tak by doslo ke ztrate znaku a to u programatorskeho editoru je hloupe. Proto je tam to varovani a uzivatel ma moznost to otevrit takovym bocnim procesem, ktery kodovani nekontroluje. Ten se ale nevyrovna s tak velkym stringem.

Bud to z UTF-8 prekoduji rucne po svem nebo to pro velke retezce rozdelim a udelam navickrat.

Můžu vědět kde konkrétně je ten soubor utf-8 nevalidní?
Zkusil jsem mb_check_encoding v php na utf-8 a udává mi to, že ty soubory včetně toho největšího jsou utf-8 validní.
(Udělal jsem i ověřovací test souboru znaků české abecedy v utf-8 a ansi i mix (třeba utf-8 text s jedním ansi č) a mb_check_encoding to detekuje správně)
(mb_check_encoding kontroluje všechno, i mimo ty znaky, které jsem testoval)

Asi máš pravdu. Já ten soubor oprostil od diakritiky, poté pro jistotu v PSPadu po rozdělení nechal překódovat do UTF-8 a po opětovném spojení se to chová naprosto stejně.

Přitom po rozdělení na dva 700 MB soubory to načíst šlo.

Já si převádím denně (už 11let) XML do čitelné podoby tak, že si ho nastyluji.
Ale já ho potom jen čtu a kopíruji texty. V PSPadu mám užovatelský konvertor a je to během chvilky.
Jestli by to takto fungovalo i u velkého souboru (nevím, jak velký PSPad načte) netuším. Rovněž netuším, jestli by šly nadělat tabulky.
Nicméně někomu by se to mohlo hodit, proto tady odepisuji.
Konvertor vypadá takto (začátek):

[Settings]
ForwardName=Kráťův konvertor
ForwardIgnoreCase=1
BackWardIgnoreCase=0
ConversionStyle=regexpressions
[RegExpressions]
<?xml.* ?>|xml version="1.0" encoding="windows-1250" ?> <?xml-stylesheet type="text/css" href="-data.css"?>
<?xml version="1.0" encoding="utf-8" ?>|<?xml version="1.0" encoding="utf-8" ?> <?xml-stylesheet type="text/css" href="-data.css"?>
\<(Skore|Autor|Datum|Str.|Oblast|ProfilID|Zpracovano|Jazyk|Rubrika|Mutace)\>.*| 
<Poradi>.*</Poradi>|
<Zkratka_oblasti>.*</Zkratka_oblasti>|
<Zkratka_zdroje>.*</Zkratka_zdroje>|

A CSS soubor vypadá takto (začátek)


Documents {
  font-family: verdana;
  font-size: 83%;
  width: 751px;
  background-color: #ffffff;
  width: 95%;
 }

Document {
  margin-bottom: 65px;
}

Hlavicka_Clanku {
  display: block;
  margin-top: 30pt;
  margin-bottom: 10pt;
  margin-left: 20px;
}

Skore {
  width: 100%;
  color: #FF0000;
  }

Celé to potom vypadá takto:

Zpět do poradny Odpovědět na původní otázku Nahoru