feedback on ImportExcel

[dfinke]

Hi there 👋

I'm starting this discussion to ask you for some feedback on ImportExcel. I'd love to know what you use the module for, whether you've found it useful, and what other kinds of information or ideas you'd like to see covered. I'd love to hear your ideas on how to improve the module too.

Also:

• 💡How does the product fit into your business?
• 💡What features do you use most often?
• 💡Feel free to post examples of scripts you've built or Excel solutions you've created.

Share your thoughts and ask me.

Thanks in advance for your help!

[stahler]

Doug,

The overwhelming majority of our use of the ImportExcel module is related either to Active Directory or marrying data from SQL with Active Directory. We make heavy use of the SQLServer module as we pull data from disparate databases and either merge the data or compare for reporting and automation purposes.

Many teams across our enterprise use the ImportExcel module, I know there are reports being created for M365 (especially Exchange) and reports created out of our electronic medical record (EMR). It has been evangelized often with various teams and is a highly regarded and utilized module.

As far as specific functionality is concerned, I'd say that these are the primary cmdlets being used from the module: Import-Excel, Export-Excel, Get-ExcelFileSummary, Invoke-ExcelQuery and Read-Clipboard.

Your module is used everyday by many and along with the ServiceNow module are excellent examples of the open community spirit.

Keep up the great work!

[dfinke]

Thanks Wes, great feedback!

[bobbynog]

