Skonwertuj wszystko

Procedura wypełniania bazy danych rekordami na podstawie plików z danymi dzieli się na dwie części: implementację aplikacji oraz wprowadzanie danych.

Zespół programistów może
więc pracować niezależnie od zespołu odpowiedzialnego
za opracowanie danych. W ten sposób
nie trzeba czekać z wprowadzaniem treści do
serwisu internetowego aż do momentu ukończenia
aplikacji.

W artykule przedstawię technikę konwersji
pomiędzy formatami .txt, .xls oraz .xml. Konwersje
takie pozwalają na wprowadzanie do bazy danych
informacji przygotowanych w dowolnym z trzech
formatów. Wybór formatu możemy wówczas
dostosować do potrzeb osób odpowiedzialnych za
wprowadzanie tekstów.

Tworząc aplikacje internetowe, które wykorzystują
bazy danych, zwykle przyjmuję następujący
cykl rozwoju:

• wstępne opracowanie modelu bazy danych,
• wypełnienie bazy danych przykładowymi rekordami,
• opracowanie zapytań SQL,
• implementacja aplikacji.

Wypełnianie bazy danych przykładowymi
rekordami implementuję zazwyczaj wykorzystując
pliki tekstowe. Metoda ta ma trzy istotne zalety. Po
pierwsze, umożliwia szybkie ponowne wypełnienie
bazy danych. Jest to przydatne w przypadku
zaśmiecenia bazy danych po wykonaniu błędnego
kodu.

Po drugie daje możliwość łatwego dostosowania
wypełniania bazy danych, gdy model bazy
ulegnie zmianie. Po trzecie, pozwala na opracowywanie
danych aplikacji, zanim kod zostanie
ukończony (czy nawet doprowadzony do stanu
alfa). Osoba odpowiedzialna za przepisywanie
otrzymuje ścisłe instrukcje na temat formatu, po
czym może przystąpić do pracy. Rozwój aplikacji
oraz obróbka danych mogą być prowadzone równolegle
i niezależnie.

W zależności od struktury bazy oraz umiejętności
osób odpowiedzialnych za przygotowanie
danych, pliki tekstowe mogą być niewygodne.
W przypadku, gdy format tekstowy jest zbyt skomplikowany,
możemy stosować arkusze kalkulacyjne
Excel lub pliki XML.

W artykule omówię sześć konwersji formatów,
które pozwalają na wymienne stosowanie plików
tekstowych, arkuszy kalkulacyjnych Excel oraz XML.
Konwersjami tymi są:

• TXT -> XLS
• TXT -> XML
• XLS -> TXT
• XLS -> XML
• XML -> TXT
• XML -> XLS

Zestawienie artykułów publikowanych
w \”Magazynie INTERNET\”

Wymienionych sześć konwersji formatów przetestowałem
na zestawieniu artykułów opublikowanych
na łamach \”Magazynu INTERNET\” w 2007 roku.

Każdy artykuł jest opisany przez następujące atrybuty:

• rok publikacji (np. 2007),
• numer czasopisma (np. 1),
• strony zajmowane przez artykuł (początkowa i końcowa, np. 17-21),
• tytuł artykułu,
• wstęp artykułu,
• rubryka,
• podrubryka,
• lista autorów.

Dane te zostały wprowadzone w formacie
arkusza kalkulacyjnego Excel. Każdy artykuł został
w opisany jednym pliku .xls o dwóch zakładkach.

Dane w formacie XLS zostały przekonwertowane
do formatu tekstowego oraz do formatu XML.
W każdym z trzech formatów jeden artykuł jest opisany
przez jeden plik. Struktura plików tekstowych
jest następująca:

ROK:2007
NUMER:1
STRONY:18,18
TYTUŁ:Najlepsze polskie sklepy internetowe
LID:Hobby to na tyle obszerna kategoria...
RUBRYKA:aktualności.e-zakupy
AUTOR:Michał Stępień

Pliki XML mają strukturę:

  2007
  1
  18
  18
  Najlepsze polskie sklepy internetowe
  Hobby to na tyle obszerna kategoria...
  aktualności
  e-zakupy
  
    
    Michał
    Stępień
    
  

Konwersja TXT -> XML

Konwersja plików tekstowych do formatu XML
wykorzystuje opisane już funkcje: odczytanie pliku
tekstowego (parse_txt()) oraz zapisanie tablicy
$parsed do pliku XML ({stala}parsed_write_tpl(){/stala}):

