CSV format issues

Chumbo · 16 February 2021 00:49

Hello,

I'm trying to read the csv data seen below, but for each 'WorkOrderNumber' there is multiple rows.
I need the 'userid' number to be in the same row as the rest of the info...

can I join these somewhow? I need to sort by mutliple of these fields but it won't work becasue they are in different 'objects'

Colin · 16 February 2021 09:16

Do you have the whole set as an array in a message? If so can please feed that into a debug node and expand enough of it so we can see exactly what form it is in.

Chumbo · 17 February 2021 19:50

Hi Colin,

I have it set to feed a message per row, below is an example image of the output I get (different record, but they are all like this, some have 3 messages some have up to 7 or 8).

Also just to be clearer about what I am trying to achieve;
-I have used switch nodes to filter out all the messages that are assigned to a certain 'UserId'. This works fine, but it only returns the info in the message with UserId in it, and i loose all the info in the other messages (containing name, origin etc)
-Ideally if all the data fields where populated in one message I could handle it easy. but im not sure how to proceed when its like this.

zenofmud · 17 February 2021 20:03

It would seem to me that this is not a normal CSV file. You probably have to read each record and pass on or eliminate the rows until you get to the next row with an id in it.

But it might be easier to go back to what ever is creating the file and altering things there.

Chumbo · 17 February 2021 20:05

Yeah I'm going down that path but its slow going. I think when there is multiple 'users' assigned to each 'work order no' they index the csv into separate rows like this.

zenofmud · 17 February 2021 21:46

Here is what I would do

initilise a flow variable array 'current_ids = ' (an empty array)
update the current_ids array with the id's to be removed
read a row - if it does not have an ID, pass it on - if it has an ID see if it is in the 'remove' list and set another variable ('keep_or_not') to 'remove' or 'keep' and pass it on.
in a switch node check 'keep_or_not' if it says remove ignore the record otherwise pass it on for what ever else you need to do with it.

So every row after a 'remove' should be ignored and the rest will flow thru.

Chumbo · 17 February 2021 21:55

thanks, I'll give it a g in a bit.
I tried maybe something similar below, but I'm not that familiar with the intricacies of JavaScript and flow context and I'm getting lots of errors haha.... is the below something that is maybe possible to get working?!

{

var workordercheck = flow.get(msg.payload.WorkorderNo)

if (msg.payload.Name != null )
	{
	flow.set("WO",msg.payload.WorkOrderNo);
	flow.set("origin",msg.payload.OriginType);
	flow.set("hrs",msg.payload.EstimatedHours);
	}
else if (msg.payload.WorkOrderNo == WO_Check)
	{
	If (msg.payload.UserId != null)
			{
			flow.set("user",msg.payload.UserId);
			}
    	}
	else 
			{
			//do nothing
			}
    
}

var WO1 = flow.get("WO");
var origin1 = flow.get("origin");
var hrs1 = flow.get("hrs");
var user1 = flow.get("user");

return [WO1, origin1, hrs1, user1];

zenofmud · 17 February 2021 22:56

can you provide some of the actual data (sanitized of course)? This should be doable in nodes with out the need for a function node.

Chumbo · 17 February 2021 23:06

example_data.txt (50.8 KB)

zenofmud · 18 February 2021 00:22

Do you realize that file is not comma seperated? It is tab seperated so it is not a CSV file.

UnborN · 18 February 2021 00:35

indeed i noticed the same thing but i think thats the least of Chumbo's problems
the csv node should handle the parsing of the tab seperated values.

@Chumbo this is a table from the sample data you sent

What UserId is supposed to be filled in rows 2 to 3, what UserId for rows 5 to 6 and what for rows 9 to 26

Chumbo · 18 February 2021 00:37

example_data2.txt (25.4 KB)

it wouldnt let me upload csv before, but above link is now csv. the file im using in node red is csv.

Chumbo · 18 February 2021 00:39

I have no idea why it exports this way.
there is between 1 and 4 users assigned to any specific work order.

for example 'leak in mechanical' is assigned to user '3', fortnightly mechanical check is assigned to users '12' and '109'.

not sure why it exports a seemingly random amount of empty rows. for each work order...

Ultimately I'm trying to use nodes to read the rows in the csv file and return an array containing the fields 'OriginType,UserId,WorkOrderNo,EstimatedHours'.

