External table to load text with multiple delimiters
Hi folks
I have a text file to load via external table.
Sample file looks like this:
100,John,12345,'02/feb/2004',Boston;Producta:3200:186723:3456:0:35;Productb:1800:33535:4567:1:46
200,Mary,12344,NJ;Producta:1900:34355:1267:1:67;productc:3600:32434:2726:2:48
300,Jim,12333,'01/jan/2002',London;Productd:3200:46464:2626:4:89;Producte:3636:57575:1425:3:78;Productf:2567:56584:4:32
Result in table:
| SNO | Name | SalesmanID | SalesDate | City | Sales_Details |
|---|---|---|---|---|---|
| 100 | John | 12345 | 02-FEB-2004 | Boston | Sales_Collection_Type |
| 200 | Mary | 12344 | NJ | ||
| 300 | Jim | 12333 | 01-JAN-2002 | London |
Sales_Collection_Type (Ex for first Sales man only)
| Column | Value |
|---|---|
| Product_Name | Producta |
| Product_ID | 3200 |
| Batch_Serial | 186723 |
| Batch_Qty | 3456 |
| In_Stock | 0 |
| In_PO | 35 |
| Salesman_ID | 12345 |
| Column | Value |
|---|---|
| Product_Name | Productb |
| Product_ID | 1800 |
| Batch_Serial | 33535 |
| Batch_Qty | 4567 |
| In_Stock | 1 |
| In_PO | 46 |
| Salesman_ID |
1