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
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>
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:
Connection strings for Excel. Connect using CData ADO.NET Provider for Excel, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, OleDbConnection.
Chrissy’s Articles:
A while back, I wrote about High-Performance Techniques for Importing CSV to SQL Server using PowerShell. Earlier today, I released a PowerShell module, …