Copy&Paste from Excel to sqlplus. Multi-row multi-column data

By default, at least with Excel 2007, if you copy multi-row and multi-column data from Excel,
the data is delimited by 'new-line' character and 'TAB' character.

 

So, if you use 'newline' and 'TAB' in regexp_substr() function as I used in my previous posts,
you can see the copy&pasted data as tabular data.

You need to know the number of columns beforehand. I don't think there's a smart way around this.

SQL> l
  1  with excelrows as (
  2  select regexp_substr('
  3  Steven     King    515.123.4567
  4  Neena      Kochhar 515.123.4568
  5  Lex        De Haan 515.123.4569
  6  Alexander  Hunold  590.423.4567
  7  Bruce      Ernst   590.423.4568
  8  David      Austin  590.423.4569
  9  Valli      Pataballa       590.423.4560
 10  Diana      Lorentz 590.423.5567
 11  Nancy      Greenberg       515.124.4569
 12  Daniel     Faviet  515.124.4169
 13  John       Chen    515.124.4269
 14  ','[^
 15  ]+',1,level) erow from dual connect by level < 16)
 16  select
 17  regexp_substr(erow, '[^    ]+',1,1) col1,       --  TAB char, not space
 18  regexp_substr(erow, '[^    ]+',1,2) col2,
 19  regexp_substr(erow, '[^    ]+',1,3) col3
 20* from excelrows where erow is not null
SQL> /

COL1             COL2             COL3
---------------- ---------------- ----------------
Steven           King             515.123.4567
Neena            Kochhar          515.123.4568
Lex              De Haan          515.123.4569
Alexander        Hunold           590.423.4567
Bruce            Ernst            590.423.4568
David            Austin           590.423.4569
Valli            Pataballa        590.423.4560
Diana            Lorentz          590.423.5567
Nancy            Greenberg        515.124.4569
Daniel           Faviet           515.124.4169
John             Chen             515.124.4269

11 rows selected.
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today