if we ignore all the blank fields, it outputs one message containing 'Name, OriginType, WorkOrderNo, EstimatedHours, City', and a separate row (with the same WorkOrderNo) that has the 'UserId' field.

does this make sense?

for example, if input is;

i need output to be;

zenofmud · 18 February 2021 00:41

Without knowing that, how can you determine what should stay and what should go?
You need to get a handle on how that file is being created before you can massage it. And if multiple people can be assigned, how do you decide which to use or are you suppose to add in some new columns?

Chumbo · 18 February 2021 00:51

I edited my last response for some further info.
I will eventually be filtering it out as i'm only interested in the work order no's with User 3 assigned.

UnborN · 18 February 2021 01:01

I think your best chances are to figure out why the system that creates this file leaves empty fields for each row. If you solve this it will be very easy to filter out the data for UserId 3 in Node-red

Using javascript to fill in the missing data is possible .. everything is possible with programming but what happens in the case of "fortnightly mechanical check" ?

Whats the script going to fill in this case 12 or 109 ?

zenofmud · 18 February 2021 01:06

So any row without something in 'Name' AND without something in 'Userid' can be dumped. So in the case of '12' and '109' would two rows go to the output with all the information, one with 'Userid' of '12' and the other with '109'?

And with that, I'm gone for the evening

Chumbo · 18 February 2021 01:14

please ignore that example and see below;

for example, if input is;

i need output to be;

this is what I am trying to accomplish.

UnborN · 18 February 2021 01:22

Check this test flow .. change the path of the File In node to the path to your file / source

[{"id":"94bbef5.3dbcf9","type":"file in","z":"84c7e31b.224af8","name":"example_data.csv","filename":"C:\\\\Users\\\\User\\Desktop\\example_data2.txt","format":"utf8","chunk":false,"sendError":false,"encoding":"utf8","x":330,"y":300,"wires":[["eb9f97a3.d21ea"]]},{"id":"eb9f97a3.d21ea","type":"csv","z":"84c7e31b.224af8","name":"","sep":",","hdrin":true,"hdrout":"all","multi":"mult","ret":"\\n","temp":"Name, OriginType, UserId, WorkOrderNo, EstimatedHours, City","skip":"0","strings":true,"include_empty_strings":false,"include_null_values":true,"x":550,"y":300,"wires":[["73c789cf.cd5a58","b0fa884c.359fd8"]]},{"id":"73c789cf.cd5a58","type":"debug","z":"84c7e31b.224af8","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":710,"y":200,"wires":[]},{"id":"f1d53e2a.7031f","type":"inject","z":"84c7e31b.224af8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":300,"wires":[["94bbef5.3dbcf9"]]},{"id":"b0fa884c.359fd8","type":"function","z":"84c7e31b.224af8","name":"","func":"msg.payload.forEach(el => {\n    if (el.Name === \"Leak in Mechanical\") {\n        el.UserId = 3;\n    }\n})\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":750,"y":300,"wires":[["14ea7976.321597"]]},{"id":"14ea7976.321597","type":"debug","z":"84c7e31b.224af8","name":"2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":920,"y":300,"wires":[]}]

Basically the function loops through all data and if the Name is equal to "Leak in Mechanical"
it adds a UserId 3 to that specific array element.

msg.payload.forEach(el => {
    if (el.Name === "Leak in Mechanical") {
        el.UserId = 3;
    }
})
return msg;

Filter could be used to then filter out the elements that are equal with "Leak in Mechanical"

msg.payload.forEach(el => {
    if (el.Name === "Leak in Mechanical") {
        el.UserId = 3;
    }
})

msg.payload = msg.payload.filter(el => el.Name === "Leak in Mechanical")

return msg;

zenofmud · 18 February 2021 11:29

Here is my version using only nodes and returning a row for each UserId assigned to the Work Order

