Endelig! Brugbare data fra IBM i med FTP

Brugbare data med FTPda_DK Der er mange måder at hente data på fra en IBM i til pc. FTP er en relativ enkel måde at gøre det på, men er der pakkede og binære felter, så må man opgive at bruge FTP, da man ender op med mystiske tegn i filen. Man må finde en anden, og typisk mere besværlig, måde at gøre det på. Det vil sige, sådan har det været indtil i dag, hvor jeg viser dig en Windows Command fil, der sørger for at hente en fysisk eller logisk fil fra IBM i og oprette en pc fil i CSV format, hvor pakkede og binære felter er konverteret til læsbart format og der er overskrifter til kolonnerne 🙂

CSV filen er tilpasset til dansk standard, det vil sige at felter er adskilt med semikolon og tal har komma som decimal skilletegn:

“Field 1″;”Field 2”;123,45;678,90

CSV filen er lige til at læse ind i, for eksempel, Execl®, jeres BI modul (f.eks. SAS®) eller en helt anden applikation der skal have data i CSV format.

Windows Command filen, som jeg har valgt at kalde GetCSVFileFtp.cmd kan du hente her i en zip fil.

Syntaksen for GetCSVFileFtp.cmd er:

GetCSVFileFtp  library  pf/lf_fil  pc_fil  IBM_i  brugerprofil  kodeord  overskrift

GetCSVFileFtp.cmdHvis du udfører GetCSVFileFtp uden parametre, bliver du spurgt om dem.

CSV filen får feltbeskrivelserne (TEXT() fra DDS/SQL definitionen) som kolonneoverskrift 🙂

Hvordan virker det?

Det grundliggende i denne Windows Command fil er, at jeg får FTP serveren til at udføre en række kommandoer og resultatet af disse kommandoer overføres til pc’en.

Til at starte med afvikles en serie af FTP scripts som kontrollerer om hosten man har angivet i parameteret IBM_i nu også er en IBM i, om bruger id og kodeord er valide på IBM i. Sidst men ikke mindst, om filen findes i det angivne library.

Det rigtig spændende starter først efter ovenstående kontroller. For at få feltnavne til kolonneoverskrifter udføres kommandoen Display File Field Description og resultatet skrives til en fil. Denne fil konverteres til tekst format og hentes over på pc’en. Her ser du dét udsnit af CMD filen der gør dette:

Echo open %iseriesname% >"%scriptfile%"
Echo %user% >>"%scriptfile%"
Echo %pwd% >>"%scriptfile%"
Echo QUOTE TIME 3600 3600 >>"%scriptfile%"
Echo quote rcmd DSPFFD %lib%/%fil% OUTPUT(*OUTFILE) OUTFILE(QTEMP/XYZTEMP) >>"%scriptfile%"
Echo quote rcmd CPYTOIMPF FROMFILE(QTEMP/XYZTEMP) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*FIXED) >>"%scriptfile%"
Echo quote rcmd DLTF QTEMP/XYZTEMP >>"%scriptfile%"
Echo asc >>"%scriptfile%"
Echo get /tmp/%username% "%filelist%" >>"%scriptfile%"
Echo quote rcmd DEL '/tmp/%username%' >>"%scriptfile%"
Echo Quit >>"%scriptfile%"

Bemærk at det er Copy To Import File (CPYTOIMPF) kommandoen, der tager det tunge læs med at konvertere outfilen fra Display File Field Description (DSPFFD) kommandoen. CPYTOIMPF konverterer filen til tekst format, og i denne process oversættes binære og pakkede felter til tekstformat, og dermed bliver felterne læsbare.

Efter denne del af CMD filen er afviklet, ligger der en fil på pc’en som indeholder beskrivelse af alle felter. MEN felterne ligger et felt pr. record i filen. Jeg har brug for at få fat i feltteksterne og lave én record ud af det, med alle felter i. Denne proces varetages af denne del af CMD filen:

REM
REM Konverter felt filen og lav en overskrift.
REM
if exist "%fldhdr%" del "%fldhdr%"
for /F "usebackq tokens=*" %%A in ("%filelist%") do (
set linjen=%%A
set ftext=!linjen:~184,50!
REM
REM Herunder et trick for at udgå CRLF i filen.
REM
 <NUL set /p= ""!ftext!";" >> "%fldhdr%"
)
Echo.>> "%fldhdr%"

Environment variablen %filelist% holder navnet på pc filen, der indeholder output fra DSPFFD kommandoen. Linjen der starter med ‘for …’ gør det, at den læser filen %filelist% en record ad gangen og for hver record udføres koden i parentes.

For at få teksten til det pågældende felt, laves en substring ud af linjen læst fra filen. I position 184 og 50 positioner frem, i tekst filen findes beskrivelsen af feltet. Hvis du foretrækker selve feltnavnet (DDS/SQL) da skal du ændre denne linje til at lave substring fra position 136 og 10 positioner frem.

Denne linje:

 <NUL set /p= ""!ftext!";" >> "%fldhdr%"

skriver feltnavnet ud i filen %fldhdr% UDEN der bliver indsat et linjeskift efter feltnavnet er tilføjet. Af netop denne årsag er jeg nødt til at tilføje et linjeskift når linjen med kolonneoverskrifter er skrevet til filen. Det gør jeg ved, blot at udføre en tom Echo ud i filen:

Echo.>> "%fldhdr%"

Et lille ‘men’ …

Måden som Windows Command filen får en IBM i fysisk eller logisk fil over i CSV format er ganske smart, men i forhold til andre værktøjer til at overføre data, så som File Transfer fra Client Access®, så tilbyder min Windows Command fil ikke mulighed for at selektere data. Det er alt eller intet. Dette er der dog måder at kommer rundt om, f.eks. ved at afvikle en Query, QM Query eller SQL og så overføre resultats filen.

Et lille ‘men’ mere …

Det har vist sig, at visse filer har feltbeskrivelser som indholder tegnene ‘*’, ‘(‘ og ‘)’. Dette er blandt andet tilfældet for outfilen fra DSPFFD. Disse tegn bliver mistolket af Windows’ kommando fortolker og ender med, at overskrifterne bliver underlige. Jeg har derfor tilføjet parameteret ‘overskrift’ således, at du kan vælge om du vil have en overskrift eller ej.

Brug for CSV filen i US format?

Hvis du har brug for at CSV filen er i US format, det vil sige at felter er adskilt med komma og decimal komma er et punktum, da er det denne linje i scriptet du skal ændre:

Echo quote rcmd CPYTOIMPF FROMFILE(%lib%/%fil%) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(';') DECPNT(*COMMA) >>"%scriptfile%"

Den skal ændres til:

Echo quote rcmd CPYTOIMPF FROMFILE(%lib%/%fil%) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(',') DECPNT(*PERIOD) >>"%scriptfile%"

Så er der kun tilbage at ønske dig god fornøjelse med dette nyttige Windows Command script 🙂