Import Data using Excel/PDF in Power Apps

[Sidhant_02]

Hi everyone,
Currently to take in user inputs for registration purpose I am using a simple form structure where in user can enter the details as required. 
For example: Qualification

There is a gallery wherein I am showing all the data that the user has entered:

Now to make this more simpler what I want to do is add a button named Import from File, where in there will be the qualifications details for the user and using the file that is uploaded it should save all the details similar how we did using the form (which should be associated only for the current user not others: for which in case of forms I have used the MS365.Id and the Employee Id field).

Filter(SkillMatrix_Certifications,EmployeeId= Office365Users.MyProfile().Id)

My form has multiple sections like certification details, project details, skills, qualifications. So currently I am trying to import one section from the file, but later on if we have to import all the data in their respective field like qualifications should populate in its field not others, how to do that (is it part of AI builder) - {this can be done later}
For now I am focusing one part (qualifications}, in this case if anyone has any idea on how to solve this please do reply it will be helpful.

Regards,
Sidhant.

[Sidhant_02]

Hi @Pstork1 ,
You have mentioned that uploading the file and getting the content cannot be done in one flow for that two flows are required. So let's I create one flow to upload the file to SP document library like this:

And then other flow were you referring something like to this:

But now how will this flow be called like do we need to run it on any button On-select?. And then how will the contents extracted from the file uploaded (without using Response).
Is there anything I missed or is it incorrect?

Regards,
Sidhant.

[Pstork1]

Not quite. I said that uploading the file is what triggers the flow to get the content. You don't need a flow to upload the file to SharePoint. Uploading files to SharePoint is built in to the product. If you want to create a Power App to upload the file to SharePoint then you need a flow. But that is not an easy App and flow to create so I would recommend using the built-in mechanism for uploading the file.

[Sidhant_02]

Hi @Pstork1 
What's the in-built mechanism you are referring could you share the steps for it (every user will be uploading a excel file which will be having a fixed format headers will be defined, the user just needs to fill their details and once done upload the file in our case to SP document library) and then extract the content and show it in Power apps (so the 2nd flow that I have created {the trigger one is that correct, if so how to use it to show values in Power Apps}} or some changes are needed.

Regards,
Sidhant.

[Pstork1]

There is an upload link in every SharePoint Document library. That's what I mean by the built-in method.

[Sidhant_02]

Okay, but in my case this will be somewhat different, that uploading will done in Power apps (which you mentioned will require a flow) as the default mechanism is done from SharePoint side but the end users wont be accessing the SharePoint, so I have a flow from which I am able to upload the file to SharePoint document library successfully which is:

Using the flow above a file gets uploaded to Sharepoint document library (which works similar to the in-built upload button provided SharePoint), now the next part that you mentioned that once the file is uploaded the trigger (When a file is created or modified) how that is to approached?.

Regards,
Sidhant

[Pstork1]

As I've already mentioned the When a File is Created or Modified trigger will fire when a file is uploaded. It doesn't matter whether the user does it directly or you do it using an App/flow. The upload is separate. But once the upload completes that trigger will trigger the flow to process the file.  I've explained all this several times before.

[Sidhant_02]

Ok, but I was saying whether the trigger flow that I had designed earlier and shared is that right or are there any more steps required in the flow

[Pstork1]

Once the file is uploaded by whatever means that is the right trigger.

[Sidhant_02]

Hi @Pstork1 
Okay got it. I have designed 2 flows: 
In the first flow using the attachment control the excel file is uploaded to Sharepoint document library and then all the contents inside it are extracted and stored in another SP list which has the exact same columns as in the excel

After the extraction is done data is saved in the following SharePoint list:

In Power Apps:

Once the data is uploaded I have used a gallery that will show the record (so that user can verify whether the details are imported properly or not) and then on submit button I am making using of ForAll expression and Patch statement to Patch the respective fields in their respective SP list (as mentioned in post question).

//Mapping Qualifications
ForAll(Details,
Patch(Employee_Qualifications,
Defaults(Employee_Qualifications),
{
    Institutename: ThisRecord.University,
    Qualificationtitle:ThisRecord.Degree,
    EmployeeId:Office365Users.MyProfile().Id
}
)
);

Currently in the SharePoint list I am just having a single record so it is working fine but later when multiple users will use the import functionality the sharepoint list will have more than 1 record. So in that case II had some questions:

1. Currently in the gallery I am referring to the 'Details' SharePoint list, if I just want to show only the record that is associated with that particular user in that case what changes do I need to make on the Items property of the gallery?.

2. To differentiate different records (like Skills, Qualifications) I am making using of the Office365.Users().Id which helps in differentiating the records (belonging to a particular user). So similarly if I want to map the record uploaded (with the help of excel file), I have tried this:

On-Visible property of the screen I have initialized a local variable which stores the current user id and then I am comparing the same variable with User id (on the final submit button)

//Mapping Project Details:
If(currentUser = Office365Users.MyProfile().Id,

ForAll(Details,
    Patch(Employee_Projects,
    Defaults(Employee_Projects),
    {
        Title:ThisRecord.Project1,
        Description:ThisRecord.Description1,
        EmployeeId:Office365Users.MyProfile().Id
    }
    )
),
Notify("User Id mismatch",NotificationType.Information)
)

I am not sure whether it is correct or not, but here I just want to patch only the record that is uploaded by current user.

3. This is regarding multiple uploads:
(Currently I am testing and developing the app so I am able to upload n files that are having same id (office365.Users().id), but in real-time I don't want this to happen that is one user can only use the import functionality once not more than that otherwise we will have multiple records associated with the same id. So this type of check how can I add in my existing structure?.

4. How to provide an option for users to download the template (excel file template which only includes the headers), like on click of the button it will download the excel template file and then during upload check whether the format is as per the template and not any other format

Regards,
Sidhant.

[Pstork1]

1) You can filter the gallery based on the user's email currently running the app. Since the file is being uploaded by Power Apps the modified by field of the SharePoint list should match the user doing the upload.

2) Since the user is uploading the file you already have access to their unique email address. There is no need to use Office365 users.id

3) Check to see if the SharePoint list has any records modified by the user.  If it does then its a duplicate upload.

4) Post the template file to a SharePoint document library and add a url for the file to a button. That should let the users open the file in Excel Online. From there they can edit it and save it or download it.  If the file doesn't match the table format then an error will be thrown when you try to list rows. Use a parallel branch and run after settings to intercept the error.