I am beating my head against a wall here for over a week about this flow and the repeating errors I am getting. It keeps failing when it is supposed to take the file from CSV and convert into JSON formatting. It is failing first in "split each line into columns" where, when testing, I get a "not specified" result. Which then causes "Debug" action to fail. Then it skips "Append to array variable(ParsedCSV)" because, from what I can only assume, it is not moving to "True" and thus defaulting to "False". I can provide the 'SampleData.csv' and 'TestTable.xlsx' files if need be since they are still a test environment. So here is my process:
When a new email arrives in a shared mailbox (V2)
Email address – email@email.com
Importance – Any
Only with attachments – Yes
Include attachments – Yes
Subject Filter – SWTest
Folder - Inbox
Initialize variable (CSVContent)
Name = CSVContent
Type = String
Value = *blank*
Initialize variable (ParsedCSV)
Name = ParsedCSV
Type = Array
Value = []
Apply to each - triggerOutputs()?['body/attachments']
Create file
/SonicWallLogTesting
File Name – items(‘Apply_to_each’)?[‘Name’]
File Content – item()?[‘contentBytes’]
Get file content
File – outputs(‘Create_file’)?[body/Id’]
Infer Content Type – Yes
Compose Split CSV into lines
split(string(outputs('Get_file_content')), '\n')
Compose skip first two lines
skip(outputs('split_CSV_into_lines'), 2)
Apply to each(split lines)
Outputs(‘skip_first_two_lines’)
Compose split each line into columns
split(item(), ',')
Compose Debug split results
Outputs(‘split_each_line_into_columns’)
Condition
AND length(outputs('split_each_line_into_columns')) equals 15
True >
Append to array variable(ParsedCSV)
{
"type": "AppendToArrayVariable",
"inputs": {
"name": "ParsedCSV",
"value": {
"Time": "@outputs('split_each_line_into_columns')[0]",
"ID": "@outputs('split_each_line_into_columns')[1]",
"Category": "@outputs('split_each_line_into_columns')[2]",
"Priority": "@outputs('split_each_line_into_columns')[3]",
"Src. Int.": "@outputs('split_each_line_into_columns')[4]",
"Dst. Int.": "@outputs('split_each_line_into_columns')[5]",
"Src. IP": "@outputs('split_each_line_into_columns')[6]",
"Src. Port": "@outputs('split_each_line_into_columns')[7]",
"Src. Name": "@outputs('split_each_line_into_columns')[8]",
"Dst. IP": "@outputs('split_each_line_into_columns')[9]",
"Dst. Port": "@outputs('split_each_line_into_columns')[10]",
"Dst. Name": "@outputs('split_each_line_into_columns')[11]",
"IP Protocol": "@outputs('split_each_line_into_columns')[12]",
"Notes": "@outputs('split_each_line_into_columns')[13]",
"Message": "@outputs('split_each_line_into_columns')[14]"
}
}
}
Compose Create CSV table
variables('ParsedCSV')
Columns - Automatic
Compose Before JSON
Body(‘Create_CSV_table’)
Compose Ensure JSON Format
concat('{"records":[', join(',', split(outputs('Before_JSON'), '\n')), ']}')
Compose After JSON
outputs(‘Ensure_JSON_Format’)
Compose Parse JSON
outputs(‘After_JSON’)
Schema
{
"type": "object",
"properties": {
"records": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Time": {
"type": "string"
},
"ID": {
"type": "integer"
},
"Category": {
"type": "string"
},
"Priority": {
"type": "string"
},
"Src. Int.": {
"type": "string"
},
"Dst. Int.": {
"type": "string"
},
"Src. IP": {
"type": "string"
},
"Src. Port": {
"type": "integer"
},
"Src. Name": {
"type": "string"
},
"Dst. IP": {
"type": "string"
},
"Dst. Port": {
"type": "integer"
},
"Dst. Name": {
"type": "string"
},
"IP Protocol": {
"type": "string"
},
"Notes": {
"type": "string"
},
"Message": {
"type": "string"
}
},
"required": [
"Time",
"ID",
"Category",
"Priority",
"Src. Int.",
"Dst. Int.",
"Src. IP",
"Src. Port",
"Dst. IP",
"Dst. Port",
"IP Protocol",
"Message"
]
}
}
}
}
Compose Create HTML table
outputs(‘Parse_JSON’)
Columns - Automatic
Compose Parse JSON 2
outputs(‘Create_HTML_table’)
Schema
{
"type": "array",
"items": {
"type": "object",
"properties": {
"Time": {
"type": "string"
},
"ID": {
"type": "integer"
},
"Category": {
"type": "string"
},
"Priority": {
"type": "string"
},
"Src. Int.": {
"type": "string"
},
"Dst. Int.": {
"type": "string"
},
"Src. IP": {
"type": "string"
},
"Src. Port": {
"type": "integer"
},
"Src. Name": {
"type": "string"
},
"Dst. IP": {
"type": "string"
},
"Dst. Port": {
"type": "integer"
},
"Dst. Name": {
"type": "string"
},
"IP Protocol": {
"type": "string"
},
"Notes": {
"type": "string"
},
"Message": {
"type": "string"
}
},
"required": [
"Time",
"ID",
"Category",
"Priority",
"Src. Int.",
"Dst. Int.",
"Src. IP",
"Src. Port",
"Dst. IP",
"Dst. Port",
"IP Protocol",
"Message"
]
}
}
List rows present in a table
Location – Onedrive for Business
Document Library – OneDrive
File - /SonicWallLogTesting/TestTable2.xlsx
Table – MainTable
NO ADVANCED PARAMETERS UTILIZED
Initialize variable RowsArray
Name – RowsArray
Type – Array
Value – []
Apply to each 2
Variables(‘RowsArray’)
Append to array variable 1
Name – RowsArray
Value
{
"Time": @{items('Apply_to_each_2')['Time']},
"ID": @{items('Apply_to_each_2')['ID']},
"Category": @{items('Apply_to_each_2')['Category']},
"Priority": @{items('Apply_to_each_2')['Priority']},
"Src. Int.": @{items('Apply_to_each_2')['Src. Int.']},
"Dst. Int.": @{items('Apply_to_each_2')['Dst. Int.']},
"Src. IP": @{items('Apply_to_each_2')['Src. IP']},
"Src. Port": @{items('Apply_to_each_2')['Src. Port']},
"Src. Name": @{items('Apply_to_each_2')['Src. Name']},
"Dst. IP": @{items('Apply_to_each_2')['Dst. IP']},
"Dst. Port": @{items('Apply_to_each_2')['Dst. Port']},
"Dst. Name": @{items('Apply_to_each_2')['Dst. Name']},
"IP Protocol": @{items('Apply_to_each_2')['IP Protocol']},
"Notes": @{items('Apply_to_each_2')['Notes']},
"Message": @{items('Apply_to_each_2')['Message']}
}
Create CSV table 1
Variables(‘RowsArray’)
Columns - Automatic
