[FMPP] RE: FW: dynamic traversal of csv records

[Ralf H. <ral...@gm...>]

Thx - looks nice. Will look at it once it has been released!
> -----Original Message-----
> From: Daniel Dekany [mailto:dd...@fr...]
...
> Now that you already did it ;), it's actually even simpler in the latest
> version (it's in the CVS), because now rows are not only hashes, but are
> also the sequences of the cells:
> 
> <#list n_xml as d>
>   ...
>   VALUES (<#list d as x>'${x}'<#if x_has_next>,</#if></#list>)
>   ...
> </#list>
> >> >> >> > Basically I want not to access the columns of the table by
> >> >> >> > name, but by position. E.g. instead of
> >> >> >> >   <#list flowers as flower>
> >> >> >> >      <tr><td>${flower.name}...
> >> >> >> > as in
> >> http://fmpp.sourceforge.net/dataloader.html#key_csv, I would
> >> >> >> > want to just access
> >> >> >> >   <#list flowers as flower>
> >> >> >> >      <tr><td>${flower.1}...
> >> >> >> > and "1" being column 1
> >> >> >> >
> >> >> >> > Also, I would need an inner loop if I not only don't want to
> >> >> >> > use the column name to access the field values, but also don't 
> >> >> >> > know ahead of time many how columns there are.
> >> >> >>
> >> >> >> You can use the "headers" sub-variable to dynamically query the
> >> >> >> number of columns and the name of columns.
> >> >> >
> >> >> >> OK, you don't have names... actually
> >> >> >> it's a problem that annoys me for a while, since it's sometimes
> 
> >> >> >> not easy to ensure that the first row with header names exists...

> >> >> >> I will look into this tomorrow. I hope...
> >> >> > That is a different topic - I do have the names, but I do not want
> >> >> > to hard-code them. I want to have a generic csv2sql converter 
> >> >> > macro that can handle csv files with varying numbers of columns. 
> >> >> > Questions:
> >> >> > 1) How do I access the size of the "headers" subvariable?
> >> >> Like the size of any other sequences... theSequence?size
> >> >>
> >> >> > 2) And how would I access the i-th column-field in a row - I guess
> >> >> > ${flower.i} is not correct?
> >> >> Currently you have to use the name from the header. So,
> >> >> ${flower[headers[i]]}, where the "headers" variable was set to
> >> >> theCvsThing.headers.
> >> > I guess we are almost there:
> >> > <#assign n_xml = pp.loadData("csv", dataFile,{'separator':','})>
> >> > <#assign numbOfCols = n_xml.headers?size />
> >> > <#assign headers = n_xml.headers />
> >>
> >> (Well, you don't use this variable... so why you assign it.)
> >>
> >> > <#assign tbl = "TBL_GLOBALS" />
> >> > <#list n_xml as d>
> >> >  INSERT INTO  ${tbl} (<#list n_xml.headers as x>${x}<#if
> >> >    x_has_next>,</#if></#list>)  VALUES (<#list 1..numbOfCols as
> >> x>>'${d[headers[x]]}'<#if
> >> >    x_has_next>,</#if></#list>);
> >> FreeMarker indices from 0. So, 1..numbOfCols-1. But anyway, you
> >> actually don't need here to get the values by numerical index. So just:
> >> <#list n_xml.headers as x>'${d[x]}'</#list>
> >> > </#list>
> >> >
> >> > Results after the first iteration in:
> >> >   Expression headers[x] is undefined on line 7, column 72 in ...
> >> > so, it appears that the headers moves along as the loader iterates
> >> > through the data row?
> >> >
> >> > So the first line of output INSERT INTO  TBL_GLOBALS
> >> > (id,keyname,val,category,lang_id,datatype)  VALUES
> >> > ('URI','https://localhost/','mail','0','String','
> >> > appears to be correct!
> >> >>
> >> >> But I guess I will change it so it will be possible to do it with
> >> >> directly with integer index as well.