Convert XLSX file into CSV

using Powershell only

Any links to this site are appreciated.
If you decide to include a link to this article on your site, please drop us a note.

Abstract

This web page attempts to offer a solution to conversin of XLSX files (Excel workbooks) to CSV files.
This is easily done using Excel itself using its "Save As" option, but if one needs to do it in a batch mode and on a server machine, where it is customary not to install MS Office nor any s/w drivers including OLEDB, it becomes a real problem.
So, we offer a pure Powershell solution to conversion of xlsx files into csv.

Proposed Solution

A few points before presenting the solution:

It turned out that XLSX file is a zip file which contains several xml files with data.
The individual sheets are kept as separate XML files.
To save on space even more Microsoft stores all the character literal values in one common xml dictionary file. The individual cell value found for this string in the actual .xml file is just an occurence number in this dictionary file.
Dates are stored as day number since 1900/01/01 (at least they are supposed to. I discovered that one has to subtract 2 from this number of days to get the correct conversion).
Time portion of the date is stored as a fraction of a day, so it has to be multiplied by 60*60*24 (86400) to get the actual number os seconds.
Microsoft does not store empty cells or rows, so any gaps between values have to be taken care by the code.
To figure out the number of skipped columns one need to be able to figure out the distance between, say, cell "AB67" and "C67". The way columns are named: A through Z, then AA through AZ, then AAA through AAZ, etc., suggests that we may assume they are using a base-26 system and therefore use a simple conversion method from a base-26 to the decimal system and then use subtraction to find out the number of commas between columns.
If a cell value contains a comma, the enire value has to be enclosed in doublequotes.
If a cell value contains a doublequote each of them has to be doubled and then the value shoudl be enclosed in doublequotes.

With this in mind here is the code:

param([string]$xlsx="C:\mydir\myfile.xlsx", [string]$sheet="sheet1")
$strDir = Split-Path  $xlsx
$strFileName = [System.IO.Path]::GetFileName($xlsx)

$strTempDir = "c:\temp"
$strFullFileName = Join-Path $strDir $strFileName

if ((Test-Path -path $strFullFileName) -ne $True) {
   throw [SYstem.IO.FileNotFoundException] "File $strFullFileName not found"
}

$strFileNameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($strFileName)
$strFileExtension = [System.IO.Path]::GetExtension($strFileName)
$strZipFileFullName = Join-Path $strDir ($strFileNameWithoutExtension + '.zip')
$strUnzipDirName = Join-Path $strTempDir $strFileNameWithoutExtension

# convert column name into sequential column #
Function Get-Column-number ($cellNm) {
    # Separate the column name from cell Id: if $cellNm = "AB34", then the strColNm is AB
    $strColNm = ""
    for ($i = 0; $i -le $cellNm.Length-1; $i++) {
        $c = $cellNm.substring($i,1)
        if ("0123456789".IndexOf($c) -ge 0) {$strColNm = $cellNm.Substring(0,$i); break}
    }
    # A corresponds to 1, Z - to 26, AA = 27, etc. So, we are doing conversion from a base 26 to base 10
    $num = 0
    for ($i = 0; $i -le $strColNm.Length-1; $i++) {
        $num = $num * 26 * $i + ([int][char]$strColNm.substring($i,1)-64)
    }
    $num
}



# 1. =====================================
# rename the .xlsx file into .zip
# unzip it into the directory with the same base name (delete the previous version if necessary)
# rename the .zip file back into .xlsx
#
Rename-Item $strFullFileName ($strFileNameWithoutExtension + '.zip')

$shell = New-Object -com shell.application
$zip = $shell.NameSpace($strZipFileFullName)
if (Test-Path $strUnzipDirName) {
    Remove-Item $strUnzipDirName -force -Recurse -Confirm:$false
}
$d = New-Item $strUnzipDirName -itemType directory
foreach($item in $zip.items()) {
    $shell.Namespace($strUnzipDirName).copyhere($item)
}
Rename-Item $strZipFileFullName ($strFileNameWithoutExtension + $strFileExtension)
# Now the content of .xlsx file is in $strUnzipDirName directory
#####################################################################################################

