Power Platform Community Forum Thread Details

Hi all,

I will provide more context below, but at a high level I have two questions which are:

How can I automatically convert a flat file with no extension into a format that Dataverse can consume?
Once that flat file is converted, how can I perform CRUD activities on a target Dataverse database based on the data in this converted file? (explained in more detail below).

I have a comma delimited flat file (with no extension) with ~1000 records that comes to me from a data source once a week via SFTP and I need to automate a process to turn this flat file into some form of appropriate file for carrying out the tasks below with (excel, csv, etc).

The data in this converted file needs to be compared against a Dataverse database (target database) which has ~600,000 records, and CRUD activities need to be carried out accordingly. Out of these ~1000 records, ~800 might be new entries to the dataset, ~150 might be updates to existing records in the dataverse database, and ~50 could be deletions from the dataverse database.

I've attached a file with a sample of the data that comes through once a week via SFTP as a flat file. As you can see, there is a unique identifier string (the second column), and usually there is a field with 'RES', but sometimes this field will have 'CAN' instead (the first column). The 'CAN' marker indicates that the record should be deleted, and therefore I need to create a process to ensure this is deleted from the target database.

If there is a record in the file we have converted with a unique identifier string that doesn't exist in the target database, that new record should be added to the target database.

If there is a record in the file we have converted with a unique identifier string that matches the unique identifier string on a record in the target database, the record in the target database should be updated with the data from the converted file.

I would appreciate any advice you have on how to achieve these goals.

Thanks,

Karl