Hi Guys,
Hoping someone could give me a bit of a hand with something or at least point me in the right direction. I've got a pretty large data source I'm building an app for in order to import it into a SQL database (exporting data out of existing database and reimporting into SQL). The app's purpose is to give to the business so they can just get their existing data, CTRL C and CTRL V into the app, app will then process the data and then visualize it in a table before the end user presses' a button to bulk import into a SQL database.
An example of the data they'll be importing (raw export from their existing database) is as follows - some of the info has been removed for privacy reasons.
"12799","Location","User","DeviceID","No","2022-04-03 06:42:45","2022-04-03 06:47:45","2022-04-03 06:42:48","2022-04-03","06:42:48","2022","4","6","Animal1, Animal2, Animal3","5.00","6,6","600x600","","Continuous","Individual","Animal1","","","","","","","","","","","","","","CONTINUOUS BEHAVIOUR","Swimming","Alone","","","","8.00","Endeavour","N/A","N/A","Float","No","No","Before Activity","Animal1, Animal2, Animal3"
I've successfully modified the data to the point wherein the "" are replaced for single quotes ('') and is turned into a string, but I now want to access the data in between the ' '... i.e. Animal1, Animal2 and Animal3 are all meant to be in the column Project Animals but because of the logic from the export of their existing database, its splitting it out based on a "," of which is separating Animal1, Animal2, and Animal3.
This is the current logic I'm using and is on a per column basis. I'm wanting to access information between two individual quotes and just keep that information - this is the part that is not working. When running this code, it only returns one instance (Project Animals as an example again, it'll only return 'Animal1
[DesiredColumnName]: With(
{
_Place: Last(
FirstN(
Split(
varPartiallyProcessedData_StringConversion,
","
),
14
)
).Result
},
Right(
_Place,
Len(_Place) - Find(
"'",
_Place
)
)
)
This is the code I use to get their raw processed data into the varPartiallyProcessedData_StringConversion variable... Don't know if the string conversion is even necessary to be honest (unsure myself).
Set(//Imports Raw Unprocessed Data from TextInput1
varRawUnprocessedData,
TextInput1.Text
);
Set(//Splits the TextInput1 Input based on a whether there is are New Line(s) present in the Input
varRawUnprocessedData_NewLineSplit,
Split(
varRawUnprocessedData,
Char(10)
).Result
);
Set(//Removes and Substitutes Double Quotes ("") for Single Quotes ('')
varPartiallyProcessedData_QuoteSubstitution,
Substitute(
varRawUnprocessedData_NewLineSplit,
Char(34),
"'"
)
);
Set(//Converts varPartiallyProcessedData_QuoteSubstitution into a String
varPartiallyProcessedData_StringConversion,
Concat(
varPartiallyProcessedData_QuoteSubstitution,
Result,
Char(10)
)
);
If anyone can think of anything or advise if my code could be better written out, that'd be great. End goal is to just give them this app, they dump their data in, app then processes the data and extracts the information between the "XYZ".
There will eventually be a flow that is linked to this, but the above needs to be displayed in a data table first before so if there are any issues, the end user can modify or double check before it goes into the SQL database which they do not have access to.
Thanks in advance, sorry for the long post!
Cheers