# 2. =====================================
# read xl\workbook.xml into memory
# build an array of sheet names
#
[xml]$xmlSheetIds = Get-Content (Join-Path $strUnzipDirName "xl\workbook.xml")
[System.Collections.ArrayList]$arrStrSheetIds = @()
foreach($sheetTag in $xmlSheetIds.workbook.sheets.sheet) {
    $r = $arrStrSheetIds.add(@{$sheetTag.name='sheet'+$sheetTag.sheetId})
#    write-host("sheetTag="+$sheetTag.sheetId+", sheetName="+$sheetTag.name)
}
# Now all the sheetnames can be converted into sheet xml names are in $arrStrSharedStrings
$strSheetName = $arrStrSheetIds.$sheet
# now parameter $sheet is converted into a correct sheet1 or sheet2, etc.
#####################################################################################################


# 3. =====================================
# read xl\sharedStrings.xml into memory
# build an array of shared strings
#
[xml]$xmlSharedStrings = Get-Content (Join-Path $strUnzipDirName "xl\sharedStrings.xml")
[System.Collections.ArrayList]$arrStrSharedStrings = @()
foreach($si in $xmlSharedStrings.sst.si) {
    $r = $arrStrSharedStrings.add($si.t)
}
# Now all the sharedStrings are in $arrStrSharedStrings
#####################################################################################################

# 4. =====================================
# read xl\styles.xml into memory
# build an array of custom numFmts and a hashtable of all numFmts
#
[xml]$xmlStyles = Get-Content (Join-Path $strUnzipDirName "xl\styles.xml")
[System.Collections.ArrayList]$arrNumFmts = @()
foreach($numFmt in $xmlStyles.StyleSheet.numFmts.numFmt) {
#    write-host($numFmt.formatCode+" "+$numFmt.numFmtId)
$r = $arrNumFmts.add(@{'numFmtId'=$numFmt.numFmtId; 'formatCode'=$numFmt.formatCode; 
    'dataType' = if (($numFmt.formatCode -match 'h') -or ($numFmt.formatCode -match 's')) {
                    'datetime'
                 } elseif (($numFmt.formatCode -match 'd') -or ($numFmt.formatCode -match 'y')) {
                    'date'
                 } else {
                 'number'
                 }
            }
    )
}


