Fastest way to read from XLSX file

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

Viewing 11 reply threads
  • 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: 50
      Replies: 199
      Points: 806
      Helping Hand
      Rank: Major 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: 9
      Replies: 1239
      Points: 4,457
      Helping Hand
      Rank: Community Hero

      Did you try ImportExcel module ?

    • #163041
      Participant
      Topics: 50
      Replies: 199
      Points: 806
      Helping Hand
      Rank: Major 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: 9
      Replies: 1239
      Points: 4,457
      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: 9
        Replies: 1239
        Points: 4,457
        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: 9
      Replies: 1239
      Points: 4,457
      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 …

      <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

      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: 148
      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

Viewing 11 reply threads
  • The topic ‘Fastest way to read from XLSX file’ is closed to new replies.