CODEWITHSUNDEEP
powershellvbscriptpowershell-2.0export-to-csvpowershell-4.0
Ezra P.
Ezra P.

Reputation: 91

Convert xlsx to CSV without using Excel

I get following error:

Cannot index into a null array. 
At C:\tmp\Folder\excel\output\net45\test.ps1:14 char:1
+ $Data = $Reader.AsDataSet().Tables[0].Rows
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo : InvalidOperation: (:) [], RuntimeException
     + FullyQualifiedErrorId : NullArray
# Zero based index. The second row has index 1.
$StartRow = 2
# Input File
$InputFileName = "C:\tmp\Folder\excel\output\net20\test.xlsx"
# Output File
$OutputFileName = "C:\tmp\Folder\excel\output\net20\SomeFile.csv"
# Path to Excel.dll is saved (downloaded from http://exceldatareader.codeplex.com/)
$DllPath = "C:\tmp\Folder\excel\output\net45\Excel.4.5.dll"  

[void]([Reflection.Assembly]::LoadFrom($DllPath))

$Stream = New-Object IO.FileStream($InputFileName, "Open", "Read")
$Reader = [Excel.ExcelReaderFactory]::CreateBinaryReader($Stream)
$Data = $Reader.AsDataSet().Tables[0].Rows

# Read the column names. Order should be preserved
$Columns = $Data[$StartRow].ItemArray

# Sort the remaining data into an object using the specified columns
$Data[$($StartRow + 1)..$($Data.Count - 1)] | % {
  # Create an object
  $Output = New-Object Object
  # Read each column
  for ($i = 0; $i -lt $Columns.Count; $i++) {
    $Output | Add-Member NoteProperty $Columns[$i] $_.ItemArray[$i]
  }
  # Leave it in the output pipeline
  $Output
} | Export-CSV $OutputFileName -NoType

Upvotes: 1

Views: 11716

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32220

You're calling the binary method (.xls) and using an Open XML format file (.xlsx). Try using [Excel.ExcelReaderFactory]::CreateOpenXmlReader($Stream) instead.

This works for me:

$DllPath = 'C:\Excel.DataReader.45\Excel.4.5.dll';
$FilePath = 'C:\Students.xlsx';

$FileMode = [System.IO.FileMode]::Open;
$FileAccess = [System.IO.FileAccess]::Read;

Add-Type -Path $DllPath;

$FileStream = New-Object -TypeName System.IO.FileStream $FilePath, $FileMode, $FileAccess;

$ExcelDataReader = [Excel.ExcelReaderFactory]::CreateOpenXmlReader($FileStream);

$ExcelDataReader.IsFirstRowAsColumnNames = $true;

$ExcelDataSet = $ExcelDataReader.AsDataSet();

$ExcelDataReader.Dispose();
$FileStream.Close();
$FileStream.Dispose();

$ExcelDataSet.Tables | Format-Table -AutoSize

If you're still having trouble, you might consider using the Microsoft.ACE.OLEDB.12.0 provider, which you install separately from Office. There's some doc here.

Upvotes: 2

jmrsilva
jmrsilva

Reputation: 11

I've read this "Convert XLS to CSV on command line" and this "convert-xlsx-file-to-csv-using-batch" before in a similar doubt I have. Try too see if it helps.

Upvotes: -2

Related Questions