$r = $arrNumFmts.add(@{'numFmtId'='0'; 'formatCode' = 'General';dataType='general'})
$r = $arrNumFmts.add(@{'numFmtId'='1'; 'formatCode' = '0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='2'; 'formatCode' = '0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='3'; 'formatCode' = '#,##0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='4'; 'formatCode' = '#,##0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='5'; 'formatCode' = '$#,##0;\-$#,##0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='6'; 'formatCode' = '$#,##0;[Red]\-$#,##0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='7'; 'formatCode' = '$#,##0.00;\-$#,##0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='8'; 'formatCode' = '$#,##0.00;[Red]\-$#,##0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='9'; 'formatCode' = '0%';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='10'; 'formatCode' = '0.00%';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='11'; 'formatCode' = '0.00E+00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='12'; 'formatCode' = '# ?/?';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='13'; 'formatCode' = '# ??/??';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='14'; 'formatCode' = 'mm-dd-yy';dataType='date'})
$r = $arrNumFmts.add(@{'numFmtId'='15'; 'formatCode' = 'd-mmm-yy';dataType='date'})
$r = $arrNumFmts.add(@{'numFmtId'='16'; 'formatCode' = 'd-mmm';dataType='date'})
$r = $arrNumFmts.add(@{'numFmtId'='17'; 'formatCode' = 'mmm-yy';dataType='date'})
$r = $arrNumFmts.add(@{'numFmtId'='18'; 'formatCode' = 'h:mm AM/PM';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='19'; 'formatCode' = 'h:mm:ss AM/PM';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='20'; 'formatCode' = 'h:mm';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='21'; 'formatCode' = 'h:mm:ss';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='22'; 'formatCode' = 'm/d/yy h:mm';dataType='datetime'})

$r = $arrNumFmts.add(@{'numFmtId'='37'; 'formatCode' = '#,##0 ;(#,##0)';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='38'; 'formatCode' = '#,##0 ;[Red](#,##0)';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='39'; 'formatCode' = '#,##0.00;(#,##0.00)';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='40'; 'formatCode' = '#,##0.00;[Red](#,##0.00)';dataType='number'})

$r = $arrNumFmts.add(@{'numFmtId'='44'; 'formatCode' = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='45'; 'formatCode' = 'mm:ss';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='46'; 'formatCode' = '[h]:mm:ss';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='47'; 'formatCode' = 'mmss.0';dataType='time'})
$r = $arrNumFmts.add(@{'numFmtId'='48'; 'formatCode' = '##0.0E+0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='49'; 'formatCode' = '@';dataType='number'})

$r = $arrNumFmts.add(@{'numFmtId'='27'; 'formatCode' = '[$-404]e/m/d';dataType='datetime'})
$r = $arrNumFmts.add(@{'numFmtId'='30'; 'formatCode' = 'm/d/yy';dataType='datetime'})
$r = $arrNumFmts.add(@{'numFmtId'='36'; 'formatCode' = '[$-404]e/m/d';dataType='datetime'})
$r = $arrNumFmts.add(@{'numFmtId'='50'; 'formatCode' = '[$-404]e/m/d';dataType='datetime'})
$r = $arrNumFmts.add(@{'numFmtId'='57'; 'formatCode' = '[$-404]e/m/d';dataType='datetime'})

$r = $arrNumFmts.add(@{'numFmtId'='59'; 'formatCode' = 't0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='60'; 'formatCode' = 't0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='61'; 'formatCode' = 't#,##0';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='62'; 'formatCode' = 't#,##0.00';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='67'; 'formatCode' = 't0%';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='68'; 'formatCode' = 't0.00%';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='69'; 'formatCode' = 't# ?/?';dataType='number'})
$r = $arrNumFmts.add(@{'numFmtId'='70'; 'formatCode' = 't# ??/??';dataType='number'})

[System.Collections.ArrayList]$arrCellXfs = @()
foreach($xf in $xmlStyles.StyleSheet.cellXfs.xf) {
#    write-host($numFmt.formatCode+" "+$numFmt.numFmtId)
    $r = $arrCellXfs.add(@{'numFmtId'=$xf.numFmtId})
}

# Now all the formats are in $arrNumFmts
# and all styles in $arrCellXfs
#####################################################################################################


#5. ====================================== 
if ((Test-Path -path (Join-Path $strUnzipDirName ("xl\worksheets\$strSheetName"+".xml"))) -ne $True) {
   throw [SYstem.IO.FileNotFoundException] "Incorrect sheet name $sheet"
}

#6. ======================================
# Read actual xml with the sheet name $strSheetName
[xml]$xmlData = Get-Content (Join-Path $strUnzipDirName ("xl\worksheets\$strSheetName"+".xml"))

#7. ======================================
# Read column information from the sheet $strSheetName and store the style information for each column 
#
[System.Collections.ArrayList]$arrColStyles = @()
$cn = 0 # column number
foreach($col in $xmlData.worksheet.cols.col) {
        for ([int]$i = $cn; $i -lt $col.min-1; $i++) {   
            $r = $arrColStyles.add(@(""))
        }
        $cn = $col.min
#        $r = $arrColStyles.add(@(if($col.style =$null) {"0"} else {$col.style}))
        $r = $arrColStyles.add(@($col.style))
#        write-host("r="+$r+" s="+$col.style)
}



#8. ======================================
# process xl/worksheets/$strSheetName.xml 
# Process skipped rows
# For each row loop through its cells. Process skipped cells. For each cell get its value (it is not a trivial excercise) and create csv output line
# 
[int]$lastRow = 0
foreach($row in $xmlData.worksheet.sheetData.row) {
    # write-host("row="+$row.r)
    $currRow = [int]$row.r
    # print $currRow-$lastRow-1 empty lines (Excel doesn't store skipped lines)
    for ($i = $lastRow; $i -lt $currRow - 1; $i++) {
        write-output("")
    } 
    $strOut = ""
    $lastCell = 0
    foreach($c in $row.c) {
        $t = $c.t
        $s = $c.s
        $v = $c.v
        $r = $c.r
        $vi = $c.is.t
        $currCell= Get-Column-number $r
        $glbColStyle = $arrColStyles[$currCell-1] 
        $glbColStyle= if("a"+$arrColStyles[$currCell-1] -eq "a") {0} else {$arrColStyles[$currCell-1]}
        $finalColStyle=if("a"+$s -eq "a") {$glbColStyle} else {$s}
        # find what this style corresponds to
        $fmt = $arrCellXfs[$finalColStyle].numFmtId
       
        for ([int]$i = 0; $i -le 300; $i++) { 
            if ($arrNumFmts[$i].numFmtId -eq $fmt) {break}
        } 
        $dtype = $arrNumFmts[$i].dataType
#        write-host("col="+$currCell+"   c.t="+$t + ", c.s="+$s+" v="+$v+",vi="+$vi+", c.r = "+$r+", colstyle="+$glbColStyle+", finalColStyle = "+$finalColStyle+" , format="+$fmt+", datatype="+$dtype)
        $strRealVal = ""
        #processing the value based on t attribute (t="str" means it is a real string, t="s" - lookup in shared string
        switch ($t) {
            $null       {$strRealVal=$v;break}
            'str'       {$strRealVal=$v;break}
            's'         {$strRealVal=$arrStrSharedStrings[$v];break}
            'inlineStr' {$strRealVal=$vi;break}
            default{$strRealVal="don't know";break}
        }

        if ($strRealVal) {
            switch ($dtype) {
                'general' {
                        $strRealVal=$strRealVal
                }
                'date' {
                    try {
                            $strRealVal = Get-Date((Get-Date "1/1/1900 00:00 am" ) + $strRealVal).AddDays(-2) -format "MM/dd/yyyy"  # no idea why I need to subtract 2
                    } catch [System.Exception] {
                        $strRealVal = $strRealVal
                    }
                }
                'datetime' {
                    try {
                           $intDays = [System.Math]::Truncate($strRealVal)
                           $decFrac = $strRealVal - $intDays
                           $intSeconds =  [System.Math]::Round($decFrac*86400)
                           $strRealVal = Get-Date((Get-Date "1/1/1900 00:00 am" ) + $intDays).AddDays(-2).AddSeconds($intSeconds) -format "MM/dd/yyyy HH:mm:ss" # no idea why I need to subtract 2
                    } catch [System.Exception] {
                        $strRealVal = $strRealVal
                    }
                }
                'time' {
                    try {
                           $intDays = [System.Math]::Truncate($strRealVal)
                           $decFrac = $strRealVal - $intDays
#                           $intSeconds =  [System.Math]::Truncate($decFrac*86400)
                           $intSeconds =  [System.Math]::Round($decFrac*86400)
                           $strRealVal = (Get-Date((Get-Date "1/1/1900 00:00 am" ) + $intDays).AddDays(-2).AddSeconds($intSeconds) -format "MM/dd/yyyy HH:mm:ss").Substring(11)
                    } catch [System.Exception] {
                        $strRealVal = $strRealVal
                    }
                }
                default {$strRealVal=$strRealVal}
        }
        }

        if ($strRealval) {
            # if the resulting text contains " replace it with double quotes
            if ($strRealVal.indexOf('"') -ge 0) {
                $strRealVal = $strRealVal -replace '"','""'
            }

            # if the resulting text contains ", or ,  or hard return, put doublequotes around it
            if (($strRealVal.indexOf('"') -ge 0 ) -or ($strRealVal.IndexOf(',') -ge 0)  -or ($strRealVal.IndexOf([char]10) -ge 0)){
                $strRealVal = '"'+$strRealVal + '"'
            }
        }
        # $strRealVal is formatted and ready to be appended to the output stream, but we need to know how many commas to insert in font of it
        # since Excel may have empty cells 
        $strOut = $strOut+(',' * ($currCell- $lastCell))  # add appropriate number of commas
        $strOut = $strOut+$strRealVal  # add the value 
        $lastCell = $currCell
    }
    if ($strOut) {
        $strOut = $strOut.Substring(1)  # remove the leading comma
    }
    write-output($strOut)  # output results
    $lastRow = $currRow
}

#9.  Cleanup ====================================
#    Delete the unzipped directory from c:\temp
if (Test-Path $strUnzipDirName) {
    Remove-Item $strUnzipDirName -force -Recurse -Confirm:$false
}

To call this code you may use a command like this:
@echo off set mydir=%~dp0 set mydrive=%~d0 powershell -File %mydir%\convert_xlsx_to_csv.ps1 -xlsx %mydir%\convert_xlsx_to_csv_test.xlsx -sheet Sheet1

The resulting will be printed, so you will have to redirect it to a file.

We hope you found this article useful. If you have any comments about this article please send them to Boris Knizhnik borisk@bikinfo.com. We appreciate any links to this site as they show your appreciation and work as free ads. If you decide to include a link to our site on your site, please drop us a note.

Back to BIK Information Services home page Go to top