cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
genarich
Helper V
Helper V

Exporting columns in CSV without arranging it alphabetically

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.

 

genarich_0-1630429164806.png

genarich_1-1630429176798.png

 

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. 😞

 

genarich_2-1630431486332.png

genarich_3-1630431547622.png

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

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.

View solution in original post

22 REPLIES 22
WarrenBelz
Super User
Super User

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.

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.

genarich_0-1630471096532.png

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

genarich_2-1630471523879.png

 

genarich_1-1630471512164.png

 

Your insights would really be helpful.

@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).

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' & "," &

 

genarich_1-1630852640885.png

 

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.

genarich_3-1630852918515.png

 

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)

genarich_4-1630853415874.png

 

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.

genarich_5-1630853810817.png

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.

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.

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...

 

genarich_0-1630984563601.png

but on the spreadsheet it shows as.... 

genarich_1-1630984620942.png

 

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)

 

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.

 

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.

 

genarich_1-1630997443806.png

 

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

 

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

Hi @genarich ,

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,010)