1

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
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Ezra P.
  • 91
  • 1
  • 1
  • 12
  • What have you tried this far? It's commonly expected that questions in StackOverflow show some effort in terms of actual code. So, maybe try some programming on your own, and then ask specific questions concerning parts you have trouble with. Consider putting aside a few minutes to take a look at [some guidelines about asking questions](http://stackoverflow.com/help/how-to-ask) too. –  Mar 11 '16 at 13:15
  • 1
    I thought that loading a DLL file is different than use in your code: $E = New-Object -ComObject Excel.Application. Because Excel.Application opens the Excel. – Ezra P. Mar 11 '16 at 13:26
  • Why is "without using excel" important? – Walter Mitty Mar 11 '16 at 14:48
  • @AnsgarWiechers He's not loading Excel. He's loading a .Net DLL from http://exceldatareader.codeplex.com/ which knows how to parse a .xlsx file. As far as I can tell, it does not require any version of MS Excel to be installed, nor does it use the Excel.Application COM Object that ships with MS Office. – Bacon Bits Mar 11 '16 at 14:50
  • 1
    @BaconBits I figured it might be something like that. Still, in the original question the OP stated that loading DLLs (not only Excel) was out of the question, yet he was loading `Excel.4.5.dll`. So I asked for clarification. – Ansgar Wiechers Mar 11 '16 at 15:02
  • @WalterMitty "Why is without using excel important" - have you ever considered the **monster** benefit of being able to read and process and Excel file just by simply converting `.csv` then using `Import-Csv` to turn it into a `PsObject`. At that point you can process enormous amounts of Excel data simply with a script. Let's say you wanted to change every Excel sheet on a filesystem to contain a certain label or header. Would you like to do that by double clicking every single Excel file? – Kellen Stuart May 26 '17 at 16:02
  • So would using the powershell excel module be within the scope of what you are looking for? – Walter Mitty May 26 '17 at 20:07

2 Answers2

2

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.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
-2

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.

Community
  • 1
  • 1
jmrsilva
  • 11
  • 7