Slow exporting of sql data to csv by Powershell

Question

I need to export a large amount of data (from TERADATA by an sql query) to a csv file using PowerShell. The process is working but is very very slow. I am using this code for exporting :

($DataSet.Tables[0] | ConvertTo-Csv  -delimiter "|" -NoTypeInformation )   -replace '"', "" | Out-File $extractFile -Force

$extractFile is the path where I want to put my csv file.

Do you have any idea why is it that slow and how i can deal with that ?

large amount of data = you better use a TPT Export for this. — dnoeth
– dnoeth, Commented Apr 26, 2017 at 14:06
Do you have any documentation on how to export with TPT ? I am new to teradata and i didn't find a good documentation on TPT — Souhail Ouabi
– Souhail Ouabi, Commented Apr 27, 2017 at 14:02
Check the TPT User Guide info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/… — dnoeth
– dnoeth, Commented Apr 27, 2017 at 19:32
I used BTEQ, FastExport, but the highest speed I achieved was with SAS using BulkLoad over ODBC. We are talking about 1B+ records in a few hours. — access_granted
– access_granted, Commented Apr 27, 2017 at 19:50

dbso · Accepted Answer · 2017-04-26 14:23:50Z

1

Try using StreamWriter

$Stream = [System.IO.StreamWriter] $extractFile
$DataSet.Tables[0] | ConvertTo-Csv  -delimiter "|" -NoTypeInformation | % { $Stream.WriteLine($_.Replace('"', '')) }
$Stream.Close()

Edit: Adding the Replace directly in WriteLine made it quicker as well.

You could also utilize Measure-Command to identify what part is slowing down and what changes speeds it up.

edited Apr 26, 2017 at 14:23

answered Apr 26, 2017 at 14:06

dbso

7161 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Souhail Ouabi Over a year ago

I have this error : You cannot call a method on a null-valued expression. At E:\PowerShell\script\Spaceman.ps1:26 char:77 + $DataSet.Tables[0] | ConvertTo-Csv -delimiter "|" -NoTypeInformation | % { $Str ...

dbso Over a year ago

Is it the StringWriter or Replace that is causing this?

Souhail Ouabi Over a year ago

StringWriter i think, it's the one underlined

dbso Over a year ago

Can you try changing to % { if($_) { $Stream.WriteLine($_.Replace('"', '')) } } ?

Collectives™ on Stack Overflow

Slow exporting of sql data to csv by Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related