Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access 6.0.061
  HXTT Cobol 3.0.061
  HXTT DBF 6.0.064
  HXTT Excel 5.0.064
  HXTT Paradox 6.0.061
  HXTT Text(CSV) 6.0.062
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT XML 2.0.061
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Beijing City (abbr, HXTT)

HXTT Text(CSV)
hxtt csv driver sql regex parse
shakti
2018-02-01 04:16:37.0
I have a csv file with the below structure.

USer,Role
"A","p,q"
"B","p"
"C","q,r,s"

I am trying to write a sql query to parse the data to form the below output
user,role
A,P
A,q
B,p
C,q
C,r
C,s

I am trying to use REGEX but still unable to parse multi value columns into
multiple rows. ANy suggestions
Re:hxtt csv driver sql regex parse
HXTT Support
2018-02-02 03:41:35.0
A new feature will be available in 48 hours.

Split Multivalue Column Into Rows

If each row has multiple multi value columns, a special subquery table can be used in special join sql. For instance,

select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;

select User,Role,Year from aTable,(select split(aTable.Roles,',') as Role,split(aTable.Years,',') as Year) AS bTable;
Re:Re:hxtt csv driver sql regex parse
shakti
2018-02-04 22:45:28.0
Hello,
I tried using the query above.
Its not working please provide in details that will be parse comma separated values into multiple rows based on example provided above.
Re:Re:Re:hxtt csv driver sql regex parse
HXTT Suport
2018-02-05 01:22:06.0
You should download the latest package.
select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;
can work.
Re:Re:Re:Re:hxtt csv driver sql regex parse
shakti
2018-02-05 02:13:09.0
Any other approach, rather than downloading the latest package.
can we use CONNECT BY to do such parsing.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 1999-2017 Heng Xing Tian Tai Lab of Beijing City. | All Rights Reserved. | Privacy | Legal | Sitemap