0

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table names as parameters. The procedures are for a data validation process to see what is not aligned to our specifications. These all check for different things: the "validity" of specific values, formatting etc. That said they all have a different output, including the number of columns. I am attempting to automate the load, the stored procedure runs, and the placement of the result sets into a spreadsheet.

I have written a Powershell script to load the files into the DB using dbatools, and then run the "root" stored procedure that runs the other 12 or so. I have found myself a bit stuck attempting to export this data without doing 12+ separate stored procedure calls into separate CSVs or inserting the results into a "final" table that includes header names in the rows... I will do that if needed, but this output is typically sent to clients for review; forcing them to open up to 12 CSVs for a review is cumbersome and having one table is not very clean given how much the columns differ.

TSQL is my jam and I am not super experienced with PS. If I am being dumb, please point me to a relevant resource that I can make myself not dumb. Any suggestions are appreciated. Thanks

1
  • Here is a powershell module (found using google) you can use to manipulate excel files through powershell. Add worksehet, paste results from stored procedure, repeat for each result set. github.com/dfinke/ImportExcel/tree/master Commented Jun 21 at 13:39

1 Answer 1

0

There are two approaches you could take:

  1. Use the UNION operator in SQL to combine the output of the 12 procedures into a single view.
  2. Write twelve different CSV files and use PS to merge them.

Without the particulars of your case, it's hard to know which approach is more promising. - user1050

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.