put an xml and csv file togehter

[Paddymaster]

Well. Its good. Since you answered i developed an own solution

Instead of work with this variablecolumn and variableprimarykey i replaced it with string.
but your solution is very good.

upload.zip (824 Bytes)
BUT both solutions have one mistake. i show you with a new xml and fileupload.

The LIstfiles are alphanumeric sorted. The XML isn’t alphanumeric sorted. Its important to match the filename in xml-xpath URL with the datanames. Otherwise you get the false column names

[armingrudd]

Your XML again has an error:
2 successive opening <Table> tags.
Here is the modified version:
upadestructure.xml (1.5 KB)

And here is the modified version of the workflow regarding:

@Paddymaster

The LIstfiles are alphanumeric sorted. The XML isn’t alphanumeric sorted. Its important to match the filename in xml-xpath URL with the datanames.

xml_headers.knwf (68.2 KB)

The change in this version is the added String Manipulation (Variable) node in which the file name is extracted one step before the XPath creation. So we can use the file name in XPathes. I used the file name to specify the true tags we are looking for.
Regarding the changing structure of the CSV files, I had to replace the File Reader node with a CSV Reader node and add a Missing Value node.

@Paddymaster

[Paddymaster]

Your such a brain. Unbelievable. I tried your Solution. I’ll do a little bit smaller with String Manipulation.

