CSV to JSON V3.2 issues

[AfonsoTanida]

Hello, I am trying to use a CSV to JSON template that I downloaded from here . I have to use this template because my csv file contains commas. 

The issue is that in the "Select CSV to JSON" step, the "From:" field is producing no inputs. I'm not sure where I went wrong. Any help would be appreciated, thanks.

Here are some screenshots for you guys.

Issue Screenshot:

More Screenshots:

Let me know if any more screenshots are necessary. Thanks.

[AfonsoTanida]

The "From:" field code w/ screenshots.

take(skip(split(outputs('Compose_Reformat_back_to_CSV_with_new_delimiter'), outputs('Compose_CSV_data_+_Set-up')['LineBreak']), outputs('Compose_CSV_data_+_Set-up')['HeaderRowsToSkip']), sub(length(skip(split(outputs('Compose_Reformat_back_to_CSV_with_new_delimiter'), outputs('Compose_CSV_data_+_Set-up')['LineBreak']), outputs('Compose_CSV_data_+_Set-up')['HeaderRowsToSkip'])), outputs('Compose_CSV_data_+_Set-up')['FooterRowsToSkip']))

[takolota]

@AfonsoTanida 

Why did you want the new delimiter to be a comma , ??

That part of the template is all about changing the delimiter to something other than a comma so the later Select action can properly parse the CSV data.

What happens if you use the default bar | as the “NewDelimiter” input?

[AfonsoTanida]

I did have it as that an I got the same issue, I was just testing things out.

[takolota]

@AfonsoTanida 

Okay in that case could you share more of your output from the Reformat action?

I suspect it’s not finding the regular \r\n to split based on the CSV linebreaks, & it skips the 1st header row it gets in that piece, so it’s not splitting into multiple items, everything stays in the 1st item, then it skips that 1 & only item.

So we need to see if your linebreak characters are different, maybe it is just \n.

Then we can change the linebreak characters input in the settings action to adjust for that.

[AfonsoTanida]

Thank you so much for helping me. Here is a screenshot and Input/Output from the previous "Compose Reformat back to CSV" step. (It definitely looks incorrect)

Screenshot:

Input: 

