Exporting columns in CSV without arranging it alphabetically

[genarich]

Need some help! My goal is to extract a CSV file with columns that are arranged in the sequence I like.

The below is currently my code. Apparently, once it has been inserted in a Collection, the columns would be arranged alphabetically.

I asked Shane Young and Reza Dorrani on their YT channels and below were their replies. I am not really sure how this would be accomplished. 😞

[WarrenBelz · Accepted solution]

Hi @genarich ,

It helps to post your code in Text (the below was OCR'd, so check spelling), but have you tried the "long" way - you would have to change your Flow to simply accept the output of this as the file content.

ForAll(
   galOrderStatusList.AllItems As aExport,
   Collect(
      colDataExported,
      {
         'Order Date': aExport.'Date Opened',
         'Part Number’: aExport.'Part# Requested',
         'Part Description': aExport.'Part Description', 
         'Consumables?': aExport.'Consumables?',
         Task: aExport.Task,
         Quantity: aExport.Quantity, 
         uom: aExport.UoM,
         'Delivered Quantity': aExport.’Delivered Quantity', 
         'Defect Ref/SB': aExport.'Defect Ref/ SB',
         'AC Tail Num': aExport.'A/C Tail#'.Value,
         RDD: aExport.RDD,
         Priority : aExport.Priority.Value,
         'Requested By': aExport.’Requested By',
         'Sales Order': aExport.'Sales Order',
         Line: aExport.Line,
         'Tracking Ref': aExport.'Tracking Ref',
         EDD: aExport.EDD,
         'Reason for No EDD': aExport.'Reason for no EDD’, 
         'Issued To’: aExport.'Issued to',
         'Date Completed': aExport.'Date Completed',
         'Order Status’: aExport.'Order Status'.Value,
         Notes : aExport. Notes
      }
   )
);
Set(
   varExportFile,
   Concat(
      colExportedData,
      'Order Date' & "," & 'Date Opened' & "," & 'Part Number’ & "," &
      'Part Description' & "," & 'Consumables?' & "," & Task & "," &
       Quantity & "," & uom & "," & 'Delivered Quantity' & "," &
       'Defect Ref/SB' & "," & 'AC Tail Num' & "," & RDD & "," &
       Priority & "," & 'Requested By' & "," & 'Sales Order' & "," &
       Line & "," & 'Tracking Ref' & "," & EDD & "," &
       'Reason for No EDD': & "," & 'Issued To’ & "," &
       'Date Completed' & "," & 'Order Status’ & "," & Notes & Char(10)
   )
);
'ExportDataCSV-FWSAR'.Run(varExportFile)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[genarich]

Hi @WarrenBelz , thanks for replying! I tried using your code, and below was the final one. As I used the below code, I encountered two issues. Please see below images.

ForAll(
galOrderStatusList.AllItems,
Collect(
colDataExported,
{
'Order Date': ThisRecord.'Date Opened',
'Part Number': ThisRecord.'Part# Requested',
'Part Description': ThisRecord.'Part Description',
'Consumables?': ThisRecord.'Consumables?',
'Task': ThisRecord.Task,
'Quantity': ThisRecord.Quantity,
'UOM': ThisRecord.UoM,
'Delivered Quantity': ThisRecord.'Delivered Quantity',
'Defect Ref/SB': ThisRecord.'Defect Ref/ SB',
'AC Tail Num': ThisRecord.'A/C Tail#'.Value,
'RDD': ThisRecord.RDD,
'Priority': ThisRecord.Priority.Value,
'Requested By': ThisRecord.'Requested By',
'Sales Order': ThisRecord.'Sales Order',
'Line': ThisRecord.Line,
'Tracking Ref': ThisRecord.'Tracking Ref',
'EDD': ThisRecord.EDD,
'Reason for No EDD': ThisRecord.'Reason for no EDD',
'Issued To': ThisRecord.'Issued to',
'Date Completed': ThisRecord.'Date Completed',
'Order Status': ThisRecord.'Order Status'.Value,
'Notes': ThisRecord.Notes
}
)
);
Set(
varExportFile,
Concat(
colDataExported,
'Order Date' & "," &
'Part Number' & "," &
'Part Description' & "," &
'Consumables?' & "," &
Task & "," &
Quantity & "," &
UOM & "," &
'Delivered Quantity' & "," &
'Defect Ref/SB' & "," &
'AC Tail Num' & "," &
RDD & "," &
Priority & "," &
'Requested By' & "," &
'Sales Order' & "," &
Line & "," &
'Tracking Ref' & "," &
EDD & "," &
'Reason for No EDD' & "," &
'Issued To' & "," &
'Date Completed' & "," &
'Order Status' & "," &
Notes & Char(10)
)
);
'ExportdatatoCSV-FWSAR'.Run(varExportFile)
/* JSON(
varExportFile,//colDataExported,
JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes
)
).filelink*/
;
//Download(varExportFile);
Clear(colDataExported)

1. 'Consumables?' is showing error, which I am not sure why. Upon checking above code, the name is correct.

2. I commented 'Consumables?' out and tried running it and found another error.

Your insights would really be helpful.

[WarrenBelz]

@genarich ,

What type of field is Consumables?

As mentioned, you will also have to change your flow to simply create the CSV file from the parameter (there is no JSON conversion needed).

[genarich]

Hi @WarrenBelz Thank you for being awesome!! I made the Consumables work and added ".value" at the end. Also, as requested, I have modified the flow and removed the JSON conversion. Downloading/exporting the data worked, however, there are 4 related concerns I wanted to address. I'm hoping you would be able to help me on this.

1. The code worked but it didn't include headers. So I tried adding the below code, but I think I am missing something because when I tried exporting it the format was off..

Set(
varExportFile,
Concat(
colDataExported,
"Order Date" & Char(10) & 'Order Date' & "," &
"Part Number" & Char(10) & 'Part Number' & "," &

2. There used to be a direct spreadsheet access after exporting the file on top. Currently, with the new code, it just went to the respective folder I set it to, without notifying the user if the export was complete. Now, I need to manually go to that folder to check whether the file has been downloaded or not.

3. The Notes section contain multiline values, when I tried adding it into the code. The spreadsheet format got disordered.

Column A Line 1 - Order Date field

Column B Line 1 - Part Number field

Column A Lines 2-6 & Column B Line 2 (This should be in one cell)

4. Since this is a collection, it needs to be cleared for a new refresh to happen. Currently, when I tried adding the below code at the end, it didn't let me.

The goal of this is that whatever is in the gallery (galOrderStatusList) will only be the values that will be exported. There are filters working backend on this. So values shown in this gallery is dynamic. Right now, it doesn't reflect that since it downloaded everything.

[WarrenBelz]

Hi @genarich ,

I am not on a PC right now to send code but all you need to do is put the headers separated by Commers before the contact function and that it will added at the top.

[genarich]

Thank you! It worked!

But how do I address those fields that has a comma and take it as a delimiter?

For example, I have "Part Description" below that has comma (,) in it...

but on the spreadsheet it shows as.... 

Now moving the rest of the fields into the next column.

Below is my code for reference:

ForAll(
    galOrderStatusList.AllItems,
    Collect(
        colDataExported,
        {
            'Order Date': ThisRecord.'Date Opened',
            'Part Number': ThisRecord.'Part# Requested',
            'Part Description': ThisRecord.'Part Description',
            'Consumables?': ThisRecord.'Consumables?',
            Task: ThisRecord.Task,
            Quantity: ThisRecord.Quantity,
            UOM: ThisRecord.UoM,
            'Delivered Quantity': ThisRecord.'Delivered Quantity',
            'Defect Ref/SB': ThisRecord.'Defect Ref/ SB',
            'AC Tail Num': ThisRecord.'A/C Tail#'.Value,
            RDD: ThisRecord.RDD,
            Priority: ThisRecord.Priority.Value,
            'Requested By': ThisRecord.'Requested By',
            'Sales Order': ThisRecord.'Sales Order',
            Line: ThisRecord.Line,
            'Tracking Ref': ThisRecord.'Tracking Ref',
            EDD: ThisRecord.EDD,
            'Reason for No EDD': ThisRecord.'Reason for no EDD',
            'Issued To': ThisRecord.'Issued to',
            'Date Completed': ThisRecord.'Date Completed',
            'Order Status': ThisRecord.'Order Status'.Value,
            Notes: ThisRecord.Notes
        }
    )
);
Set(
    varExportFile,
        "Order Date" & "," & 
        "Part Number" & "," & 
        "Part Description" & "," & 
        "Consumables?" & "," & 
        "Task" & "," & 
        "Quantity"& "," & 
        "UOM"& "," & 
        "Delivered Quantity" & "," & 
        "Defect Ref/SB" & "," & 
        "AC Tail Num" & "," & 
        "RDD" & "," & 
        "Priority" & "," & 
        "Requested By" & "," & 
        "Sales Order" & "," & 
        "Line" & "," & 
        "Tracking Ref" & "," & 
        "EDD" & "," & 
        "Reason for No EDD" & "," & 
        "Issued To" & "," & 
        "Date Completed" & "," & 
        "Order Status" & Char(10) & 
    Concat(
        colDataExported,
        'Order Date' & "," & 
        'Part Number' & "," & 
        'Part Description' & "," & 
        'Consumables?'.Value & "," & 
        Task & "," & 
        Quantity & "," & 
        UOM & "," & 
        'Delivered Quantity' & "," & 
        'Defect Ref/SB' & "," & 
        'AC Tail Num' & "," & 
        RDD & "," & 
        Priority & "," & 
        'Requested By' & "," & 
        'Sales Order' & "," & 
        Line & "," & 
        'Tracking Ref' & "," & 
        EDD & "," & 
        'Reason for No EDD' & "," & 
        'Issued To' & "," & 
        'Date Completed' & "," & 
        'Order Status' & Char(10)
        // Notes & ","
    )
);
'Copyof-ExportdatatoCSV-FWSAR'.Run(varExportFile)

[WarrenBelz]

Hi @genarich ,

Try this formula (it uses a space, but you can use whatever you want) and also the structure

ForAll(
   galOrderStatusList.AllItems As aCol,
   Collect(
      colDataExported,
      {
         'Order Date': aCol.'Date Opened',
         'Part Number': Substitute(aCol.'Part# Requested',","," ")
         'Part Description': aCol.'Part Description',
         'Consumables?': aCol.'Consumables?',
         Task: aCol.Task,
         Quantity: aCol.Quantity,
         UOM: aCol.UoM,
         'Delivered Quantity': aCol.'Delivered Quantity',
         'Defect Ref/SB': aCol.'Defect Ref/ SB',
         'AC Tail Num': aCol.'A/C Tail#'.Value,
         RDD: aCol.RDD,
         Priority: aCol.Priority.Value,
         'Requested By': aCol.'Requested By',
         'Sales Order': aCol.'Sales Order',
         Line: aCol.Line,
         'Tracking Ref': aCol.'Tracking Ref',
         EDD: aCol.EDD,
         'Reason for No EDD': aCol.'Reason for no EDD',
         'Issued To': aCol.'Issued to',
         'Date Completed': aCol.'Date Completed',
         'Order Status': aCol.'Order Status'.Value,
         Notes: aCol.Notes
      }
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[genarich]

Thank you! It worked I was also able to add it to the rest of the fields that require fixing, except for the Notes field.

Is there a way that I can merge the highlighted ones into one cell?? The Notes section has multiline values but it kept separating on the extracted spreadsheet.

I used the below code, but I think I am missing something..

Notes: Substitute(aCol.Notes,Char(13)," ")

[WarrenBelz]

Hi @genarich ,

Try Char(10) - I cannot test it presently, but have used this before