create multiple output files based on query

Question

create multiple output files based on query

SSCrazy

Points: 2082

December 30, 2024 at 11:53 pm

I have this script that puts all of the output from query to a single file. Could it be changed to create multiple files based on query results?

The query will find like a months worth of information like this in the table based on the Like condition.

20240501

20240502

20240503

Instead of creating 1 large file could it create a file for each hit it finds?

Thanks.

#Connection Strings
$Database = "CB_Compare"
$Server = "(local)"
#Export File
$AttachmentPath = "c:\fileloading\rmcstdetails.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "
Select * from
RMcstDetails where ImageName like '202405%'"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
($DataSet.Tables[0] | ConvertTo-Csv -Delimiter "|" -NoTypeInformation) -replace """, "" |
Out-File -Force -Encoding utf8 $AttachmentPath

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeffrey Williams SSC Guru Points: 90309 More actions · Answer 1

Sure - depends on what you consider a 'hit'.

With that said - you have a lot of additional code here that isn't really needed. You can get rid of all of the setup for the connection and replace that with:

$results = Invoke-SqlCmd -Server $server -Database $database -Query "your query";

Then - you just need to filter the results to each file or create a loop, but that really depends on what a 'hit' means.

For example

$results | Where-Object {$_.SomeColumnName -eq "SomeValue"} | Export-Csv ...;

If a hit is based on some specific column - you could do something like:

$results | % {
    $hit_column = $_.HitColumn;
    Export-Csv -Path "c:\fileloading\$hit_column_details.csv" -NoTypeInformation -Append;
    }

You can even include sorting to ensure the specific 'hit' column rows are sorted so the files are created/appended appropriately.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2082 More actions · Answer 2

thanks, what I mean by hit would be each time a NEW date is found then create a new output file. If I ran this query against table this is what I trying to achieve.

This imagename field stores a date in the first 8 positions. I want to create outputs files by day because they are large and I have another process to use them in and I need somewhat smaller files.

using example, it would find 20240501 and for all records matching that mask I want an output file called

20240501.csv.

It should find by day and create that same output file..

I hope this help explain.

Select 
count(*) as 'ByDay',
SUBSTRING(ImageName,1,8) as 'Day'
from
tblb
group by SUBSTRING(ImageName,1,8)
order by 2

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

I can't help because I don't use PowerShell for much but I am curious. The output file in your original post is "c:\fileloading\rmcstdetails.csv". Where is the "c:\" drive physically located? On the SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2082 More actions · Answer 4

On my physical laptop ... just testing the process before using Network share.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 5

Bruin wrote:

On my physical laptop ... just testing the process before using Network share.

Is the "SQL Server" you're testing on also on your laptop?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2082 More actions · Answer 6

Bruin

SSCrazy

Points: 2082

January 2, 2025 at 12:59 am

#4508605

Yes,

$Server = "(local)"

Bruin SSCrazy Points: 2082 More actions · Answer 7

Was there an issue with creating solution locally before deploy?