4 Replies Latest reply: Jun 6, 2014 12:00 PM by Doolius RSS

    Parse/Upload CSV data to table by column name

    Doolius

      I've found a lot of information on parsing through an uploaded CSV file and transfering the data to a table, but it doesn't solve a problem I have.

       

      Problem: Spreadsheets that are uploaded do not have all of the same columns. Is there a way to parse through a spreadsheet by the column name and when data is uploaded to the final table, it is uploaded by the column names that match?

       

      Example:

      Spreadsheet 1 has columns:

      one||two||three||five||seven||nine

       

      Spreadsheet 2 has columns:

      one||two||four||six||seven||eight

       

      Final table has columns:

      one||two||three||four||five||six||seven||eight||nine

       

      Is there a way to do this? Both spreadsheets will have the column names in the first row.

       

      Thanks

      Steven

        • 1. Re: Parse/Upload CSV data to table by column name
          Mike Kutz

          You tagged APEX but didn't mention you were using APEX.

          If you are, that makes the "solution" easier.

          Assuming you are loading things into an APEX Collection (eg Excel2Collection), create a view for each of the destination tables.

          Then, in your process:  INSERT ... SELECT ... from xxx_view

           

          The Base SQL statement: (it could use some tweaks, but you should get the idea)

          create or replace
          view sheet1_view
          as
            with column_info as ( 
          -- contains only the row with column data 
            select collection_name 
                    ,c001,c002,c003,c004,c005,c006,c007,c008,c009,c010 
                    ,c011,c012,c013,c014,c015,c016,c017,c018,c019,c020 
                    ,c021,c022,c023,c024,c025,c026,c027,c028,c029,c030 
                    ,c031,c032,c033,c034,c035,c036,c037,c038,c039,c040 
                    ,c041,c042,c043,c044,c045,c046,c047,c048,c049,c050 
            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 
                    ,c011,c012,c013,c014,c015,c016,c017,c018,c019,c020 
                    ,c021,c022,c023,c024,c025,c026,c027,c028,c029,c030 
                    ,c031,c032,c033,c034,c035,c036,c037,c038,c039,c040 
                    ,c041,c042,c043,c044,c045,c046,c047,c048,c049,c050 
            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 
                    ,c011,c012,c013,c014,c015,c016,c017,c018,c019,c020 
                    ,c021,c022,c023,c024,c025,c026,c027,c028,c029,c030 
                    ,c031,c032,c033,c034,c035,c036,c037,c038,c039,c040 
                    ,c041,c042,c043,c044,c045,c046,c047,c048,c049,c050 
                  ) 
              ) 
          ), 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 
                    ,c011,c012,c013,c014,c015,c016,c017,c018,c019,c020 
                    ,c021,c022,c023,c024,c025,c026,c027,c028,c029,c030 
                    ,c031,c032,c033,c034,c035,c036,c037,c038,c039,c040 
                    ,c041,c042,c043,c044,c045,c046,c047,c048,c049,c050 
                  ) 
              ) 
          ), actual_data as (
          -- join the two together 
          select DU.collection_name, DU.seq_id 
              ,CU.column_name -- ADJUST COLUMN NAMES HERE
              ,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)
          )
          select *
          from actual_data
          pivot (
            min(data_value)
            for column_name
             in ('one' as "ONE"
                ,'two' as "TWO"
                ,'three' as "THREE"
                ,'four' as "FOUR"
              )
          )
          where collection_name = 'SHEET 1'
          ;
          
          • 2. Re: Parse/Upload CSV data to table by column name
            Paulie

            I'm assuming that the data in columns one and two are the same, i.e. correspond to the same

            records?

             

            Have two external tables using your two spreadsheets S1 and S2.

             

            S1) one||two||three||five||seven||nine

            S2) one||two||four||six||seven||eight

             

            Then when you wish to combine them use a normal table T1 - from S1

             

            INSERT INTO T1 (one, two, three, five, seven, nine) VALUES (SELECT one, two, three, five, seven, nine FROM S1);

             

            And then - from S2

             

            UPDATE T1 (four, six, eight) SET VALUES = (SELECT four, six, eight FROM S2 WHERE S1.1 = S2.1)

             

            NOTE - it's the logic I'm getting at here - I don't have an Oracle server handy to play with so

            the syntax may be (i.e. is probably) wrong (+ I'm tired) but you get the idea. You could possibly look at the

            MERGE syntax also?

             

            Paul...

            • 3. Re: Parse/Upload CSV data to table by column name
              Doolius

              My aplolgies. Yes I am using APEX.

               

              I've played around with the Excel2Collections but the only problem with that is the spreadsheets have more than 50 columns.

              Which is why I think I will need to parse/upload the CSV data into a tempory table I will create.

              • 4. Re: Parse/Upload CSV data to table by column name
                Doolius

                Yes, columns one and two are the primary keys of the records.

                 

                I've thought about creating separete tables for each spreadsheet, but the problem with that is between the users there are about 8 or 9 different spreadsheets.

                So I didn't want to have to create 8 or 9 different tables.

                 

                I'm working on getting it down to only 2 different spreadsheets, but wanted to see if it would be possible to match the column in the spreadsheet to the column in the table when it is being uploaded instead of:

                spreadsheet column1 = table column1

                spreadsheet column2 = table column2

                etc.

                 

                Steven