Need to output table data into multiple CSV files

  • Hello, I created the below script to output 999 rows from SQL Server table into multiple CSV files. The SQL table has about 45,000 rows. We can only output max of 999 rows into the CSV files. I installed powershell sql server module. Since this is a powershell script, I may need to modify the below script to run as powershell script. Any ideas on the changes needed? Thanks.

    DECLARE @Num int = 0;

    While @Num <= 52


    SELECT col1, col2

    from table1

    OFFSET 999*@num ROWS


    $filename = "C:\temp\filename"

    Invoke-Sqlcmd -Query "SELECT col1, col2 from table1" -ServerInstance "SQLServer1" |

    Export-Csv -Path "$filename.csv" -NoTypeInformation

    SET @Num = @Num + 1;


  • Maybe not the most performant solution but as a next step import the full file and process it

    $FullfileData = import-csv -Path $fullfile ;

    $RecCounter = 0;
    $TargetFileNo = 1;
    $TargetFiletemplate = $('{0}\file_<no>.csv' -f $env:temp )
    foreach ( $rec in $FullfileData ) {
    $RecCounter ++
    $targetfile = $TargetFiletemplate.replace('<no>', $TargetFileNo)

    $rec | Export-Csv -Path $targetfile -NoClobber -NoTypeInformation -Append ;

    if ( $RecCounter % 10 -eq 0 ) {
    $TargetFileNo ++


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply