Bulk Importing Records

[MGeale]

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

[EddieE]

@MGeale 

You could try this

Set(//Removes and Substitutes Double Quotes (",") for Single Quotes (|)
    varPartiallyProcessedData_QuoteSubstitution,
    Substitute(
        varRawUnprocessedData_NewLineSplit,
        Char(34)&","&Char(34), 
        "|"
    )
);

Then use Split on your string, using the character '|' to split your data?

I found it a little hard to replicate your situation because I don't have access to your raw data, but I think this should work.

[MGeale]

Hi @EddieE,

That solves one of the problems, thanks!

Now how would I be able to iterate through each line of the user inputted data extracting the information now between the pipes? The thing has to be scalable...ie if the user has 10 items in their CSV file, it needs to loop through the data 10 times and extract each line item out now using the pipes as separators. That was the true issue that I was having trouble with... As I posted above, this was my attempt at it, but would only grab the first record inbetween the data. Now with your recommendation to the above logic, it grabs the entire row and populates (see following image)

[DesiredColumnName]: With(
                {
                    _Place: Last(
                        FirstN(
                            Split(
                                varPartiallyProcessedData_StringConversion,
                                ","
                            ),
                            XYZ //Where XYZ is the column point in the CSV data extract
                        )
                    ).Result
                },
                Right(
                    _Place,
                    Len(_Place) - Find(
                        "'",
                        _Place
                    )
                )
            )

Also if you want to have a crack at replicating it, the raw data is exactly the same as what I've provided just more specific (ie the actual user name, device ID etc).

Appreciate your help!

[EddieE]

@MGeale 

Is it just as simple as replacing the "'" with "|" in your formula?

[DesiredColumnName]: With(
                {
                    _Place: Last(
                        FirstN(
                            Split(
                                varPartiallyProcessedData_StringConversion,
                                ","
                            ),
                            XYZ //Where XYZ is the column point in the CSV data extract
                        )
                    ).Result
                },
                Right(
                    _Place,
                    Len(_Place) - Find(
                    // replaced below here
                        "|",
                        _Place
                    )
                )
            )

[MGeale]

Hi @EddieE,

Yeah Hilariously after I responded back, I figured it out.

Instead of it being what you said, it's this:

[DesiredColumnName]: With(
                {
                    _Place: Last(
                        FirstN(
                            Split(
                                varPartiallyProcessedData_StringConversion,
                                "|"
                            ),
                            XYZ //Where XYZ is the column point in the CSV data extract
                        )
                    ).Result
                },
                Right(
                    _Place,
                    Len(_Place) - Find(
                        """",
                        _Place
                    )
                )
            )

The above works mostly - when adding more than one line of data import, it seems to break on the latter half of the data source (ie after there are a few blank strings indicative of the data import).

If you know a quick fix to that, it'd be swell.

Thanks a bunch for your help 🙂

[EddieE]

@MGeale 

Nice 🙂

Could you use an If() statement on your Right() function? ie

[DesiredColumnName]: With(
                {
                    _Place: Last(
                        FirstN(
                            Split(
                                varPartiallyProcessedData_StringConversion,
                                "|"
                            ),
                            XYZ //Where XYZ is the column point in the CSV data extract
                        )
                    ).Result
                },
                If( Len(_Place) > 0,
                  // then
                    Right( _Place, Len(_Place) - Find("""", _Place)),
                  // else
                  ""
               )
            )

[MGeale]

That did it too!

So close now...just this one little chestnut that's breaking it

12799 is suppose to be the start of a new row...for some reason, the code isn't picking up the last group of information from the inputted data (look at example)...by that its not picking up "Animal1, Animal2, Animal3" [New Line]

[EddieE]

@MGeale 

Going back to my first response, would this work ( sorry, for guessing I'm still a little unsure of the different states of your data throughout the process)?

Set(//Removes and Substitutes Double Quotes (",") for Single Quotes (|)
    varPartiallyProcessedData_QuoteSubstitution,
    Substitute(
           Substitute( varRawUnprocessedData_NewLineSplit, Char(34)&","&Char(34), "|" ),
           Char(34),"|"
    )
);

[MGeale]

Your first solution works much better - When I implemented what you just advised, it moved all columns one over and made one of the columns incorrect Info...

[MGeale]

This is what the new line looks like in the data with the most recent solution (ie If Statement and the first response modification)