$files = glob(\'dane/*.txt\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.xml\');
  $parsed = parse_txt($f);
  parsed_set_output($parsed, \'xml\');
  parsed_write_tpl($nf, $parsed);
}

Konwersja XML -> TXT

Kod zamieszczony poniżej przedstawia skrypt
xml2txt.php:

$files = glob(\'dane/*.xml\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.txt\');
  $parsed = parse_xml($f);
  parsed_set_output($parsed, \'txt\');
  parsed_write_tpl($nf, $parsed);
}

Jedynym nowym elementem jest funkcja
{stala}parse_xml(){/stala}:

function parse_xml($filename)
{
  $tmp = simplexml_load_file($filename);
  $w = array();
  $w[\'rocznik\'] = f_in_xml((string)$tmp->rok);
  $w[\'numer\'] = f_in_xml((string)$tmp->numer);
  ...
  return $w;
}

Funkcja ta odpowiada za przekształcenie pliku
XML w tablicę asocjacyjną {stala}$parsed{/stala}. Do odczytania
pliku XML wykorzystana została biblioteka
simplexml.

Konwersja XML -> XLS

Konwersja formatu XML do formatu .xls jest
wykonywana przez skrypt {stala}xml2xls.php{/stala}. Wykorzystuje
ona opisane już funkcje: {stala}parse_xml(){/stala} oraz
{stala}parsed_write_xls(){/stala}:

$files = glob(\'dane/*.xml\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.xls\');
  $parsed = parse_xml($f);
  parsed_set_output($parsed, \'xls\');
  parsed_write_xls($nf, $parsed);
}

Podsumowanie

Konwersji trzech formatów zaimplementowałem,
stosując format pośredni: tablicę asocjacyjną.
Konieczne jest zaimplementowanie trzech procedur
tworzenia tablicy asocjacyjnej na podstawie pliku
oraz trzech procedur zapisywania tablicy asocjacyjnej
do pliku.

Za odczytywanie danych z plików odpowiadają
funkcje:

• parse_xml(), parsed_set_output() i f_in_xml()
• parse_xls(), parsed_set_output() i f_in_xls()
• parse_txt(), parsed_set_output() i f_in_txt()

Tablicę asocjacyjną należy konwertować do
formatu wynikowego. Odpowiadają za to funkcje:

parsed_set_output() i f_out_xml()

parsed_set_output() i f_out_xls()

parsed_set_output() i f_out_txt()

Natomiast zapis danych do plików jest wykonywany
przez:

• XLS: parsed_write_xls()
• XML: parsed_write_tpl() (szablon pliku xml)
• TXT: parsed_write_tpl() (szablon pliku xml)

Ogólna procedura konwersji formatu danych

Wszystkie opisane konwersje dotyczą dużej
liczby plików (w naszym przykładzie około 300).

W każdym przypadku schemat procesu konwersji
jest identyczny. Wyszukujemy pliki poddawane
konwersji (funkcja glob()), po czym w pętli foreach
przetwarzamy znalezione pliki:

$plks = glob(\'dane/*.xls\');
foreach ($plks as $plk) {
//ustalenie nowej nazwy pliku
//parsing formatu wejściowego
//konwersja do formatu wynikowego
//zapisanie wynikowego formatu
}

Nazwa pliku, w którym zostaną zapisane
wyniki konwersji, jest zwracana przez funkcję
{stala}newfilename(){/stala}, przedstawioną poniżej:

function newfilename($filename, $ext)
{
  $b = basename($filename);
  $b = preg_replace(\'/\.\w{3}$/\', $ext,$b);
  return \'wyniki/\' . $b;
}

Funkcję tę wywołujemy, podając nazwę pliku
z danymi oraz nowe rozszerzenie. Na przykład po
wywołaniu:

$stara = \'dane/a.xls\';
$nowa = newfilename($stara, \'.txt\');

zmienna {stala}$nowa{/stala} przyjmie wartość wyniki/a.txt.
Zmianie ulega nazwa folderu oraz rozszerzenie
pliku.

Parsing formatu wejściowego będzie wykonywany
przez jedną z funkcji {stala}parse_xls(){/stala}, {stala}parse_txt(){/stala},
{stala}parse_xml(){/stala}. Każda z tych funkcji pobiera jeden
parametr: nazwę pliku, który należy przetworzyć.
Wynikiem działania jest tablica asocjacyjna {stala}$parsed{/stala},
która zawiera elementy:

$parsed[\'rocznik\']
$parsed[\'numer\']
$parsed[\'odstrony\']
$parsed[\'dostrony\']
$parsed[\'tytul\']
$parsed[\'lid\']
$parsed[\'rubryka\']
$parsed[\'podrubryka\']
$parsed[\'autorzy\']

Kolejna funkcja {stala}parsed_set_output(){/stala} odpowiada
za konwersję danych do formatu wyjściowego.
Konwersja taka obejmuje transformację kodowania
polskich znaków diakrytycznych oraz przekształcenia
specyficzne konkretnych formatów. Na przykład
przed zapisaniem danych w formacie XML należy
wykonać przekształcenie {stala}htmlspecialchars(){/stala}.

Ostatnia z wywoływanych funkcji jest odpowiedzialna
za zapisanie danych do pliku. W zależności
od wymaganego formatu będzie to {stala}parsed_write_tpl(){/stala} (formaty XML i TXT) lub {stala}parsed_write_xls(){/stala}
(format XLS).

Konwersja XLS -> TXT

Konwersję formatu .xls do formatu .txt realizuje
skrypt xls2txt.php:

$files = glob(\'dane/*.xls\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.txt\');
  $parsed = parse_xls($f);
  parsed_set_output($parsed, \'txt\');
  parsed_write_tpl($nf, $parsed);
}

Za odczytanie pliku .xls odpowiada funkcja
{stala}parse_xls(){/stala}, skrótowo przedstawiona niżej:

function parse_xls($filename)
{
  $xls = new Spreadsheet_Excel_Reader();
  $xls->setOutputEncoding(\'cp1250\');
  $xls->read($filename);
  $w = array();
  $w[\'rocznik\'] = f_in_xls($xls->sheets[0][\'cells\'][1][2]);
  $w[\'numer\'] = f_in_xls($xls->sheets[0][\'cells\'][2][2]);
  ...
  return $w;
}

Wykorzystuje ona klasę {stala}Spreadsheet_Excel_Reader{/stala}.
Każda odczytywana komórka jest przekształcana
funkcją {stala}f_in_xls{/stala}:

function f_in_xls($str)
{
  $str = trim($str);
  return $str;
}

W przypadku odczytywania arkuszy kalkulacyjnych
przekształcenie wejściowe sprowadza się do
usunięcia białych znaków.

Dane odczytane z pliku .xls trafiają do tablicy
asocjacyjnej {stala}$parsed{/stala}. Przed zapisaniem tablicy
{stala}$parsed{/stala} do pliku tekstowego, należy wykonać
niezbędne konwersje wyjściowe. Całą tablicę {stala}$parsed{/stala}
przekształcamy funkcją {stala}parsed_set_output(){/stala},
podając jako drugi parametr napis \’txt\’.

Funkcja {stala}parsed_set_output(){/stala} przekształci każdy element
tablicy asocjacyjnej funkcją zależną od drugiego
parametru:

function parsed_set_output(&$art, $output)
{
if ($output == \'xml\') {
  $art[\'rocznik\'] = f_out_xml($art[\'rocznik\']);
  $art[\'numer\'] = f_out_xml($art[\'numer\']);
  $art[\'odstrony\'] = f_out_xml($art[\'odstrony\']);
  ...
} 
else if ($output == \'txt\') {
  $art[\'rocznik\'] = f_out_txt($art[\'rocznik\']);
  $art[\'numer\'] = f_out_txt($art[\'numer\']);
  $art[\'odstrony\'] = f_out_txt($art[\'odstrony\']);
...
} 
else if ($output == \'xls\') {
  $art[\'rocznik\'] = f_out_xls($art[\'rocznik\']);
  $art[\'numer\'] = f_out_xls($art[\'numer\']);
  $art[\'odstrony\'] = f_out_xls($art[\'odstrony\']);
  ...
 }
}

W przypadku podania parametru \’txt\’ wykonane
zostanie przekształcenie {stala}f_out_txt(){/stala}:

function f_out_txt($str)
{
  $str = str_replace(\"\r\", \' \', $str);
  $str = str_replace(\"\n\", \' \', $str);
  $str = preg_replace(\'/ +/\', \' \', $str);
  $str = pl_strict2iso($str);
  return $str;
}

Przy tworzeniu pliku tekstowego konieczne jest
usunięcie znaków złamania wiersza.

Po przekształceniu do formatu wynikowego
tablica {stala}$parsed{/stala} zostaje zapisana do pliku tekstowego. Zadanie to wykonuje funkcja {stala}parsed_write_tpl(){/stala}. Jest ona zaimplementowana przy użyciu
szablonów Smarty:

function parsed_write_tpl($filename,$article)
{
  $s = new Smarty;
  $s->assign(\'art\', $article);
  $tmp = $s->fetch(\'artykul.tpl\');
  file_put_contents($filename, $tmp);
}

Szablon artykułu w formacie tekstowym jest
widoczny niżej:

ROK:{$art.rocznik}
NUMER:{$art.numer}
STRONY:{$art.odstrony},{$art.dostrony}
TYTUŁ:{$art.tytul}
...

Konwersja XLS -> XML

Poniżej został przedstawiony skrypt xls2xml.
php. Wykonuje on konwersję formatu .xls do
formatu .xml. W stosunku do poprzedniego
rozwiązania różni się dwoma detalami. Funkcja
{stala}parsed_set_output(){/stala}, odpowiedzialna za konwersję
do formatu docelowego, jest wywołana z parametrem
\’xml\’:

$files = glob(\'dane/*.xls\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.xml\');
  $parsed = parse_xls($f);
  parsed_set_output($parsed, \'xml\');
  parsed_write_tpl($nf, $parsed);
}

Każdy element tablicy {stala}$parsed{/stala} zostanie przekształcony
funkcją {stala}f_out_xml(){/stala}:

function f_out_xml($str)
{
  $str = htmlspecialchars($str);
  $str = pl_strict2utf8($str);
  return $str;
}

Drugim elementem, który odróżnia konwersję
xls2xml od konwersji xls2txt, jest szablon w formacie
XML:

  {$art.rocznik}
  {$art.numer}
  {$art.odstrony}
  ...

Konwersja TXT -> XLS

Konwersja formatu .txt do formatu .xls została
przedstawiona poniżej. Skrypt {stala}txt2xls.php{/stala} wywołuje
funkcje {stala}parse_txt(){/stala} oraz {stala}parsed_write_xls(){/stala}:

$files = glob(\'dane/*.txt\');
foreach ($files as $f) {
  $nf = newfilename($f, \'.xls\');
  $parsed = parse_txt($f);
  parsed_set_output($parsed, \'xls\');
  parsed_write_xls($nf, $parsed);
}

Funkcja {stala}parse_txt(){/stala} przy użyciu wyrażeń
regularnych wycina z pliku tekstowego konieczne
informacje. Wszystkie wykrojone napisy są przekształcane
funkcją {stala}f_in_txt(){/stala}:

function parse_txt($filename)
{
  $p = file($filename);
  $w = array();
  if (preg_match(\'/^ROK:(.*)$/\',trim($p[0]), $m)) {
  $w[\'rocznik\'] = f_in_txt($m[1]);
} 
else {
  $w[\'rocznik\'] = \'\';
}

if (preg_match(\'/^NUMER:(.*)$/\',trim($p[1]), $m)) {
  $w[\'numer\'] = f_in_txt($m[1]);
} 
else {
  $w[\'numer\'] = \'\';
}
 ...
 return $w;
}

Natomiast funkcja {stala}parsed_write_xls(){/stala} zapisuje
tablicę {stala}$parsed{/stala} do formatu .xls. Wykorzystuje ona
klasę Spreadsheet_Excel_Writer. Oto zarys kodu
funkcji {stala}parsed_write_xls(){/stala}:

function parsed_write_xls($filename, $article)
{
  $xls = new Spreadsheet_Excel_Writer($filename);
  $sheetArt = $xls->addWorksheet(\'Artykuł\');
  $sheetArt->setColumn(0, 0, 20);
  $format = $xls->addFormat();
  $format->setBold();
  $sheetArt->write(0, 0, \'Rok\', $format);
  $sheetArt->write(1, 0, \'Numer\',$format);
  ...
  $sheetArt->write(0, 1,
  $article[\'rocznik\']);
  $sheetArt->write(1, 1,
  $article[\'numer\']);
  ...
  $xls->close();
}