database - Extract Oracle data to csv file (UTF8 and pipe "|" separator), trim and re-join into new file - Sta

admin管理员组

文章数量:1202334

my goal is to extract millions of data from a specific Oracle table, clean useless spaces (trim each element of the table) and join once again each value delimited by "|" into a new csv file.

The table named "tablename1":

----------COL1----------+----------COL2----------+----------COL3----------
row2Col1                |                        |12345
row3 Col1               |12345                   |row3, col3
row4Col1                |row4 col2               |          
...
...

I've found the code below (from .bat windows):

SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET COLSEP '|'
SET TRIMSPOOL ON
set lines 1000
set pages 1000

SPOOL .\export\data_extraction.csv
SELECT LISTAGG(COLUMN_NAME, '|') WITHIN GROUP (ORDER BY COLUMN_ID) 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'tablename1';
SPOOL .\export\data_extraction.csv append;
select * from tablename1;
SPOOL OFF;

but opening the "data_extraction.csv" with Notepad++ the result seems to mantain the original columns formatting and, more, there are still spaces:

COL1|COL2|COL3
row1Col1                 |row1 col2               |row1, col3
row2Col1                 |                        |12345
row3 Col1                |12345                   |row3, col3
row4Col1                 |row4 col2               |

What i expect:

COL1|COL2|COL3
row1Col1|row1 col2|row1, col3
row2Col1||12345
row3 Col1|12345|row3, col3
row4Col1|row4 col2|

Is it possible to eventually run some additional and helpful powershell script to gain that?

Thanks in advance, Fab

my goal is to extract millions of data from a specific Oracle table, clean useless spaces (trim each element of the table) and join once again each value delimited by "|" into a new csv file.

The table named "tablename1":

----------COL1----------+----------COL2----------+----------COL3----------
row2Col1                |                        |12345
row3 Col1               |12345                   |row3, col3
row4Col1                |row4 col2               |          
...
...

I've found the code below (from .bat windows):

SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET COLSEP '|'
SET TRIMSPOOL ON
set lines 1000
set pages 1000

SPOOL .\export\data_extraction.csv
SELECT LISTAGG(COLUMN_NAME, '|') WITHIN GROUP (ORDER BY COLUMN_ID) 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'tablename1';
SPOOL .\export\data_extraction.csv append;
select * from tablename1;
SPOOL OFF;

but opening the "data_extraction.csv" with Notepad++ the result seems to mantain the original columns formatting and, more, there are still spaces:

COL1|COL2|COL3
row1Col1                 |row1 col2               |row1, col3
row2Col1                 |                        |12345
row3 Col1                |12345                   |row3, col3
row4Col1                 |row4 col2               |

What i expect:

COL1|COL2|COL3
row1Col1|row1 col2|row1, col3
row2Col1||12345
row3 Col1|12345|row3, col3
row4Col1|row4 col2|

Is it possible to eventually run some additional and helpful powershell script to gain that?

Thanks in advance, Fab

• database
• csv
• select
• trim

Share Improve this question Follow edited Jan 21 at 8:45 Olaf 5,23222 gold badges1818 silver badges2626 bronze badges asked Jan 21 at 8:02 FabfiscFabfisc 111 bronze badge 1

• What are the table column types like? – vonPryz Commented Jan 21 at 8:17

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Try this.

Obviously the various "col1", "col2" etc should be replaced by the actual column names.

$CsvPath = '.\export\data_extraction.csv'

$TrimmedCols = @{Name = 'Col1'; Expression = { $_.Col1.trim() } },
@{Name = 'Col2'; Expression = { $_.Col3.trim() } },
@{Name = 'Col3'; Expression = { $_.Col2.trim() } }

$CsvPath | Import-Csv -Delimiter '|' -Header col1, col2, col3 | 
    Select-Object -Skip 1 -Property $TrimmedCols

本文标签：