Hi Doug We use ImportExcel for automating the process of getting report data out of our SQL Server data warehouse for distribution to customers. Doing Excel via SSIS is agony, not easily customised, doesn’t give table formatting options, etc. Specifically we use: * Get-ExcelSheetInfo: when rendering spreadsheets via SSRS, we use this to write tab information to the database (allows us to avoid creating duplicate tab names when merging SSRSs together or adding new tabs with query data) * Send-SQLDataToExcel: oh baby this changed our world! This allows us to do things in spreadsheets properly; most “Excel” report options are just CSVs with different names. The following switches are manna from heaven: * -AutoSize * -AutoFilter * -BoldTopRow * -FreezePane (for freezing header rows and/or columns, and as the number can be specified we can freeze both Title and Column headers) * -Title (plus -TitleBold and -TitleSize - make it look so much more polished) * -TableStyle (branding for free) * Copy-ExcelWorkSheet: when merging SSRS spreadsheet outputs together * Set-Format: we only use this to set the formatting on values that are Database Date format, to take out the time element Our next piece of work will be dynamically generating dynamic PowerShell scripts to use ImportExcel’s components more effectively, e.g. for Copy-ExcelWorkSheet, only opening the destination workbook once and writing other worksheets to it, rather than having it open, save and close the destination with each Copy. But in summary, ImportExcel allows us to do things with spreadsheets on our database server that we wouldn’t easily be able to otherwise. Bob From: Wes Stahler ***@***.***> Sent: 29 August 2022 21:06 To: dfinke/ImportExcel ***@***.***> Cc: Subscribed ***@***.***> Subject: Re: [dfinke/ImportExcel] feedback on ImportExcel (Discussion #1238) Doug, The overwhelming majority of our use of the ImportExcel module is related either to Active Directory or marrying data from SQL with Active Directory. We make heavy use of the SQLServer module as we pull data from disparate databases and either merge the data or compare for reporting and automation purposes. Many teams across our enterprise use this module, I know there are reports being created for M365 (especially Exchange) and reports created out of our EMR. It has been evangelized often with various teams and is a highly regarded and utilized module. As far as specific functionality is concerned, I'd say that these are the primary cmdlets being used from the module: Import-Excel, Export-Excel, Get-ExcelFileSummary, Invoke-ExcelQuery and Read-Clipboard. Your module is used everyday by many and along with the ServiceNow module are excellent examples of the open community spirit. Keep up the great work! — Reply to this email directly, view it on GitHub <#1238 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/A2B66YVSGJQGAXJAQN6ZSK3V3VNBLANCNFSM577RA2CA> . You are receiving this because you are subscribed to this thread. <https://github.com/notifications/beacon/A2B66YUDTGGW2QIAKJTJEODV3VNBLA5CNFSM577RA2CKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAA2XUKY.gif> Message ID: ***@***.*** ***@***.***> >

[dfinke]

Thank you Bob, really good stuff!

[caratage]

I use it similar to Bob as replacement of SSIS Excel functions to generate reports out of a DWH. Made life a lot easier!

[ninmonkey]

Export-Excel -TableName RawData is nice, then you can use structured references in excel formulas like

= DeptSales[Sales Amount] * DeptSales[% Commission]

[TWalijew]

Hi,
as the others contributors our goal is to extract SQL data from databases, do some stats maths and deliver the information gained in a user/management-friendly way, all automated and scheduled.
Needless to mention that Export-Excel with its variety of options is the highlight.
Best Regards
Thomas

[dfinke]

@TWalijew Thank you for the feedback, very helpful. What sort of stats do you do? Do you use PS for that?

[TWalijew]

Well,
mainly the usual stats stuff like Percentage/Min/Max/Count/Average.
Besides that also:

• moving average of e.g. CPU/memory loads
• number of Info, Warnings, Errors and Critical log messages
  We use PS, especially to retrieve/analyze data from Azure services, although there are some Python scripts dealing with FTP

[SQLvariant]

Over the years the # 1 thing I have use the ImportExcel module for is to take data from Excel files and load them into tables in a SQL database. On occasion I have been known to use it to export data from a database into Excel files.

As of late I've been using Import-Excel as a kind of 'quick & easy' way to get my data sorted out in rows and columns, and then convert it to a properly formed .JSON file for use in a downstream process.

Almost makes me wonder if it could do the same kind of thing for .YAML files I need to construct for my deployment pipeline? 🤔

I literally can't calculate how much time I have saved over the years thanks to the ImportExcel module; I'm not talking about days here I'm talking about weeks of saved time getting something working & deployed quickly thanks to this module 👏

[dfinke]

Great feedback, thank you @SQLvariant. Ask and ye shall receive. You need to install the EZOut module to get access to that feature.

https://github.com/dfinke/ImportExcel/blob/master/Examples/FormatResults/GetAsYaml.ps1

[JeremiahTheFirst]

I'm an Excel nerd, so when I took over my position that required some work in Excel I gradually worked on automating anything I could - I reached the point where I was pulling info down from an API as a DataTable and throwing it into a csv to then manipulate it with macros, but I really needed one file and multiple sheets, not tens of sheets a week, so csv wouldn't cut it, leading me to here.

I believe I exclusively use Open-ExcelPackage to see if I already have the file I need and, if so, to identify the index of a worksheet named similarly to the one I want to append so I can append it immediately after (a bonus) and then Export-Excel to actually do the thing and put it where I want.

I spent quite a bit of time hemming and hawing over maybe trying to cobble together something that would do the same thing but I finally bit the bullet and accepted the genius of other people's tireless efforts and stopped trying to reinvent the wheel and whew, it has been easy since then!

[dfinke]

Great use case. Glad the module helped out.

[DrMarkX]

When @stahler introduced ImportExcel to me, it removed obstacles that business object tools could not overcome. I created scripts that accomplished the work of numerous cobbled tools that tended to break without notification. I finally have a way to turn over some maintenance to the end-users by allowing them access to a spreadsheet that I consume through ImportExcel. And with the addition of Invoke-ExcelQuery, I can quickly join extensive data into a single report, eliminating the need for elaborate custom objects using Where-Object to link information.

I have run into issues when I avoid Invoke-ExcelQuery and opt for either Add-Member or Where-Object. If I import data from excel into a variable and store data in another variable from a source like Active Directory or another excel file and then attempt to join the two with ForEach & Where-Object, my CPU usage spikes. Then, this process takes a very long time to complete.

This week I wrote a script that stored data from two spreadsheets and a SQL table. Matching rows on a single item took over an hour to complete for one report and over 15 hours before I killed the script on another. I rewrote that script using Invoke-ExcelQuery, and the reports were generated in 1 minute instead of an hour and 30 seconds instead of 15+ hours.

That's the only issue I have noticed. This is a fantastic tool that provides so many automation opportunities for our team. Thank you!!

[dfinke]

Sounds great. I reviewed/collaborated with the person who implemented the Invoke-ExcelQuery function. I liked it, wasn't sure if it would be used. Thanks for the feedback!

[ninmonkey]

For testing, do you have example code that triggers the performance issue?

[gmjulian]

My team mainly uses the report to pull info out of Active Directory and parse other data feeds. Previous to my team taking over this responsibility, the data was gather and manipulated manually so it was super time consuming and the other teams that relied on it were always waiting. Many of the files that we generate now are either emailed using a sendmail function or simply output directly to a shared location each morning so that they can self service. It has been a huge time saver and productivity benefit as I rarely hear from them anymore :) Kudos to you and your team for this tool!

[dfinke]

Awesome, use PowerShell automation for Excel to reduce clients always asking "where's my Excel file?" :)

[ninmonkey]

I have some multi-stage transformations between a web APIs, that it's nice to visualize and validate the stages.
I'm basically importing 4 Csv files merged into one workbook. Being able to export as tables, is nice, because you automatically get sorting, and column filters for free. Plus fancy-pants excel stuff, like the new (to me) structured references

=SORT(UNIQUE( EmployeeTable[LastName] ))
=DeptSales[[Sales Person]:[Region]]

Adding conditional formatting for blank values goes a long way. It's easier to scan a page.
My tiny wrapper saves as filetime, so I can keep one or two previous runs open, without messing with saving or copying files.

Stuff I have questions on

1] Not seeing how I apply conditional formatting to a column (code below), ie: how do I get an address from my column?

2] and what object types are better to populate tables?

• I have a couple Pwsh-classes, that piping an array seems to export good using a [list[object]]`
• it hides columns with hidden attributes

But, What if I wanted to add a calculated property, without changing the class definition? It didn't always seem to render the temp columns in my attempt. ( maybe that wasn't the problem ). I was thinking:

• Do I pipe instances as normal, piping through add-member first ?
• Or Select-Object to lose the class definition, then piping through add-member?

This errors, I am trying

What isn't working is trying using column names. I'm close, the column is valid
$t_sheet.Tables['IndexCache'].Columns['terminationDate'].GetType() returns OfficeOpenXml.Table.ExcelTableColumn

$t_sheet = $pl.Workbook.Worksheets['IndexCache']
$t_table = $t_sheet.Tables['IndexCache']
$addConditionalFormattingSplat = @{
    Address         = $t_sheet.Tables['IndexCache'].Columns['terminationDate'] 
    WorkSheet       = $t_sheet
    RuleType        = 'NotContainsBlanks'
    BackgroundColor = [excelColor]::FromRGB( 0xff, 0x5f, 0x89 ) #'#ff5f89'
}
Add-ConditionalFormatting @addConditionalFormattingSplat

Cannot convert argument "address", with value: "OfficeOpenXml.Table.ExcelTableColumn", for
     | "AddNotContainsBlanks" to type "OfficeOpenXml.ExcelAddress": "Cannot convert the
     | "OfficeOpenXml.Table.ExcelTableColumn" value of type "OfficeOpenXml.Table.ExcelTableColumn" to type
     | "OfficeOpenXml.ExcelAddress"."

This part works: table.Address.Address

$t_sheet = $pl.Workbook.Worksheets['IndexCache']
$t_table = $t_sheet.Tables['IndexCache']
$addConditionalFormattingSplat = @{
    Address         = $t_table.Address.Address # note, .Address 1 time will not work
    WorkSheet       = $t_sheet
    RuleType        = 'ContainsBlanks'
    BackgroundColor = [excelColor]::FromRGB( 0xff, 0xbf, 0x89 ) #'#ffbf89'
}
Add-ConditionalFormatting @addConditionalFormattingSplat```

[dfinke]

Thanks for the feedback @ninmonkey. Please add these as issues, better tracking and visibility.

[ninmonkey]

Will do, I haven't forgotten.

[Chuckles55]

We receive spreadsheets with hundreds of columns, those spreadsheets sometimes being modified by (ahem) overly-enthusiastic users to include additional columns beyond what was originally asked. Sometimes we get what IBM would call "unexpected values". Standard tools (think SSIS) like stable and predictable which these spreadsheets sometimes ain't.
So, with the help of the wonderful Import-Excel module, we PowerShell whatever is in the spreadsheets, creating SQL table-structures on-the-fly using information gotten from the column-characteristics provided by Import-Excel, then we load the extracted data. Easy-peasy, works like a charm.
So and as I mentioned earlier to DougF, the public employees of a certain Pacific Northwest state thank him for his contribution. Because of the efficiencies it enabled, the saved money and time was available as assistance to extremely vulnerable, at-risk citizens.

[dfinke]

That's great, thank you for writing that up.

[RoYoMi]

The Export-Excel option called CellStyleSB is really cool, massively powerful, but under documented. Recommend adding some additional notes about this feature.

[dfinke]

Thanks for the feedback. That was a very early experiment (years ago). It is under documented because I didn't work to figure out how to make it more bullet proof. While cool, it opens up a potential landslide of support questions, allowing users to add arbitrary code in a scriptblock.

[steve-daedilus]

Hi Doug,

My use case has been automating the generation of reports from disparate data items.

In one example (the one you helped with recently), I am processing ten's of thousands of verbose logs for specific data and appending the resulting object into a data table. Then I am building analytics to front that data in a way that will provide value to customers.

In another example, I am ingesting a relatively small number of CSVs which conform to certain standards, and am creating worksheets based on the specific standard of a given file. This is collating environment health from multiple customers into a single file, thus vastly reducing the manual effort and overall time required to collate this information otherwise.

This is making such a big difference, and I can foresee other uses in the near future.

[dfinke]

Thanks @steve-daedilus, really good to hear

[jdenicola-pa]

For one project, I use it to import data from Excel spreadsheets into a data warehouse. These are sheets maintained by finance people, who of course are very comfortable with Excel. It saves me from having to write a CRUD app to let them enter/view the data. Of course, with Excel being as flexible as it is, I have to build in lots of checks and exceptions because they don't think about how the data can be imported - if Excel can do it, then they think it is just fine to do (such as pressing Alt-Enter and entering multiple values in a single cell...)

In another project, I use it to generate a Data Dictionary from SalesForce metadata. Each SalesForce object (table) is a sheet (tab) and on each it lists the fields, data types, validation rules, etc.

It works very well, I just wish there were some reference docs (if there are, I can't find them). I find myself having to either find an example, which may not use every option available, or going into the source code to try to figure out what options are available and what they do.

[dfinke]

Thank you! Great scenarios. There are no reference docs. I'm guessing you've seen/used these https://github.com/dfinke/ImportExcel/tree/master/Examples. Could you say a bit more about what you'd like to see in a "reference doc"?

[jdenicola-pa]

Yes, I've see the examples, and they are good if they do something similar to what I want - in fact the examples are something lacking in much documentation out there.

By reference docs I mean just a page for each cmdlet, with a description of what it does, syntax, and a listing all of the parameters available and description of what they do. Then examples, or links to examples would make it complete.

I think PnP Powershell does a nice job with their cmdlet reference docs - example:

Add-PnPFile

One criticism I have for the PnP Powershell reference docs is in the Syntax, it references " []" but nowhere lists what they consider "CommonParameters". In general, I know what they mean, such as "-ErrorAction" but which ones are implemented? For example, "-WhatIf" is considered a common parameter - but is it implemented? I can't tell without actually trying it.

[dfinke]

Thanks again, appreciate the feedback and the scenarios you laid out.

[elevenize]

Hi Doug, wanted to chime-in that some reference documentation would be very helpful. I'm working on a way to import 200 separate Excel files and merge them together into one file, one sheet. I have some basic code to do this, but since different teams are creating the files, there can be differences, and sheet names are unique. The code will have to start with some logic to clean up and normalize the files.

Have managed to piece together portions of the cleanup code, but it’d be great to have reference documentation to find details such as how to open a worksheet without knowing the worksheet name, how to rename the worksheet, how to trim spaces in a cell, trim entire column or row, and other details on how to work with files.

[dfinke]

Thanks for the feedback @elevenize. I have some ideas on this. Quick note, there is nothing in the module to trim whitespace that way. One way is the data would need to be read into PowerShell and then you'd .Trim() it there. You have to figure out Rows/Columns in the PowerShell array created.

[dcorrada]

Hi there,
I wrote a script for fetching data about SKU assignments from Microsoft365 tenant and, subsequently, creating updated summary reports onto an xlsx file.

I've started with COM Object instances, then I have switched to PSExcel functions. Now I am translating my script for adopting ImportExcel cmdlets.

I have re-edited most part of my script, still remains a little chunk to fix: how to create a new initial xlsx empty file?

The Export-Excel cmdlet works very well on existng excel files, but it doesn't succeed when I need to create one ex novo. I am looking for documentation without success yet.

Thnk you for your patience

[JeremiahTheFirst]

Hey fella,
I know this wasn't directed at me, but I saw it and can say this is in-line with my usage so I have some experience. Now, admittedly, my version is 7.8.4 and I have used prior versions in this way as well, so barring some sort of change in functionality since then, this should hold:

It should work the exact same way with creating a file as it does with an existing file. I looked at what I use and it is:

Export-Excel $pathVariable -Worksheetname 'Whatever' -Append -MoveAfter $indexVariable

I only have the append info so I can call it the same way if the file exists already or doesn't and it has worked for me in both cases.

[dcorrada]

Hi @JeremiahTheFirst,
in the meanwhile of your reply I have found such thread on Stack Overflow.

I fixed my issue creating first an ExcelPackage and redirecting Export-Excel towards it, instead of xlsx file directly.

Here there is my script freshly committed, I will merge it to master branch soon.

Thank you a lot, anyway, for your timely support

[JeremiahTheFirst]

Ah, nice! Yes, I didn't think to mention, I setup my function to accept a passed in $excel object and create it if it is not passed, but after creating the file, and then utilize it to do stuff. Glad you got yourself sorted out!

[dfinke]

@dcorrada Thanks for moving to ImportExcel. Funny history. PSExcel was put together by Warren Frame, tip top PowerShell/Developer. He saw me publish ImportExcel, saw my approach, thought I was done and started building PSExcel in a similar way. He pinged me after I pushed more features. He apologized and said he'd let me run with it and asked that I tell him if I abandoned the effort.

There are other efforts out there, discount versions of my ImportExcel, and you look, used many of my function names and code.

Anyway

@dcorrada that script looks pretty chewy. Would love to see what it looks like.

[dcorrada]

@dfinke glad to read your clarifications about the development of PSExcel/ImportExcel, and the other forks and variants.

The script aims to keep, into an Excel file, a history of accounts and distributed licenses, originally for my firm's reporting tasks. It then merges the data present in an archive excel file and data that come form current snapshot of the tenant.

Actually I should migrate the implementation from using MSOnline to Graph, as well. However, currently I have no way to test the Graph implementation since I haven't sufficient privileges. I need to be granted for such task, Graph seems to be locked (even in read-only mode) for my user account (I hold only a restricted set of admin privileges on tenant).

[clyons1973]

Hey, looking to retrieve formulas from Excel that contain links to other excel files. When I pull back the cell formula property it only has the external link as [1]Sheet2!$D$8 as an example. Is it possible to extract the actual external file rather than [1]? I've got 10,000s of excel files that need checked as moving the files to a different location and those that contain external links will break.

[clyons1973]

When I open up Excel using the comobject method the cell formula property has the actual external link defined and not like [1] using importexcel. Also I can also use the LinkSources property of the workbook using the comobject method to retrieve the actual external link value

[dfinke]

@clyons1973 thanks for using the Excel module.

Good question. I have never tried that. The COM approach can yield more. When the sheet is live in Excel more things are "rendered."

Try the Open-ExcelPackage function, this gives you the XL object model. See if there is a LinkSources property on the Workbook and if it has data.

[clyons1973]

Unfortunately not. Will use the COM approach.

…

________________________________ From: Doug Finke ***@***.***> Sent: Sunday, April 14, 2024 12:54 PM To: dfinke/ImportExcel ***@***.***> Cc: clyons1973 ***@***.***>; Mention ***@***.***> Subject: Re: [dfinke/ImportExcel] feedback on ImportExcel (Discussion #1238) @clyons1973<https://github.com/clyons1973> thanks for using the Excel module. Good question. I have never tried that. The COM approach can yield more. When the sheet is live in Excel more things are "rendered." Try the Open-ExcelPackage function, this gives you the XL object model. See if there is a LinkSources property on the Workbook and if it has data. — Reply to this email directly, view it on GitHub<#1238 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGDS5TKEWWVN72E6JYZLEMDY5JU53AVCNFSM577RA2CKU5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TSMJQHA4DONA>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[googledrummer]

Like many others I'm using it to pull data out of SQL and that part is great. However one of the things I need to do is create a forecast chart based on the data and I'm not sure if the module supports that natively. So I've got two questions, does the module support that, and if not does it support .XLSM files? I've got a macro I've written that does what I need, but I can't seem to get data into a .XLSM file.

[dfinke]

Thanks @googledrummer. No native support for forecasting. Could you say a bit more about that and what you are forecasting?

I tried the xlsm. Import-Excel can read it but Excel fails. Not sure if that can be corrected for the Export.

Can you export to an xlsx. I have examples of creating VBA modules using the module. You can see them on the repo.

How complicated is the macro?

[jdenicola-pa]

I've done some things with XLSM files, but not with Import-Excel though. I was using C# and tried the ClosedXML library and had a lot of problems. ClosedXML is great, but this was taxing its limits. I ended up having to purchase a license for the Aspose Excel library which did work. It was rather complex in that I had to open a pretty complicated XLSM file and update a table of values and write it back out.

[googledrummer]

@dfinke We have a script that polls a list of servers and gets the free space available on the drives and throws them in the DB. So I'm taking the date and the free space and forecasting off of that, so nothing too crazy. I didn't realize the module could create VBA, I'll give that a look. The macro isn't too complicated I don't think. Just takes the data on the active sheet and forecasts out 6 months. It also renames the new sheet similar to the sheet the data came from.

@jdenicola-pa I feel like I'm not doing anything too crazy, I'll give ClosedXML a look. Thanks!

[dfinke]

@googledrummer All sounds doable if you want to use the PowerShell Exel module. You can do the math/forecast in PowerShell and drop the info into the sheet. Or calculate when you grab it from the DB and use Export-Excel to the new sheet.

When you export, use the -PassThru switch to get the underlying workbook back, and you can rename sheets, and do a ton of things to the workbook, sheets etc. It is the same object model the macros has access to.