Excel Files and SQL

It never seems to fail that a project will come about that will require someone to enter some data into a spreadsheet for some form of import in to your database. It also never seems to fail that no matter how many times you ask you end up with a xslx or some other form of office spreadsheet that isn't friendly with bulk insert. Sometimes you do get a csv but they created it oddly and the delimiters are wrong or not in the right places. This little script opens an excel sheet and converts it to a csv. Then reads each file in and line by line turns that into a select union script. We used this in aback end process to add data to users to our system.

function ConvertFrom-XLx {
  param ([parameter(             
        Mandatory=$true,
        ValueFromPipeline=$true,
        ValueFromPipelineByPropertyName=$true)]
        [string]$path ,
        [switch]$PassThru
        )

    begin { $objExcel = New-Object -ComObject Excel.Application }
        Process { 
                    if ((test-path $path) -and ( $path -match ".xl\w*$")) {
                        $path = (resolve-path -Path $path).path
                        $savePath = $path -replace ".xl\w*$",".csv"
                        $objworkbook=$objExcel.Workbooks.Open( $path)
                        $objworkbook.SaveAs($savePath,6) # 6 is the code for .CSV
                        $objworkbook.Close($false)
                        if ($PassThru) {Import-Csv -Path $savePath }
                        }
                    else {Write-Host "$path : not found"}
                }
    end  { $objExcel.Quit() }
}

dir *.xlsx | ConvertFrom-XLx
$command = 'ls *.csv'
$array =[string] @()
$array = ls -filter "*.csv"
$out = [string] @()
foreach ( $i in $array) {
   $Sql = [string] @()
   "processing file:" + $i
   $Count = 0
   $sql += "Select * from ( select null as Column1,null as Column2,null as Column3,null as Column4,null as Column5 "
   foreach ($a in Get-Content $i) {
            $sql +=" Union All"+ "`n"
            $count += 1
            $Values = "$a".split(',')
            $sql += "select" + "'"+$values[0] + "'"+ ","+ "'"+$values[1] + "'"+ ","+ "'"+$values[2] + "'"+ ","+ "'"+$values[3] + "'"+ ","+ "'"+$values[4] + "'" #+" Union All" + "`n`r"
    }
    $sql += ") q2 where Firstname is not null"
    $out = $sql
    "Sql for File:`n`r" + $out 
    $outfile = "$i".replace(".csv",".sql")
    $out | Out-File $outfile
}
}