Fastest way to read from XLSX file

Welcome Forums General PowerShell Q&A Fastest way to read from XLSX file

This topic contains 12 replies, has 4 voices, and was last updated by

 
Participant
2 months, 3 weeks ago.

  • Author
    Posts
  • #163025

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

    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?

  • #163032

    Participant
    Topics: 37
    Replies: 155
    Points: 479
    Helping Hand
    Rank: Contributor

    copy it as a csv and then import?

  • #163035

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

    copy it as a csv and then import?

    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)?

  • #163038

    Senior Moderator
    Topics: 8
    Replies: 1041
    Points: 3,438
    Helping Hand
    Rank: Community Hero

    Did you try ImportExcel module ?

  • #163041

    Participant
    Topics: 37
    Replies: 155
    Points: 479
    Helping Hand
    Rank: Contributor

    In the importexcel module there's a command "ConvertFrom-ExcelSheet"

  • #163044

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

    In the importexcel module there's a command "ConvertFrom-ExcelSheet"

    Unfortunately that took a solid 13 minutes.

  • #163047

    Senior Moderator
    Topics: 8
    Replies: 1041
    Points: 3,438
    Helping Hand
    Rank: Community Hero

    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

  • #163074

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

    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).

    • #163121

      Senior Moderator
      Topics: 8
      Replies: 1041
      Points: 3,438
      Helping Hand
      Rank: Community Hero

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

  • #163170

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

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

  • #163175

    Senior Moderator
    Topics: 8
    Replies: 1041
    Points: 3,438
    Helping Hand
    Rank: Community Hero

    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 ...
    
  • #163218

    Participant
    Topics: 13
    Replies: 42
    Points: 243
    Rank: Participant

    not required, you can do wither of below ways.

    – Import only Import-Excel module

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

    1
    2
    ImportExcel\Import-Excel Path $FilePath ...
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

  • #163242

    Participant
    Topics: 1
    Replies: 302
    Points: 144
    Helping Hand
    Rank: Participant

    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:

    High-Performance Techniques for Importing CSV to SQL Server using PowerShell

    Import-CsvToSql: Super Fast CSV to SQL Server Import PowerShell Module

You must be logged in to reply to this topic.