"ID,Description,Status,Planned Start Date,Planned Finish Date,Type,Permissions,Project Manager,CSR,Billing type,Job type,Department name,Department ID,Budgeted duration (hours),Budgeted billing,Budgeted Cost Employee Cost,Sage Status,Task,Customer,Customer Number;1023-20-402-CIVL,Concrete Repair,Inactive,06/18/2020,12/31/2020,P;R;C,Civil=Superintendent,Covan, Matthew W.,Banda, Helen,Fixed fee,Repair,Civil,500,0.00,0.00,,Completed,GEN,Flint Hills Resources Houston Chem, LLC,1023;1127-20-312-TRAN,Insulation,Inactive,12/15/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Jennings, Danna,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Odfjell Terminals Houston,1127;1259-20-002-ENVI,Crude Oil Spill,Inactive,12/17/2020,02/28/2021,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Petromax Refining Company, LLC,1259;1023-20-409-CIVL,Concrete Repair - Firehouse,Inactive,01/11/2021,01/18/2021,P;R;C,Civil=Superintendent,Covan, Matthew W.,Banda, Helen,Fixed fee,Repair,Civil,500,386.00,52200.00,20410.00,Completed,GEN,Flint Hills Resources Houston Chem, LLC,1023;1223-20-009-STER,Pellet Tank Stair Fabrication,Inactive,12/20/2020,03/31/2021,P;R;C,Structural Steel=Superintendent,Ramos, Jaime,Quintero, Christina,Fixed fee,Repair,Structural Steel,501,60.00,98800.00,1200.00,Completed,GEN,Chevron Philips Chemical Co,1223;1239-20-027-TRAN,Sandblasting T-706; T607,Inactive,12/19/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1223-20-010-CIVL,Utility Cooling Tower,Inactive,12/18/2020,01/31/2021,P;R;C,Civil=Superintendent,Covan, Matthew W.,Quintero, Christina,Fixed fee,Repair,Civil,500,0.00,56335.56,,Completed,GEN,Chevron Philips Chemical Co,1223;1239-20-018-MAIN,December Outage - R-3,Inactive,11/20/2020,12/31/2020,P;R;C,Maintenance - Enviro=Superintendent,Cortez, Adan,Banda, Helen,Time and material,Repair,Maintenance - Enviro,1000,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1221-20-088-ENVI,Frac 6 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1051-20-168-TRAN,Tank 200-7 - (2) 12'x50' HDPE Containments,Inactive,11/01/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,KM Bostco,1051;1122-20-222-TRAN,Styrene Leak Repair Hydromud,Inactive,11/02/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC - Bayport,1122;1221-20-086-ENVI,Frac 1, 2, 3 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1221-20-087-ENVI,Frac 4 & 5 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1256-20-001-CIVL,Tank A-19 Diked Area Fill & Paving,Inactive,12/21/2020,03/27/2021,P;R;C,Civil=Superintendent,Garcia, Cesar E.,Quintero, Christina,Fixed fee,Repair,Civil,500,456.00,118025.00,17840.00,Completed,GEN,American Integrated Systems, Inc.,1256;1008-20-040-CIVL,Barge Dock 3 & 4 Crushed Concrete Repair,Inactive,11/19/2020,11/30/2020,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Civil,500,0.00,6495.00,1885.00,Completed,GEN,Kinder Morgan - Pasadena,1008;1239-20-019-MAIN,December Outage - Boiler Water Feed,Inactive,11/20/2020,12/31/2020,P;R;C,Maintenance - Enviro=Superintendent,Cortez, Adan,Banda, Helen,Time and material,Repair,Maintenance - Enviro,1000,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1122-20-223-TRAN,GF-349 I5 Filter Flush,Inactive,11/20/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC - Bayport,1122;1008-20-041-TRAN,Tank 30-3 - Tank Bottoms,Inactive,11/20/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Kinder Morgan - Pasadena,1008;1066-20-051-CIVL,DPRT Rail Expansion Civil,Inactive,11/24/2020,04/30/2021,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,New Construction,Civil,500,1673.00,222700.00,93600.00,Completed,GEN,BMWC Constructors Inc. (formerly ESW),1066;1012-20-165-STER,JSTR/Exxon Erect-A-Step Platforms,Inactive,11/20/2020,12/31/2020,P;R;C,Structural Steel=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Structural Steel,501,291.00,30700.00,11500.00,Completed,GEN,Kinder Morgan Truck Rack,1012;1119-20-141-TRAN,Tank B-600 - SBS,Inactive,11/25/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Jennings, Danna,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,LBC Houston,1119;1089-20-152-TRAN,Railroad Ties,Inactive,11/30/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC -Texas City,1089;1258-20-001-CIVL,Tank 200-3,Inactive,11/16/2020,11/30/2020,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Civil,500,30.00,9500.00,1500.00,Completed,GEN,Don Miller & Associates, Inc,1258;1051-20-170-TRAN,Tank 150-005 Sandblast Sand,Inactive,12/03/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew#_4#,"

Output:

"ID,Description,Status,Planned Start Date,Planned Finish Date,Type,Permissions,Project Manager,CSR,Billing type,Job type,Department name,Department ID,Budgeted duration (hours),Budgeted billing,Budgeted Cost Employee Cost,Sage Status,Task,Customer,Customer Number;1023-20-402-CIVL,Concrete Repair,Inactive,06/18/2020,12/31/2020,P;R;C,Civil=Superintendent,Covan, Matthew W.,Banda, Helen,Fixed fee,Repair,Civil,500,0.00,0.00,,Completed,GEN,Flint Hills Resources Houston Chem, LLC,1023;1127-20-312-TRAN,Insulation,Inactive,12/15/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Jennings, Danna,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Odfjell Terminals Houston,1127;1259-20-002-ENVI,Crude Oil Spill,Inactive,12/17/2020,02/28/2021,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Petromax Refining Company, LLC,1259;1023-20-409-CIVL,Concrete Repair - Firehouse,Inactive,01/11/2021,01/18/2021,P;R;C,Civil=Superintendent,Covan, Matthew W.,Banda, Helen,Fixed fee,Repair,Civil,500,386.00,52200.00,20410.00,Completed,GEN,Flint Hills Resources Houston Chem, LLC,1023;1223-20-009-STER,Pellet Tank Stair Fabrication,Inactive,12/20/2020,03/31/2021,P;R;C,Structural Steel=Superintendent,Ramos, Jaime,Quintero, Christina,Fixed fee,Repair,Structural Steel,501,60.00,98800.00,1200.00,Completed,GEN,Chevron Philips Chemical Co,1223;1239-20-027-TRAN,Sandblasting T-706; T607,Inactive,12/19/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1223-20-010-CIVL,Utility Cooling Tower,Inactive,12/18/2020,01/31/2021,P;R;C,Civil=Superintendent,Covan, Matthew W.,Quintero, Christina,Fixed fee,Repair,Civil,500,0.00,56335.56,,Completed,GEN,Chevron Philips Chemical Co,1223;1239-20-018-MAIN,December Outage - R-3,Inactive,11/20/2020,12/31/2020,P;R;C,Maintenance - Enviro=Superintendent,Cortez, Adan,Banda, Helen,Time and material,Repair,Maintenance - Enviro,1000,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1221-20-088-ENVI,Frac 6 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1051-20-168-TRAN,Tank 200-7 - (2) 12'x50' HDPE Containments,Inactive,11/01/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,KM Bostco,1051;1122-20-222-TRAN,Styrene Leak Repair Hydromud,Inactive,11/02/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC - Bayport,1122;1221-20-086-ENVI,Frac 1, 2, 3 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1221-20-087-ENVI,Frac 4 & 5 - Sump & Diesel Containment Cleanout,Inactive,11/19/2020,12/31/2020,P;R;C,Environmental=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Environmental,Environmental,600,0.00,0.00,,Completed,GEN,Energy Transfer GC NGL Fractionators LLC,1221;1256-20-001-CIVL,Tank A-19 Diked Area Fill & Paving,Inactive,12/21/2020,03/27/2021,P;R;C,Civil=Superintendent,Garcia, Cesar E.,Quintero, Christina,Fixed fee,Repair,Civil,500,456.00,118025.00,17840.00,Completed,GEN,American Integrated Systems, Inc.,1256;1008-20-040-CIVL,Barge Dock 3 & 4 Crushed Concrete Repair,Inactive,11/19/2020,11/30/2020,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Civil,500,0.00,6495.00,1885.00,Completed,GEN,Kinder Morgan - Pasadena,1008;1239-20-019-MAIN,December Outage - Boiler Water Feed,Inactive,11/20/2020,12/31/2020,P;R;C,Maintenance - Enviro=Superintendent,Cortez, Adan,Banda, Helen,Time and material,Repair,Maintenance - Enviro,1000,0.00,0.00,,Completed,GEN,Multi-Chem Group LLC/Athlon,1239;1122-20-223-TRAN,GF-349 I5 Filter Flush,Inactive,11/20/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC - Bayport,1122;1008-20-041-TRAN,Tank 30-3 - Tank Bottoms,Inactive,11/20/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,Kinder Morgan - Pasadena,1008;1066-20-051-CIVL,DPRT Rail Expansion Civil,Inactive,11/24/2020,04/30/2021,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,New Construction,Civil,500,1673.00,222700.00,93600.00,Completed,GEN,BMWC Constructors Inc. (formerly ESW),1066;1012-20-165-STER,JSTR/Exxon Erect-A-Step Platforms,Inactive,11/20/2020,12/31/2020,P;R;C,Structural Steel=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Structural Steel,501,291.00,30700.00,11500.00,Completed,GEN,Kinder Morgan Truck Rack,1012;1119-20-141-TRAN,Tank B-600 - SBS,Inactive,11/25/2020,12/31/2020,P;R;C,Transportation-Field=Superintendent,Jennings, Danna,Moore, Sharon,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,LBC Houston,1119;1089-20-152-TRAN,Railroad Ties,Inactive,11/30/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew,Schexsnaider, Melissa,Time and material,Transportation,Transportation-Field,900,0.00,0.00,,Completed,GEN,INEOS Styrolution America LLC -Texas City,1089;1258-20-001-CIVL,Tank 200-3,Inactive,11/16/2020,11/30/2020,P;R;C,Civil=Superintendent,Carter, Seth A.,Quintero, Christina,Fixed fee,Repair,Civil,500,30.00,9500.00,1500.00,Completed,GEN,Don Miller & Associates, Inc,1258;1051-20-170-TRAN,Tank 150-005 Sandblast Sand,Inactive,12/03/2020,02/28/2021,P;R;C,Transportation-Field=Superintendent,Taylor, Matthew#_4#,"

[takolota · Accepted solution]

Alright it looks like your CSV just uses the \n linebreak without the \r return character. So in the settings action for the linebreak inputs you’ll need to remove one of the decodeuricomponent( ) expressions. Just leave the one decodeUriComponent(‘%0A’) expression.

That should get it splitting the linebreaks on the right character.

However because your data only uses the linebreak character \n, if any of your field values contains a linebreak, then the flow will also split on that in-data linebreak & error. So I hope you don’t have any multi-choice or multiline fields that include linebreaks.

[AfonsoTanida]

I'm getting a new error now in the "Parse JSON" step. The date format is MM/dd/yyyy

