It does look like the Parse CSV module expects to have each row have the number of columns indicated in the configuration. If there are columns missing it refuses to parse the whole line, so starting with row 2696, since there are no trailing commas for empty columns the Parse CSV breaks.
The main issue with a text parser (which you could use) is it would explode the number of operations you would use to the number of lines in the CSV file so I really don’t think you want this.
So it’s back to 1 or find another CSV parsing module in another app that does not depend on the number of columns to be always availabel in every row. In theory the best policy would be to import UP to the max number of columns and keep empty columns not made avavailable in the file empty even if there are no trailing commas in the file. This is after all how Excel would handle a file like this without complaint.
The below scenario snippet simply pads the lines that end with " with the right number of empty commas, assuming these rows are in correct. You could potentially create a regex to find rows with less than 9 columns and pad with the right number of commas but they would need to be at the end which is the only policy you could follow for rows like this.
View Scenario JSON Blueprint
{
"subflows": [
{
"flow": [
{
"id": 1,
"module": "http:ActionGetFile",
"version": 3,
"parameters": {
"handleErrors": false
},
"mapper": {
"url": "https://www.freddiemac.com/pmms/docs/PMMS_history.csv",
"method": "get",
"serializeUrl": false,
"shareCookies": false
},
"metadata": {
"designer": {
"x": 0,
"y": 0
},
"restore": {},
"parameters": [
{
"name": "handleErrors",
"type": "boolean",
"label": "Evaluate all states as errors (except for 2xx and 3xx )",
"required": true
}
],
"expect": [
{
"name": "url",
"type": "url",
"label": "URL",
"required": true
},
{
"name": "serializeUrl",
"type": "boolean",
"label": "Serialize URL",
"required": true
},
{
"name": "method",
"type": "hidden",
"label": "Method"
},
{
"name": "shareCookies",
"type": "boolean",
"label": "Share cookies with other HTTP modules",
"required": true
}
]
}
},
{
"id": 2,
"module": "regexp:Replace",
"version": 1,
"parameters": {},
"mapper": {
"pattern": "\"{{carriagereturn}}{{newline}}",
"value": "\",,,,,{{carriagereturn}}{{newline}}",
"global": true,
"sensitive": true,
"multiline": true,
"singleline": false,
"text": "{{1.data}}"
},
"metadata": {
"designer": {
"x": 300,
"y": 0
},
"restore": {
"expect": {
"global": {
"mode": "chose"
},
"sensitive": {
"mode": "chose"
},
"multiline": {
"mode": "chose"
},
"singleline": {
"mode": "chose"
}
}
},
"expect": [
{
"name": "pattern",
"type": "text",
"label": "Pattern",
"required": true
},
{
"name": "value",
"type": "text",
"label": "New value"
},
{
"name": "global",
"type": "boolean",
"label": "Global match",
"required": true
},
{
"name": "sensitive",
"type": "boolean",
"label": "Case sensitive",
"required": true
},
{
"name": "multiline",
"type": "boolean",
"label": "Multiline",
"required": true
},
{
"name": "singleline",
"type": "boolean",
"label": "Singleline",
"required": true
},
{
"name": "text",
"type": "text",
"label": "Text"
}
]
}
},
{
"id": 3,
"module": "csv:ParseCSV",
"version": 1,
"parameters": {
"colCount": 9,
"csvContainsHeaders": true,
"delimiterType": "other",
"relax": false,
"delimiter": ","
},
"mapper": {
"csv": "{{2.text}}"
},
"metadata": {
"designer": {
"x": 600,
"y": 0,
"messages": [
{
"category": "last",
"severity": "warning",
"message": "A transformer should not be the last module in the route."
}
]
},
"restore": {
"parameters": {
"delimiterType": {
"label": "Other"
}
}
},
"parameters": [
{
"name": "colCount",
"type": "number",
"label": "Number of columns",
"required": true
},
{
"name": "csvContainsHeaders",
"type": "boolean",
"label": "CSV contains headers",
"required": true
},
{
"name": "delimiterType",
"type": "select",
"label": "Delimiter",
"required": true,
"validate": {
"enum": [
",",
"\t",
"other"
]
}
},
{
"name": "relax",
"type": "boolean",
"label": "Preserve quotes inside unquoted field",
"required": true
},
{
"name": "delimiter",
"type": "text",
"label": "Delimiter character",
"validate": {
"max": 1,
"min": 1
},
"required": true
}
],
"expect": [
{
"name": "csv",
"type": "text",
"label": "CSV",
"required": true
}
],
"interface": [
{
"name": "col1",
"label": "date",
"type": "text"
},
{
"name": "col2",
"label": "pmms30",
"type": "text"
},
{
"name": "col3",
"label": "pmms30p",
"type": "text"
},
{
"name": "col4",
"label": "pmms15",
"type": "text"
},
{
"name": "col5",
"label": "pmms15p",
"type": "text"
},
{
"name": "col6",
"label": "pmms51",
"type": "text"
},
{
"name": "col7",
"label": "pmms51p",
"type": "text"
},
{
"name": "col8",
"label": "pmms51m",
"type": "text"
},
{
"name": "col9",
"label": "pmms51spread",
"type": "text"
}
]
}
}
]
}
],
"metadata": {
"version": 1
}
}
