CSV Filte to SQLite database

Tagged:
  • Dimitri Miller
        Has successfully completed the online course Basics (100)
      2 years ago #29990

      Hello

       

      After uploading a CSV I would like to update my SQLite entries. I couldn’t find anything that fits me. Did someone implement this before?

       

      Best regards

      Dimitri
      <div id=”gtx-trans” style=”position: absolute; left: -11px; top: 215.2px;”>
      <div class=”gtx-trans-icon”></div>
      </div>

      Jennifer Häfner
          Has successfully completed the online course Intermediate (200)
          Has successfully completed the online course Advanced (300)
          Has successfully completed the online course Basics (100)
          Has successfully completed the online course Advanced (310)
          Has successfully completed the online course Advanced (320)
          Has successfully completed the Intermediate Certification
          Has successfully completed the Advanced Certification
        2 years ago #29995
        Up
        2
        Down
        ::

        Hello Dimitri,

        your use case seems to be structured into 3 parts:
        1) uploading a csv file
        2) parsing the csv file
        3) getting the data into the SQLite database

        So for the respective steps:

        1) You need to implement the upload process. On our marketplace, we have an example app where the upload process is implemented. Here is the article that describes it, you can also find the download link for the application there: https://community.simplifier.io/knowledge/drawing-on-images-with-the-signaturepad-widget/
        The example app deals with the Signature Pad widget, but you can use the upload process from there. In the Business Object function ‘upload file'(described in Step 3), change the code line temporaryFileReader.readAsDataURL(oFile);
        to temporaryFileReader.readAsText(oFile);

        2) Parsing the csv file: after uploading the file, you can parse it from CSV to JSON using the javascript library ‘PapaParse’ (https://www.papaparse.com/). Upload this library to your Simplifier instance (here is the documentation on how to do this: https://community.simplifier.io/doc/current-release/extend/including-libraries/add-new-library/). Also add this library to your Business Object to be able to use its functions there.
        Create a business object function that takes the result from the fileReader and parses it to JSON (with PapaParse).

        3) Now you have the data from the csv in JSON format. This data can be used in a connector to be written into the SQLite database (it depends on your use case which data you want to write).

        I hope these tips are useful.

        Jennifer Häfner
            Has successfully completed the online course Intermediate (200)
            Has successfully completed the online course Advanced (300)
            Has successfully completed the online course Basics (100)
            Has successfully completed the online course Advanced (310)
            Has successfully completed the online course Advanced (320)
            Has successfully completed the Intermediate Certification
            Has successfully completed the Advanced Certification
          2 years ago #29996
          Up
          1
          Down
          ::

          Additional note: in our Standard Content, there’s the Client Side Business Object ‘ITIZ_CSV’ which can be used to parse CSV files to JSON (and vice versa). It also uses the libary PapaParse.

          Christian
              2 years ago #30000
              Up
              0
              Down
              ::

              Hi Jennifer, thank you very much for this helpful answer! I managed to create a new app that uploads an image and displays it. But 🙂 Now I would like to do two changes which do not work:

              • I want to change it to CSV handling, so I changed the mime type to text/csv, changed the FileReader call from readAsDataURL to readAsText, and changed the output mapping so that the resultURL is not used as Image.src, but as Text.text. Unfortunately, the Text widget gets not updated. I use exactly the same process and logic as for updating the image src, but it does not work. Do you have an idea here?
              • I would like to pass the readAsText result to the next Business Object. How do I do this the best way? Do I concatenate two Business Objects in the Process Designer? If yes, how do I pass the parameter?

              Thank you!

              Jennifer Häfner
                  Has successfully completed the online course Intermediate (200)
                  Has successfully completed the online course Advanced (300)
                  Has successfully completed the online course Basics (100)
                  Has successfully completed the online course Advanced (310)
                  Has successfully completed the online course Advanced (320)
                  Has successfully completed the Intermediate Certification
                  Has successfully completed the Advanced Certification
                2 years ago #30002
                Up
                0
                Down
                ::

                Hi Christian,

                Have you already checked what the output of the file reader contains?
                In Chrome, you can debug it like this:

                • Open your application. If necessary, add ‘sap-ui-debug=<Appname>’ to the URL. For example, when your application is called ‘Example_App’, the URL needs to look like this:
                  https://<your Simplifier instance>/appDirect/Example_App/index.html?sap-ui-debug=Example_App
                • Press F12 to open Chrome Dev Tools. Select ‘Sources’, and in the tree structure on the left, select ‘modules’ – ‘ClientBusinessObject’ and then your Client Side Business Object where your CSV upload code is (see attached image)
                • Add a breakpoint to your upload function
                • In your application, upload a csv file while the Chrome Dev Tools are open
                • The process will pause at your breakpoint. Now you can debug all the variables inside the code (e.g., by hovering over them with the mouse)
                • Check if the return value of the Business Object contains the values from the csv. If not, maybe your user does not have the permissions/roles for the uploader? If this is the case, check Step 5 of this article: https://community.simplifier.io/knowledge/drawing-on-images-with-the-signaturepad-widget/

                To use this Business Object output as an input to another Business Object, you can use the ‘Process Output’ shape in the Process Designer (https://community.simplifier.io/doc/current-release/applications/add-application-logic/mapping-area/, ‘Process Output’).

                I hope this information is helpful.

                Attachments:
                You must be logged in to view attached files.
                Christian
                    2 years ago #30005
                    Up
                    0
                    Down
                    ::

                    Hi Jennifer

                    thank you for this great answer! I think I already found an issue: when I change everything to “CSV” instead of “Image” (see changes above), the Business Object is not loaded when I upload a csv file… when I uploaded an image, I saw that the folder “ClientBusinessObject” ist created and I see the JS-File. When I upload a csv file, this does not happen at all. In the console I do not get an error neither.

                     

                  Viewing 6 posts - 1 through 6 (of 6 total)

                  You must be logged in to reply to this topic.