Find the Empty Columns in the CSV file

# requires ImportExcel
# can be downloaded here: https://github.com/dfinke/ImportExcel

Import-Module ImportExcel 
$ImportFile = ".\test.csv"

$Header = Get-Content $ImportFile -First 1

$Headerarray = $header.Split(",")

# Build header

for ($i=0;$i -lt $Headerarray.Length; $i++) { 
    If($Headerarray[$i] -eq "") {
        $Headerarray[$i] = "TempHeader$i"
    }
}


$CSVImport = @(Get-Content $ImportFile | Select-Object -Skip 1 | ConvertFrom-Csv -Header $Headerarray)
$Headerarray | % {
    if(($CSVImport.$_ | Where-Object {$_ -ne $null}).Count -eq 0) {
        "$_ is empty"
    }
}
$CSVImport |  Export-Excel "Filename_$(Get-date -Format yyyyMMddHHmm).xlsx" -Show

Select allOpen in new window

This solves 1st and 2nd requirement.
For the 3rd requirement I haven't found a nice solution yet.

Ok, simple. :-)

# requires ImportExcel
# can be downloaded here: https://github.com/dfinke/ImportExcel

Import-Module ImportExcel 

$ImportFile = ".\test.csv"

$Header = Get-Content $ImportFile -First 1

$Headerarray = $header.Split(",")

# Build header

for ($i=0;$i -lt $Headerarray.Length; $i++) { 
    If($Headerarray[$i] -eq "") {
        $Headerarray[$i] = "TempHeader$i"
    }
}

$PropertyNames = @()
$CSVImport = Get-Content $ImportFile | Select-Object -Skip 1 | ConvertFrom-Csv -Header $Headerarray
$Headerarray | % {
    if(($CSVImport.$_ | Where-Object {$_ -ne $null}).Count -eq 0) {
        "$_ is empty"
    } else {
       $PropertyNames += $_ 
    }
}

$CSVImport | Select -Property $PropertyNames | Export-Excel "Filename_$(Get-date -Format yyyyMMddHHmm).xlsx" -Show

Select allOpen in new window

Not having an example of such a .csv file with these issues, I can think a few things to check before deciding that you really have these issues with the file.  I have seen many forum postings like this by folks who did not have a true .csv file or didn't use the correct parameters to read it.

Don't assume that the parameters used to create the file are what you would consider the "default" ones for a so-called ".csv" file.  Improperly interpreted .csv files can look like they have missing headers or missing columns, etc.  I've seen a few ".csv" files that were not properly interpreted because the correct parameters were not used to read the file.  Such things as delimiter characters not properly designated, correct column separation characters not used (fixed width instead of tab, or comma instead of pipe, etc.) can skew the lineup.  
This is especially true if the file was exported by software, as it should have all headings and formatted appropriately as programmed.  It would look proper if it is read using the same formatting parameters as the ones used to export it.

However, just because the file extension is ".csv" doesn't mean its really formatted as a properly generated .csv file (someone just slapped a ".csv" extension on a text file).  If it's not a true ".csv" file and just thrown together some how and not created/exported by a proper .csv generation software, then... who knows what you have... or how to fix it....

Question for you... why the SQL and Query Syntax topic areas ? Why not Excel or .Net ?

Initially I had a requirement Call all the parameters like Source, Destination , Filename and File extension from Sql table. That's the reason I had SQL areas.

Because Everyone was Viewing and not answering and i got an email after 3-4 hours to modify the question and update again so that's the reason

Import-Module : The specified module 'ImportExcel' was not loaded because no valid module file was found in any module directory.

You can either install it using "Install-Module ImportExcel" command or download it (from github).

This link might help you https://devblogs.microsoft.com/scripting/introducing-the-powershell-excel-module-2/

And : https://www.powershellgallery.com/packages/ImportExcel/5.4.2 has the full list of different versions and how to install. Make sure you do get the Current Version :)

Hope that helps you to get started.... The posts above by Michael Pfister are pretty good too :)

Can someone get me the output without converting it to Excel Format only to CSV

$CSVImport | Select -Property $PropertyNames | Export-CSV "Filename_$(Get-date -Format yyyyMMddHHmm).csv" -NoClobber

Select allOpen in new window

will output the generated table to a CSV file

If you want to add the SQL connection have a look here:

https://gallery.technet.microsoft.com/scriptcenter/Connecting-SQL-Database-e34078ae

Little bit confused now....

Are you already exporting your Table from SQL into CSV ?  And want to manipulate that CSV file ?

Or, that is the real challenge. Meaning you need a means of exporting from SQL to create CSV ?

If you already have the CSV, as Mark Edwards says above, it is just a text file and largely unstructured, so, it often requires a slightly more formal structure (or at least a means of understanding a more formal structure) to be able to inspect the individual elements such as rows and columns.

Select-Object : Cannot convert System.Management.Automation.PSObject to one of the following types {System.String, System.Management.Automation.ScriptBlock}.

I've found just one problem, I forgot to disable the type information Export-CSV sets in the CSV.
The rest works during my tests, no errors.
Which line is generating the error?

.

$CSVImport | Select -Property $PropertyNames | Export-Csv -NoTypeInformation -NoClobber "Filename_$(Get-date -Format yyyyMMddHHmm).csv"

Select allOpen in new window

Can you attach a sample of the input CSV to reproduce the error?

Everything is working good With this code only one issue its not checking whether the column is empty or having data Please find the attached file.
Error1.PNG

Perfect