Home » SQL & PL/SQL » SQL & PL/SQL » Split Data in Column & Split Data in Excel (merged) (Excel 2016)
Split Data in Column & Split Data in Excel (merged) [message #671060] Mon, 13 August 2018 12:53 Go to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi All,

I have an excel file with data which should be split.

Example:

Site ID Consortia/Multi-Site
-------- -------------------------
nci TAFE NSW Library
umac Inter Regional Consortium (IRC) P3-G2 (Primary), Hong Kong & Macau Consortium - 2014, Inter Regional Consortium (IRC)
P2-G2

Site_ID =Umac has corresponding Multisite with Comma Seprated Values.

I want to get the comma seperated values as different records but for same Site Id value. How can we do it?

I want to seperate them and upload to the table.

Uploaded the file for reference.Appreciate your help

  • Attachment: Book2.csv
    (Size: 312.03KB, Downloaded 1557 times)
Re: Split Data in Excel [message #671061 is a reply to message #671060] Mon, 13 August 2018 12:56 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Excel is a Microsoft product. Nothing to do with Oracle. I shall lock the topic.
Re: Split Data in Excel [message #671062 is a reply to message #671060] Mon, 13 August 2018 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have an excel file with data which should be split.

You have NOT an Excel file, you have a CSV file.

[Updated on: Mon, 13 August 2018 13:12]

Report message to a moderator

Split Data in Column [message #671063 is a reply to message #671060] Mon, 13 August 2018 13:26 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi All,

Created a table and loaded data.Now I want to split the data in Column "Consortia" for the same Site_id

CREATE TABLE TEST_DATA
(SITE_ID VARCHAR2(2000)
,CONSORTIA VARCHAR2(2000));

I tried to write below but the data in the "Column-CONSORTIA" is not Split

Please help.

WITH
   CONSORTIA as (
        SELECT  
        to_char(regexp_substr(CONSORTIA, '[^,]+', 1, column_value)) CONSORTIA
         from (select * from TEST_DATA),
              table(cast(multiset(select level from dual
                                  connect by level <= regexp_count(CONSORTIA,',')+1)
                   as sys.odciNumberList))
         where rownum > 0
     )
     SELECT * FROM TEST_DATA
     /

Uploaded the excel file that is loaded in to the table.
Re: Split Data in Column [message #671064 is a reply to message #671063] Mon, 13 August 2018 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vharish006 wrote on Mon, 13 August 2018 11:26
Hi All,

Created a table and loaded data.Now I want to split the data in Column "Consortia" for the same Site_id

CREATE TABLE TEST_DATA
(SITE_ID VARCHAR2(2000)
,CONSORTIA VARCHAR2(2000));

I tried to write below but the data in the "Column-CONSORTIA" is not Split

Please help.

WITH
   CONSORTIA as (
        SELECT  
        to_char(regexp_substr(CONSORTIA, '[^,]+', 1, column_value)) CONSORTIA
         from (select * from TEST_DATA),
              table(cast(multiset(select level from dual
                                  connect by level <= regexp_count(CONSORTIA,',')+1)
                   as sys.odciNumberList))
         where rownum > 0
     )
     SELECT * FROM TEST_DATA
     /

Uploaded the excel file that is loaded in to the table.
Please provide needed INSERT statements.
Please provide expected & desired results.

Re: Split Data in Excel [message #671065 is a reply to message #671062] Mon, 13 August 2018 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do it using Oracle (but is this necessary?) spooling something like:
SQL> CREATE TABLE t_ext (
  2    site_id     VARCHAR2(30),
  3    consortia   VARCHAR2(4000)
  4    )
  5  ORGANIZATION EXTERNAL (
  6    TYPE ORACLE_LOADER
  7    DEFAULT DIRECTORY my_dir
  8    ACCESS PARAMETERS (
  9      RECORDS DELIMITED BY NEWLINE
 10      SKIP 1
 11      NOBADFILE
 12      NOLOGFILE
 13      NODISCARDFILE
 14      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 15      MISSING FIELD VALUES ARE NULL
 16      (site_id, consortia)
 17    )
 18    LOCATION ('Book2.csv')
 19  )
 20  /

Table created.

SQL> col consortia format a80 word_wrap
SQL> SELECT * FROM t_ext WHERE rownum <= 3
  2  /
SITE_ID                        CONSORTIA
------------------------------ --------------------------------------------------------------------------------
nci                            TAFE NSW Library
umac                           Inter Regional Consortium (IRC) P3-G2 (Primary), Hong Kong &amp; Macau
                               Consortium - 2014, Inter Regional Consortium (IRC) P2-G2
lnhk                           The Joint University Librarians Advisory Committee (JULAC) (Primary), The Joint
                               University Librarians Advisory Committee (JULAC) II, Hong Kong &amp; Macau
                               Consortium - 2014, Inter Regional Consortium (IRC) P3-G2

3 rows selected.

SQL> SELECT site_id || ',"' || ltrim(regexp_substr(consortia, '[^,]+', 1, column_value)) || '"' res
  2  FROM (SELECT * FROM t_ext WHERE ROWNUM <= 3),
  3       table(cast(multiset(select level word from dual
  4                           connect by level <= regexp_count(consortia,',')+1
  5                 ) as sys.odcinumberlist))
  6  /
RES
---------------------------------------------------------------------------------------------------------------
nci,"TAFE NSW Library"
umac,"Inter Regional Consortium (IRC) P3-G2 (Primary)"
umac,"Hong Kong &amp; Macau Consortium - 2014"
umac,"Inter Regional Consortium (IRC) P2-G2"
lnhk,"The Joint University Librarians Advisory Committee (JULAC) (Primary)"
lnhk,"The Joint University Librarians Advisory Committee (JULAC) II"
lnhk,"Hong Kong &amp; Macau Consortium - 2014"
lnhk,"Inter Regional Consortium (IRC) P3-G2"

8 rows selected.

[Updated on: Mon, 13 August 2018 13:54]

Report message to a moderator

Re: Split Data in Column [message #671066 is a reply to message #671064] Mon, 13 August 2018 13:45 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi ,

Below are sample insert and output results.

Insert Statements:
---------------------------
INSERT INTO TEST_DATA
(SITE_ID, CONSORTIA)
VALUES
('nci', 'TAFE NSW Library');

INSERT INTO TEST_DATA
(SITE_ID, CONSORTIA)
VALUES
('umac', 'Inter Regional Consortium (IRC) P3-G2 (Primary), Hong Kong &amp; Macau Consortium - 2014, Inter Regional Consortium (IRC) P2-G2)');


Sample OUTPUT :
-------------

SITE_ID CONSORTIA
------- ---------
nci TAFE NSW Library
umac Inter Regional Consortium (IRC) P3-G2 (Primary)
umac Hong Kong &amp; Macau Consortium - 2014
umac Inter Regional Consortium (IRC) P2-G2)
Re: Split Data in Excel [message #671067 is a reply to message #671065] Mon, 13 August 2018 13:53 Go to previous message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Thanks Michel cadot.
Previous Topic: How to Convert Row to Column As Below
Next Topic: XML Special Character
Goto Forum:
  


Current Time: Fri Mar 29 09:34:21 CDT 2024