DbaTools With Powershell - Omit The Id Column on CSV Export?

Question

Post reply

DbaTools With Powershell - Omit The Id Column on CSV Export?

cajun_sql

SSCommitted

Points: 1865
More actions
March 26, 2025 at 3:10 pm

#4567807
I am learning DbaTools in Powershell, and my current project is exporting a CSV file.
I've managed to get the code to work; however, there are situations in which I will need to omit the Identity column when exporting. Is there a way to do this programmatically in DbaTools, so that the CSV file won't contain that Identity column?
Thanks
- This topic was modified 3 days, 18 hours ago by cajun_sql.
- This topic was modified 3 days, 18 hours ago by cajun_sql.

Viewing 5 posts - 1 through 4 (of 4 total)

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

cajun_sql SSCommitted Points: 1865 More actions · Answer 1

Addendum: Here's the code I've been using for export:

Credit to this link

#Initiate a connection
$MyConnection = Connect-DbaInstance -SqlInstance LOCALHOST\MSSQLSERVER -TrustServerCertificate
#Define a list of tables
$Tables = @("cop.table1", "cop.table2", "cop.table3")
#Iterate through the list
foreach ($Table in $Tables) {
#Build the query
$Query = "SELECT * FROM $Table;"
#Build the output file path and remove the schema, dot from the file name
#and append the .csv extension
$CsvPath = "C:\CSVOut\" + $Table.replace('cop.', '') + ".csv"
#Run the query and export results to CSV
Invoke-DbaQuery -SqlInstance $MyConnection -Database cop `
-Query "$Query" | Export-Csv -Path "$CsvPath" `
-Delimiter "|" -NoTypeInformation -Encoding unicode 
}

jasona.work SSC Guru Points: 50151 More actions · Answer 2

I'm thinking the best option, if it's an option, would be to specify the columns in the select query and just leave out the ID column. Export-CV wouldn't see that as "formatting" the output.

The possible problem would be if the three tables in your list have different column names (which, honestly, I suspect is why you haven't gone this route)

Edited to add:

OK, forgot when I started my reply, you're using the DBATools Powershell to execute the query, so went looking at that and...

It's just going to run whatever query you pass to it, it doesn't appear to have a way to say "run select * but then do not select column ID"

This reply was modified 3 days, 17 hours ago by jasona.work.

cajun_sql SSCommitted Points: 1865 More actions · Answer 3

Thanks very much! Yes, I may do Regex replacing on the generated CSVs.

By the way, in DbaTools, is there any filtering based upon datatype of columns (e.g. AutoNumber) or the column's position in the table (whether its the first column, second, etc)? I already ran the 'Get-Help Invoke-DbaQuery,' but could not locate such a mechanism.

jasona.work SSC Guru Points: 50151 More actions · Answer 4

From looking through the documentation on the site, there's no filtering at all for the invoke-dbaquery, it's just going to run the query you throw at it and spit out the results.

DbaTools With Powershell - Omit The Id Column on CSV Export?

Cookies on SQLServerCentral