[{"id":"edc2740c.af56f8","type":"tab","label":"Add UserId to Work Order row","disabled":false,"info":""},{"id":"f82adefe.c0427","type":"inject","z":"edc2740c.af56f8","name":"Put path to input file here","props":[{"p":"filename","v":"/Users/Paul/Downloads/example_data.txt","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":210,"y":40,"wires":[["6a35c906.298dd8"]]},{"id":"6a35c906.298dd8","type":"file in","z":"edc2740c.af56f8","name":"Read CSV file","filename":"","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":180,"y":100,"wires":[["f1069bb8.a8e37"]]},{"id":"f1069bb8.a8e37","type":"csv","z":"edc2740c.af56f8","name":"Split CSV file by row","sep":",","hdrin":true,"hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":200,"y":160,"wires":[["3c69d3e0.0fc19c"]]},{"id":"81510010.f79c","type":"switch","z":"edc2740c.af56f8","name":"If 'payload.Name' not empty, save it, else pass along","property":"payload.Name","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":300,"y":320,"wires":[["ec158ecf.71583"],["4cd4a859.94e98"]]},{"id":"4cd4a859.94e98","type":"switch","z":"edc2740c.af56f8","name":"if UserId not null send it on","property":"payload.UserId","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":220,"y":380,"wires":[["815e63e8.7d053"]]},{"id":"815e63e8.7d053","type":"change","z":"edc2740c.af56f8","name":"Build row fron flow variable and add in UserId","rules":[{"t":"set","p":"UserId","pt":"msg","to":"payload.UserId","tot":"msg"},{"t":"delete","p":"payload","pt":"msg"},{"t":"set","p":"payload.Name","pt":"msg","to":"payload.Name","tot":"flow"},{"t":"set","p":"payload.OriginType","pt":"msg","to":"payload.OriginType","tot":"flow"},{"t":"set","p":"payload.UserId","pt":"msg","to":"UserId","tot":"msg"},{"t":"set","p":"payload.WorkOrderNo","pt":"msg","to":"payload.WorkOrderNo","tot":"flow"},{"t":"set","p":"payload.EstimatedHours","pt":"msg","to":"payload.EstimatedHours","tot":"flow"},{"t":"set","p":"payload.City","pt":"msg","to":"payload.City","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":440,"wires":[["a8a2e091.792758"]]},{"id":"3c69d3e0.0fc19c","type":"switch","z":"edc2740c.af56f8","name":"Send first row (column names) to new file","property":"parts.index","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":260,"y":220,"wires":[["d5820b92.e0374"],["81510010.f79c"]]},{"id":"88a43b06.8384d","type":"file","z":"edc2740c.af56f8","name":"Write column header row to new file","filename":"/Users/Paul/tmp/new.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":690,"y":260,"wires":[[]]},{"id":"5c2b7f6a.5b689","type":"file","z":"edc2740c.af56f8","name":"","filename":"Write column data row to new file","appendNewline":false,"createDir":false,"overwriteFile":"false","encoding":"none","x":240,"y":580,"wires":[["4e44da97.4c8cec"]]},{"id":"a8a2e091.792758","type":"csv","z":"edc2740c.af56f8","name":"convert object to CSV string","sep":",","hdrin":false,"hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":220,"y":500,"wires":[["5c2b7f6a.5b689"]]},{"id":"d5820b92.e0374","type":"change","z":"edc2740c.af56f8","name":"move columns to payload","rules":[{"t":"move","p":"columns","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"count","pt":"flow","to":"parts.count","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":180,"wires":[["88a43b06.8384d"]]},{"id":"d9872328.e6be","type":"comment","z":"edc2740c.af56f8","name":"Change the filename to point to the full path of output file","info":"","x":750,"y":220,"wires":[]},{"id":"ec158ecf.71583","type":"change","z":"edc2740c.af56f8","name":"Save W/O info to flow context","rules":[{"t":"set","p":"payload","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":670,"y":320,"wires":[[]]},{"id":"2de5104e.4e0f5","type":"comment","z":"edc2740c.af56f8","name":"Change the filename to point to the full path of output file","info":"","x":310,"y":540,"wires":[]},{"id":"4e44da97.4c8cec","type":"debug","z":"edc2740c.af56f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":170,"y":640,"wires":[]}]

Topic		Replies	Views
CSV and limiting output General	4	107	21 May 2023
Reading CSV and make a history General	6	189	6 September 2022
Read CSV DATA one by One General	13	6129	31 March 2020
Help - CSV node issue General	26	1495	21 April 2020
Getting an array from col1 and col 2 out of a csv file General	3	537	28 October 2022

CSV format issues

Related Topics