Screenshot of Fields:

Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            },
            "ID": {
                "type": "string"
            },
            "Description": {
                "type": "string"
            },
            "Status": {
                "type": "string"
            },
            "Planned Start Date": {
                "type": "string"
            },
            "Planned Finish Date": {
                "type": "string"
            },
            "Planned Work": {
                "type": "string"
            },
            "Actual Start Date": {
                "type": "string"
            },
            "Actual Finish Date": {
                "type": "string"
            },
            "Actual Work": {
                "type": "string"
            },
            "Bill Rate": {
                "type": "string"
            },
            "Customer": {
                "type": "string"
            },
            "Permissions": {
                "type": "string"
            },
            "Notes": {
                "type": "string"
            },
            "Project Manager": {
                "type": "string"
            },
            "CSR": {
                "type": "string"
            },
            "Billing Type": {
                "type": "string"
            },
            "Job Type": {
                "type": "string"
            },
            "Department Name": {
                "type": "string"
            },
            "Department ID": {
                "type": "string"
            },
            "Budgeted duration (hours)": {
                "type": "string"
            },
            "Budgeted Billing": {
                "type": "string"
            },
            "Budgeted Cost Employee Cost": {
                "type": "string"
            },
            "Sage Status": {
                "type": "string"
            },
            "Task": {
                "type": "string"
            },
            "Sage Customer": {
                "type": "string"
            },
            "Customer Number": {
                "type": "string"
            }
        },
        "required": [
            "Name",
            "ID",
            "Description",
            "Status",
            "Planned Start Date",
            "Planned Finish Date",
            "Customer",
            "Permissions",
            "Project Manager",
            "CSR",
            "Billing Type",
            "Job Type",
            "Department Name",
            "Department ID",
            "Budgeted duration (hours)",
            "Budgeted Billing",
            "Budgeted Cost Employee Cost",
            "Sage Status",
            "Task",
            "Sage Customer",
            "Customer Number"
        ]
    }
}

Output:

{"errors":[{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[0]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[1]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[2]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[3]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[4]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[5]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[6]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[7]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish Date.","lineNumber":0,"linePosition":0,"path":"[8]","value":["Planned Finish Date"],"schemaId":"#/items","errorType":"required","childErrors":[]},{"message":"Required properties are missing from object: Planned Finish 

Input:

{"content":[{"Name":"1023-20-402-CIVL:Concrete Repair","ID":"1023-20-402-CIVL","Description":"Concrete Repair","Status":"Inactive","Planned Start Date":"1023-20-402-CIVL","Planned FInish Date":"1023-20-402-CIVL","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1023-20-402-CIVL","Permissions":"1023-20-402-CIVL","Notes":"","Project Manager":"1023-20-402-CIVL","CSR":"1023-20-402-CIVL","Billing Type":"1023-20-402-CIVL","Job Type":"1023-20-402-CIVL","Department Name":"1023-20-402-CIVL","Department ID":"1023-20-402-CIVL","Budgeted duration (hours)":"1023-20-402-CIVL","Budgeted Billing":"1023-20-402-CIVL","Budgeted Cost Employee Cost":"1023-20-402-CIVL","Sage Status":"1023-20-402-CIVL","Task":"1023-20-402-CIVL","Sage Customer":"1023-20-402-CIVL","Customer Number":"1023-20-402-CIVL"},{"Name":"1127-20-312-TRAN:Insulation","ID":"1127-20-312-TRAN","Description":"Insulation","Status":"Inactive","Planned Start Date":"1127-20-312-TRAN","Planned FInish Date":"1127-20-312-TRAN","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1127-20-312-TRAN","Permissions":"1127-20-312-TRAN","Notes":"","Project Manager":"1127-20-312-TRAN","CSR":"1127-20-312-TRAN","Billing Type":"1127-20-312-TRAN","Job Type":"1127-20-312-TRAN","Department Name":"1127-20-312-TRAN","Department ID":"1127-20-312-TRAN","Budgeted duration (hours)":"1127-20-312-TRAN","Budgeted Billing":"1127-20-312-TRAN","Budgeted Cost Employee Cost":"1127-20-312-TRAN","Sage Status":"1127-20-312-TRAN","Task":"1127-20-312-TRAN","Sage Customer":"1127-20-312-TRAN","Customer Number":"1127-20-312-TRAN"},{"Name":"1259-20-002-ENVI:Crude Oil Spill","ID":"1259-20-002-ENVI","Description":"Crude Oil Spill","Status":"Inactive","Planned Start Date":"1259-20-002-ENVI","Planned FInish Date":"1259-20-002-ENVI","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1259-20-002-ENVI","Permissions":"1259-20-002-ENVI","Notes":"","Project Manager":"1259-20-002-ENVI","CSR":"1259-20-002-ENVI","Billing Type":"1259-20-002-ENVI","Job Type":"1259-20-002-ENVI","Department Name":"1259-20-002-ENVI","Department ID":"1259-20-002-ENVI","Budgeted duration (hours)":"1259-20-002-ENVI","Budgeted Billing":"1259-20-002-ENVI","Budgeted Cost Employee Cost":"1259-20-002-ENVI","Sage Status":"1259-20-002-ENVI","Task":"1259-20-002-ENVI","Sage Customer":"1259-20-002-ENVI","Customer Number":"1259-20-002-ENVI"},{"Name":"1023-20-409-CIVL:Concrete Repair - Firehouse","ID":"1023-20-409-CIVL","Description":"Concrete Repair - Firehouse","Status":"Inactive","Planned Start Date":"1023-20-409-CIVL","Planned FInish Date":"1023-20-409-CIVL","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1023-20-409-CIVL","Permissions":"1023-20-409-CIVL","Notes":"","Project Manager":"1023-20-409-CIVL","CSR":"1023-20-409-CIVL","Billing Type":"1023-20-409-CIVL","Job Type":"1023-20-409-CIVL","Department Name":"1023-20-409-CIVL","Department ID":"1023-20-409-CIVL","Budgeted duration (hours)":"1023-20-409-CIVL","Budgeted Billing":"1023-20-409-CIVL","Budgeted Cost Employee Cost":"1023-20-409-CIVL","Sage Status":"1023-20-409-CIVL","Task":"1023-20-409-CIVL","Sage Customer":"1023-20-409-CIVL","Customer Number":"1023-20-409-CIVL"},{"Name":"1223-20-009-STER:Pellet Tank Stair Fabrication","ID":"1223-20-009-STER","Description":"Pellet Tank Stair Fabrication","Status":"Inactive","Planned Start Date":"1223-20-009-STER","Planned FInish Date":"1223-20-009-STER","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1223-20-009-STER","Permissions":"1223-20-009-STER","Notes":"","Project Manager":"1223-20-009-STER","CSR":"1223-20-009-STER","Billing Type":"1223-20-009-STER","Job Type":"1223-20-009-STER","Department Name":"1223-20-009-STER","Department ID":"1223-20-009-STER","Budgeted duration (hours)":"1223-20-009-STER","Budgeted Billing":"1223-20-009-STER","Budgeted Cost Employee Cost":"1223-20-009-STER","Sage Status":"1223-20-009-STER","Task":"1223-20-009-STER","Sage Customer":"1223-20-009-STER","Customer Number":"1223-20-009-STER"},{"Name":"1239-20-027-TRAN:Sandblasting T-706; T607","ID":"1239-20-027-TRAN","Description":"Sandblasting T-706; T607","Status":"Inactive","Planned Start Date":"1239-20-027-TRAN","Planned FInish Date":"1239-20-027-TRAN","Planned Work":"","Actual Start Date":"","Actual Finish Date":"","Actual Work":"","Bill Rate":"","Customer":"1239-20-027-TRAN","Permissions":"1239-20-027-TRAN","Notes":"","Project Manager":"1239-20-027-TRAN","CSR":"1239-20-027-TRAN","Billing Type":"1239-20-027-TRAN","Job Type":"1239-20-027-TRAN","Department Name":"1239-20-027-TRAN","Department ID":"1239-20-027-TRAN","Budgeted duration (hours)":"1239-20-027-

[takolota · Accepted solution]

@AfonsoTanida 

You see this part of the JSON Schema?

"required": [
            "Name",
            "ID",
            "Description",
            "Status",
            "Planned Start Date",
            "Planned Finish Date",
            "Customer",
            "Permissions",
            "Project Manager",
            "CSR",
            "Billing Type",
            "Job Type",
            "Department Name",
            "Department ID",
            "Budgeted duration (hours)",
            "Budgeted Billing",
            "Budgeted Cost Employee Cost",
            "Sage Status",
            "Task",
            "Sage Customer",
            "Customer Number"
        ]

Instead blank it out…

"required": []

[AfonsoTanida]

You're a legend mate.

[AfonsoTanida]

Thank you for all the help. Everything is working now. I believe I just have one last question for you. How can I convert everything back into a csv file? The reason for this is because as you can see from my previous posts, I added columns and concatenations to the existing csv file. Thank you.

[takolota]

@AfonsoTanida 

There is a “Create CSV table” action you can use.

And that can actually take a JSON array as an input, so in the template instead of the Apply to each loop, you would just put the output of your Select or whatever action you used to add columns into the Create CSV table action.

From there you can put the output of the Create CSV table action in the File content input of a Create file action.

[AfonsoTanida]

You're amazing man. Thank you so much for all your help. The flow is completely done now.