#1 If you have Filenames with blank space you get errors. For example the Filename = “GB London.csv” will be Filename =“GB%20London”. Than you can’t match the Filename with the XPath. In the XML files are the blank space filenams replaced with a “". I don’t know how good my solution is but in String Manipulation I’ll correct this line regexReplace($${SURL}$$, “(./)|(\.csv)", “”) to
replace(regexReplace($${SURL}$$, "(./)|(\.csv)”, “”),"%20","”).

And the String Manipulation next to XML-Reader i wrote "replace(replace(replace(replace($XML$,"</VariableColumn>",""),"<VariableColumn>",""),"</VariablePrimaryKey>",""),"<VariablePrimaryKey>","")".

Is there a way to write multiple commands in different rows instead of writing command in each other?

for example
" replace($XML$,"<VariablePrimaryKey>","") replace($XML$,"</VariablePrimaryKey>","") replace($XML$,"<VariableColumn>","") replace($XML$,"</VariableColumn>","").

[armingrudd]

Well, you come with a new condition every time…

Change: Now uses the “Location” instead of “URL” to overcome the file name issue where there is a space in the file name. This change applies to String Manipulation (Variable) and CSV Reader nodes.

@Paddymaster

Is there a way to write multiple commands in different rows instead of writing command in each other?

Yes, you can remove those tags and have all the column headers in the same level so you can skip the loop:
regexReplace($XML$, "(<VariableColumn>)|(</VariableColumn>)|(<VariablePrimaryKey>)|(</VariablePrimaryKey>)", "")

xml_headers.knwf (58.0 KB)

[Paddymaster]

genius: its a better solution.
Well thank you.

You’ve Right. Next time i’ll take more time for good Examples. Sorry.
I’m new at knime. I understand the principes but its hard for me to understand the syntax from code.

[Paddymaster]

well; the solution is great.

sometimes i get CSV-Files with a comma at the end of the line.
Than the reader gives me one column more as aspected.
The Match in the Insert Column Header gives than an error and the workflow is broken.
I get also sometimes a file with the same name but only in small letters.
how can i change the workflow for this problem? i’ll upload a csv for this problem.file_headers_new.zip (385 Bytes)

[armingrudd]

@Paddymaster

sometimes i get CSV-Files with a comma at the end of the line.
Than the reader gives me one column more as aspected.

Use Missing Value Column Filter after the CSV Reader node to remove the additional column.

@Paddymaster

I get also sometimes a file with the same name but only in small letters.

Add a upperCase or lowerCase function in String Manipulation nodes (Nodes 18 & 21).

Node 21:
lowerCase(regexReplace($XML$, "(<VariableColumn>)|(</VariableColumn>)|(<VariablePrimaryKey>)|(</VariablePrimaryKey>)", ""))

Node 18:
lowerCase(regexReplace($${SLocation}$$, "(.*\\\\)|(\\.csv)", ""))

And also change all capital letters of the constant strings in Variable Expression (Node 13) to lowercase:
join("/root/table[name/text() = \"", variable("fileName"), "\"]/variablelength/name")

[Paddymaster]

well;

i’ll tried;

i just need to set up in the Node18 the first letter of the variable as uppercase.
how can i do that?.

maybe a rule engine after that ?

edit: Maybe with Rule Engine Variable.

$${SfileName}$$ LIKE “[a-z]*” => write First Letter Big.

don’t know how to write.

[armingrudd]

@Paddymaster

i just need to set up in the Node18 the first letter of the variable as uppercase.
how can i do that?

That’s possible with “capitalize” function but I considered cases like “GB London” and thought it’s better to make all the characters lowercase or uppercase to prevent any problems.

[Paddymaster]

my solution is “upperCase(substr($${SfileName}$$,0,1)) + substr($${SfileName}$$,1,length($${SfileName}$$)-1)”

yeah sure; but dont know how i can just lowercase the name in the xml. thats prob

[armingrudd]

You have to use upperCase or lowerCase. For lowercase use the expressions in my previous reply or for uppercase use these expressions:

Node 21:

regexReplace(
	upperCase(regexReplace($XML$, "(<VariableColumn>)|(</VariableColumn>)|(<VariablePrimaryKey>)|(</VariablePrimaryKey>)", "")),
	"<\\?XML.*?>",
	"<?xml version='1.0' encoding='UTF-8'?>"
	)

Node 18:
upperCase(regexReplace($${SLocation}$$, "(.*\\\\)|(\\.csv)", ""))

Node 13:
join("/ROOT/TABLE[NAME/text() = \"", variable("fileName"), "\"]/VARIABLELENGTH/NAME")

[Paddymaster]

okay. thx.

The Workflow is already very good. But some specials make that workflow better.
The Final file is a XLSX-Sheet. But if i Have more than 1,5 m rows than excel cannot create a file. I must take a CSV-Writer. Does Knime have a Node with a IF-Decision of Count-Rows ?
For Example:

Below 1 000 000 rows do a XLS-Write and Above 1 000 000 write in CSV ?

[ipazin]

Hi there,

for workflow control there is IF Switch node and multiple Case Switch nodes. You can get number of rows in couple of ways. For example Extract Table Dimensions.

Here is example workflow:
https://kni.me/w/o4Cc5HNiVgCiXX5W

Br,
Ivan

[Paddymaster]

thats really genius @ipazin thx

but in this case you didn’t take the if switch node; just the case switch data.

I’ve a small problem: I added your workflow to mine. But I’ve more than one file. Thats why i ’ ve a loop like that given workflow above. I geht the Error --> “Active Scope End node in inactive branch not allowed.”. And the Error is given because I’ve the Variable Loop End after the CSV Writer and Excel Writer. I’ll give you a Screenshot .

I

[armingrudd]

Based on @ipazin’s suggestion:

Node 25:

$${INumber Rows}$$ <= 1048576 => "top"
TRUE => "bottom"

And use the variable in IF Switch node. That’s it!

[Paddymaster]

why do you need table row to variable and 2 times variable to table row ?

and my end loop is Variable Loop End. Its not possibel to connect with End If

edit: well its okay; i can change Variable Loop End with Loop End

[armingrudd]

@Paddymaster

why do you need table row to variable and 2 times variable to table row ?

The Nodes 32 & and 33 are used to make it possible to use the End IF node.

@Paddymaster

and my end loop is Variable Loop End. Its not possibel to connect with End If

You can change it. This change will make no difference in the output.