none
How do I create xlsx files in Powershell without using Excel.Application? RRS feed

  • Question

  • How do I convert files between CSV, XLS, XLSM, and XLSX to CSV, XLS, XLSX, and XML in Powershell without using Excel.Application? I would like to just use MICROSOFT.ACE.OLEDB.12.0.

    Monday, October 7, 2019 4:33 PM

All replies

  • This is my first attempt. It seems to be working with the data I throw at it. I am super interested in a better way of setting up the schema and writing into the file.

        <#
        .SYNOPSIS
        This function will take 2 filenames and use the excel application to convert from the first file to the second file.
    
        .DESCRIPTION
        This function allows you to use the full power of excel to open and save files. The infile can be any file that excel can open. The outfile can be xlsx, xls or csv. Also, there is an option to delete the destination file before runing the save operation to avoid prompts when overwriting, and to erase the origin file after the process has completed.
    
        .EXAMPLE
        Convert-OLEDB -infile 'Source.xls' -outfile 'destination.xlsx' -delete $true
        Converts source.xls to xlsx file type.
        Deletes source.xls when done.
        Deletes destination.xlsx before it converts.
    
        .EXAMPLE
        Convert-OLEDB -infile 'Source.xlsx' -outfile 'destination.csv' 
        Converts xlsx to csv.
        Leaves both files behind when done.
    
        .EXAMPLE
        Convert-OLEDB -infile 'Source.csv' 
        Converts infile Source.csv (or whatever format) to xlsx of the same name.
        Leaves both behind when done.
    
        .EXAMPLE
        Convert-OLEDB -infile 'Source.xlsx' -Extension '.csv' 
        Converts xlsx to csv. By passing just the extension it will use the same base file name.
        Leaves both files behind when done.
    
        .EXAMPLE
        dir *.xls | Sort-Object -Property LastWriteTime | Convert-OLEDB -Extension ".csv"
        Similar to above but uses the pipeline to do multiple conversions.
        If full outfile name is given, it will create just one file over and over again. In this example it would go in chronological order creating csv files.
    
        .EXAMPLE
        Dir *.xls | Convert-OLEDB -extension ".csv" -delete $True | Convert-OLEDB -extension ".xls"
        That's just weird, but it might solve your problem, and it works.
    
        .PARAMETER infile
        Name of the origin file to use. If the full path is not given it will be opened from the context the script is running in.
    
        .PARAMETER outfile (extension)
        Name of the destination file to create. If the full path is not given it will save in the default destination of Excel.
    
        .PARAMETER delete
        If $true it will delete the target location file if it exists before conversion and the origin file after conversion. Functions like a move with clobber.
        If anything else or blank it will leave origin in place and if destination exists it will prompt for overwrite.
        #>
        function Convert-OLEDB{
            param(
                [parameter(ValueFromPipeLineByPropertyName=$True,ValueFromPipeline=$True)]
                [Alias('FullName')]
                [string] $infile,
                [Alias('Extension')]
                [string] $outfile,
                [bool] $delete
            
            )
            Begin {
                $begin_outfile = $outfile
      
                $oledb = (New-Object system.data.oledb.oledbenumerator).GetElements() | Sort -Property SOURCES_NAME | where {$_.SOURCES_NAME -like "Microsoft.ACE.OLEDB*"} | Select -Last 1
                If ($oledb -eq $null) {
                    Write-Output "MICROSOFT.ACE.OLEDB does not seem to exist on this computer."
                    Write-Output "Please see https://www.microsoft.com/en-us/download/details.aspx?id=54920"
                    Write-Output "This can also happen if you have not installed the 32 or 64 bit version "
                    Write-Output "and you are running this script in the architecture that is missing it."
                    Write-Output "Solution is to do silent install on missing driver"
                    break
                } else {
                    $Provider=$Oledb.SOURCES_NAME
                    Write-Verbose "Provider $Provider found on this computer"
                }    
            }
    
            Process{
            #Check infile 
                if (-not($infile)) {
                    Write-Output "You must supply a value for -infile"
                    break
                }
                else {
                    Try {
                        $file = Get-Item $infile
                        $OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"; 
                        $OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand";
                        $OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter";
                        $DataTable = New-Object "System.Data.DataTable";
                        $Source = $file.FullName
    
                        Switch($file.Extension){
                            ".xls" {$OleDbConn.ConnectionString = "Provider=$Provider;Data Source=""$Source"";Extended Properties=""EXCEL 12.0;HDR=Yes;IMEX=1"";"}
                            ".xlsx" {$OleDbConn.ConnectionString = "Provider=$Provider;Data Source=""$Source"";Extended Properties=""EXCEL 12.0 XML;HDR=Yes;IMEX=1"";"}
                            ".xlsm" {$OleDbConn.ConnectionString = "Provider=$Provider;Data Source=""$Source"";Extended Properties=""EXCEL 12.0 MACRO;HDR=Yes;IMEX=1"";"}
                            Default {
                                $Source = $file.DirectoryName                        
                                $OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""$Source"";Extended Properties=""text;HDR=Yes;IMEX=1"";"
                                }
                        }
                        $OleDbCmd.Connection = $OleDbConn;
                        $OleDbConn.Open();
                        Switch($file.Extension){
                            {$_ -in (".xls", ".xlsx",".xlsm")} {$FirstSheet = $OleDbConn.getSchema("Tables")[0].Table_Name}
                            Default {$FirstSheet = $file.Name}
                        }
                        $OleDbCmd.CommandText = "select * from [$FirstSheet];”
                        $OleDbAdapter.SelectCommand = $OleDbCmd;
    
                        $RowsReturned = $OleDbAdapter.Fill($DataTable);
                        $OleDbConn.Close();
                    }
                    Catch {Write-Output "$infile does not seem to exist, or I can't get to it"; break}
                }
    
            #Check outfile
                #Reset value for pipeline loop            
                $outfile = $begin_outfile
    
                #If blank just presume xlsx
                if (-not($outfile)) {
                    $outfile = $file.FullName -replace '\.[^.]+$',".xlsx"
                    Write-Verbose "No outfile supplied, setting outfile to $outfile"
                }
    
                #If startswith a dot, use as an extension.
                If ($outfile.StartsWith(".")) {
                    $outfile = $file.FullName -replace '\.[^.]+$',$outfile
                    Write-Verbose "Extension supplied, setting outfile to $outfile"
                }
            
                if ($file.FullName -eq $outfile) {
                    #Nobody needs us to create a copy of an existing file.
                    write-verbose "Goal already achieved, moving on"
                }
                Else {
                    if(Test-Path ($outfile)){
                        #Avoid prompting to overwrite by removing an existing file of the same name
                        Remove-Item -path ($outfile)
                    }
    
                    Try {
                        #derive XlFileFormat from extension
                        if($outfile -cmatch '\.[^.]+$') {
                            $extens="" #Reset for pipeline loop
                            switch ($Matches[0]) 
                            {
                                ".csv"  {
                                    #Out to CSV
                                    $DataTable | Export-Csv "$outfile" -NoTypeInformation
                                }
                                ".xml"  {    
                                    #Simple, poorly formed:
                                    #$DataTable.TableName = $FirstSheet
                                    #$DataTable.WriteXml("$outfile")
    
                                    #BetterForm, strongly Typed, empty/Null differentiation
                                    Export-Clixml -Path "$outfile" -InputObject $DataTable
                                }
                                Default  {
                                    #Out to xlsx
                                    $OleDbConnOut = New-Object "System.Data.OleDb.OleDbConnection"; 
                                    $OleDbCmdOut = New-Object "System.Data.OleDb.OleDbCommand";
                                    $OleDbAdapterOut = New-Object "System.Data.OleDb.OleDbDataAdapter";
                                    $DataTable2 = New-Object "System.Data.DataTable";
    
                                    If($Matches[0] -eq ".xls"){$ext_swap = ""}
                                    If($Matches[0] -eq ".xlsx"){$ext_swap = " XML"}
                           
                                    $OleDbConnOut.ConnectionString = "Provider=$Provider;Data Source=""$outfile"";MODE=ReadWrite;Extended Properties=""Excel 12.0$ext_swap"";"
                                    $OleDbCmdOut.Connection = $OleDbConnOut;
                                    $OleDbConnOut.Open();
        
                                    $Create = "CREATE TABLE [Sheet2] ("
                                    $Stuff = "INSERT INTO [Sheet2] ("
                                    $Stuff2 = "VALUES ("
                                    $DataTable.Columns | % {
                                        $name = $_.ColumnName.Replace("#",".")
                                        $Type = $_.DataType
                                        $Stuff += "[$name], "
                                        $Stuff2 += "?, "
                                        $Create += "[$name] $Type, "
                                        $atname = "@" + $name
                                    }
                                    $Stuff = $Stuff.TrimEnd(", ") + ")"
                                    $Stuff2 = $Stuff2.TrimEnd(", ") + ")"
                                    $Create = $Create.TrimEnd(", ") + ")"
                                    $OleDbCmdOut.CommandText = $Create
                                    $UpdateCount = $OleDbCmdOut.ExecuteNonQuery()
    
                                    $OleDbAdapterOut.InsertCommand = $Stuff + " " + $Stuff2
    
                                    $DataTable | % {
                                        $Insert = "INSERT INTO [Sheet2] VALUES ("
                                        $_.ItemArray | % {
                                            $val = $_
                                            $Type = $_.GetType().Name
    
                                            switch ($Type) {
                                            "Double" {
                                                #Write "$Type"
                                                $Insert += "$val, "
                                                }            
                                            "DBNull" {
                                                #Write "$Type"
                                                $Insert += "NULL, "
                                                }
                                            Default {
                                                #Write "$Type"
                                                $val = $val.Replace("""","""""")
                                                $Insert += """$val"", "
                                                }
                                            }
                                        }
                                        $Insert = $Insert.TrimEnd(", ") + ")"
                                        $OleDbCmdOut.CommandText = $Insert
                                        Try {
                                            Write-Verbose "Attempting to run this command: $Insert"
                                            $updatedcount = $OleDbCmdOut.ExecuteNonQuery()
                                        }
                                        Catch{Write-Output "$Error[0] /nIt seems like there was a problem with this command: $Insert"}
                                    }
                                    $OleDbConnOut.Close();
                                    
                                    }
                                }
                            }
                        else {
                            Write-Output "Unable to determine the output file extenstion, this really shouldn''t happen"
                            break #if it can't find an extension in regex
                        }
                    }
                    Catch {
                        Write-Host "Unable to convert file $file because powershell with OLEDB cannot open or save it without help"
                        break
                    }
                    if ($delete) {#If asked to delete
                        if(Test-Path ($outfile)){ #And a file now exists where outfile said it should be
                            if(Test-Path ($infile)){ #And there is a file at infile
                                Remove-Item -path ($infile) #Delete it
                            }
                        }
                    }
                }
            }
            End{
                #Cleanup
            
            }
        }
    

    Monday, October 7, 2019 4:37 PM
  • You might take a look at the module ImportExcel. No need to re-invent the wheel.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Monday, October 7, 2019 11:15 PM