8 Replies Latest reply: May 5, 2014 4:04 PM by lsaunders RSS

    Transforming uploaded data

    lsaunders

      Is it possible to manipulate data uploaded via the data load wizard, into a different format for storage?

       

      An example report that will be uploaded from excel:

       

      Task01/12/201301/01/2014
      Task one3467
      Task two56643
      Task three33444
      Task four6127437

       

      But I want to upload this data and store it in Apex like this:

      DateTaskCount
      01/12/2013Task one34
      01/12/2013Task two566
      01/01/2014Task one67
      01/01/2014Task three444

       

      Can anyone show me how I could go about this?

       

      Cheers!

        • 1. Re: Transforming uploaded data
          vincent_deelen

          Hi,

           

          The Data load wizard load the csv data into a collection called 'LOAD_CONTENT'. In the upload wizard you can specify to use the first row of your file as column headers and you can reference a specific row using the collections' 'SEQ_ID' column. IN your case a query like following will result in the desired output:

           

          select

          ( select to_date(c002,'dd/mm/yyyy')

            from   apex_collections

            where  collection_name = 'LOAD_CONTENT'

            and    seq_id = 1

          ) as date

          , c001 as task

          , c002 as count

          from  apex_collections

          where collection_name = 'LOAD_CONTENT'

          and   seq_id > 1

          union all

          select

          ( select to_date(c003,'dd/mm/yyyy')

            from   apex_collections

            where  collection_name = 'LOAD_CONTENT'

            and    seq_id = 1

          ) as date

          , c001 as task

          , c003 as count

          from  apex_collections

          where collection_name = 'LOAD_CONTENT'

          and   seq_id > 1

           

          You can use this query to create a report, or to update a table or whatever it is that you want to do.

           

          Kind regards,

          Vincent Deelen

          vincentdeelen.blogspot.com

          • 2. Re: Transforming uploaded data
            Mike Kutz

            It would be easier creating your own "wizard" starting with using the Excel2Collection plugin.

            - Process Type Plugin - EXCEL2COLLECTIONS

             

            I've done this type of translation before but it will take me some time to lookup the exact code.

            From what I remember:

            • get the actual column names by UNPIVOT where seq_id=1
            • get data by UNPIVOT where seq_id > 1
            • join the two together using seq_id and original_column_name

             

            MK

            • 3. Re: Transforming uploaded data
              lsaunders

              Thanks Vincent. So using that approach, I would need to know how many date columns to import, as each would need it's own union right?

               

              Thanks

              • 4. Re: Transforming uploaded data
                lsaunders

                Hi Mike, if you have anything else you can dig up on how to implement that, it would be great

                 

                Cheers

                • 5. Re: Transforming uploaded data
                  lsaunders

                  I haven't been able to get that plugin to work and the zip file I download from apex_plugins is corrupt.


                  Does the plugin require config mods to the Listener, or should it just work as is.. ? Can anyone attach the instructions file for it?

                  • 6. Re: Transforming uploaded data
                    vincent_deelen

                    Hi,

                     

                    Indeed you'd need to know what you are importing. You can include extra columns in the union statement in case you're not always importing the same amount of date columns (add c004, C005, C006, etc..). So there is some flexibility, yet limited.

                     

                    Regards,

                    Vincent

                    vincentdeelen.blogspot.com

                    • 7. Re: Transforming uploaded data
                      Mike Kutz

                      plug-in (from your next post)

                      The plugin should work as-is.

                      If you can't import the plugin into your app, try redownloading it.

                      (Make sure you are running an appropriate version of APEX)

                      The plugin should be used for your own set of pages.. not the ones created by the Data Loader Wizard.

                       

                      SQL

                      The SQL below is a generic one.

                      You'll need to tweak it to fit your needs.

                      Once you have a specific SQL, I suggest you turn it into a VIEW.

                       

                      with column_info as (
                        -- contains only the row with column data
                        select collection_name
                                ,c001,c002,c003,c004,c005,c006,c007,c008,c009,c010
                        from apex_collections
                        where seq_id = 1
                      ),data_info as (
                        -- contains the rest of the information
                        select collection_name, seq_id
                                ,c001,c002,c003,c004,c005,c006,c007,c008,c009,c010
                        from apex_collections
                        where seq_id > 1
                      ), column_unpivot as (
                        -- unpivot COLUMN_INFO
                        select collection_name, column_name, apex_collection_column_name
                          from column_info
                          unpivot (
                            column_name
                              for apex_collection_column_name in (
                                c001,c002,c003,c004,c005,c006,c007,c008,c009,c010
                              )
                          )
                      ), data_unpivot as (
                        -- unpivot DATA_INFO
                        select collection_name, seq_id
                            ,apex_collection_column_name
                            ,data_value
                          from data_info
                          unpivot (
                            data_value
                              for apex_collection_column_name in (
                                c001,c002,c003,c004,c005,c006,c007,c008,c009,c010
                              )
                          )
                      )
                      -- join the two together
                      select DU.collection_name, DU.seq_id
                          ,CU.column_name, DU.data_value
                      from column_unpivot CU
                        join data_unpivot DU
                          on (CU.collection_name=DU.collection_name
                            and CU.apex_collection_column_name=DU.apex_collection_column_name);
                      
                      • 8. Re: Transforming uploaded data
                        lsaunders

                        Thanks Mike, I'll take a look at your SQL.

                         

                        For the plugin, I can import it ok, just can't get it to work. I haven't been able to open the ZIP from apex-plugins at all, but I got the updated .sql file from his demo page. It doesn't come with instructions though so I don't know if I missed a step somewhere.

                         

                        We're using Apex 4.2.3. Do you know if that should work ok?

                         

                        Also, for your code, I would add that to a process that fires after the plugin?

                         

                        Cheers