Post reply

Need to output table data into multiple CSV files

  • September 29, 2023 at 3:24 pm

    #4297758

    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

    Begin

    SELECT col1, col2

    from table1

    OFFSET 999*@num ROWS

    FETCH NEXT 999 ROWS ONLY;

    $filename = "C:\temp\filename"

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

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

    SET @Num = @Num + 1;

    END

  • September 29, 2023 at 6:49 pm

    #4297856

    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 ++
            }
    }

    Johan

    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