Fastest way to read from XLSX file

Is there a multithreaded way to read all data from a single-sheet XLSX file?

Currently I’m using the PsExcel module and doing a simple

$fileData = import-Excel -Path $Path

But the file I’m reading from has 16,000 rows and nearly 40 columns. If I specify that I only want columns 0-9, it still takes at least 3 minutes. If it were a CSV, I wouldn’t have this issue.

What’s the best route for tackling this problem?

copy it as a csv and then import?

[quote quote=163032]copy it as a csv and then import?

[/quote]
I did that and that of course improved the speed. Do I have other options or is there a way to automate that (in a timely manner)?

Did you try ImportExcel module ?

In the importexcel module there’s a command “ConvertFrom-ExcelSheet”

[quote quote=163041]In the importexcel module there’s a command “ConvertFrom-ExcelSheet”

[/quote]
Unfortunately that took a solid 13 minutes.

Did you try Import-Excel cmdlet in ImportExcel module ? I never worked with a large excel with this cmdlet

That’s what I had originally (look at my first post).

That is from PsExcel module, what i’m suggesting is ImportExcel module.

Since they both have a cmdlet named Import-Excel would I have to remove the PsExcel module?

not required, you can do wither of below ways.

  • Import only Import-Excel module
  • Use fully qualified name, ImportExcel\Import-Excel -Path …
ImportExcel\Import-Excel -Path $FilePath ...

[quote quote=163175]not required, you can do wither of below ways.

– Import only Import-Excel module

– Use fully qualified name, ImportExcel\Import-Excel -Path …

<textarea class="ace_text-input" style="opacity: 0; height: 17.6px; width: 6.59775px; left: 44px; top: 0px;" spellcheck="false" wrap="off"></textarea>
1
2
ImportExcel\Import-Excel -Path $FilePath ...
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
[/quote]

That method took approximately 10 minutes as well. I suppose there isn’t a good way of doing this

You might be able to do this by treating the Excel file as a database and using a database driver to connect to it.

Chrissy Le Maire wrote some great articles on working with large CSV files and this was one of the techniques she used.

I couldn’t get it working hacking the CSV examples on Chrissy’s blog but if I get more time over the weekend I’ll look into it a bit deeper.

Excel connection strings for various providers:
https://www.connectionstrings.com/excel/

Chrissy’s Articles:
https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/
https://blog.netnerds.net/2015/09/import-csvtosql-super-fast-csv-to-sql-server-import-powershell-module/