ImportExcel reading in old information

[wolfman9195]

I have a project that I've been assigned where I get a CSV delivered daily to a folder. I have to manipulate the data in the CSV to match some very specific formatting and then upload the file to an SFTP server. I am able to use PowerShell to manipulate most of the data. However, there are a couple of fields that needed more than I could get Powershell to do on its own, so I used the Import-Excel module to pull the CSV into a worksheet of a Excel workbook. A separate worksheet in that workbook manipulates the data that I've imported. That all works wonderfully. However, when I use the Import-Excel module to pull the new spreadsheet into memory to be output to CSV, it only works once. When the data changes in the underlying CSV and I get to the final step to read and export the Excel spreadsheet to CSV, it reads the old values...not what is currently in the spreadsheet. It is almost as if the information is cached somewhere. If I change the name of the worksheet being exported and update the Powershell accordingly, it reads the updated values once, and then I'm in the same boat again. Any suggestions appreciated!

[dfinke]

Sounds strange. Would need simple data and scripts that repro the issue.

[wolfman9195]

Thank you for responding! I have attached a zip file with an example of the problem. Within the zip file are the following:

• student.csv (the original data as supplied to me every morning...I have replaced all of the numbers and names with random numbers and names)
• manipulate.ps1 (the powershell script that imports student.csv, manipulates the data and saves it into student-corrected.csv, pushes student-corrected.csv into the Data tab of student-completed.xlsx, and then imports student-completed.xlsx and exports the Export tab to export.csv.
• student-completed.xlsx (the Excel sheet with the necessary formulas to manipulate the data)

The script creates student-corrected.csv and export.csv each time, so you can delete them after each run of the powershell script.

To reproduce the issue, run manipulate.ps1. You'll notice it creates a perfectly formed export.csv. Delete export.csv and student-corrected.csv. Then, edit something in the student.csv file (ie, change the name of the first student). Run the powershell script again. You'll notice that the export.csv still contains the original name of the first student. However, the student-corrected.csv and both tabs of the student-completed.xlsx file are all correctly showing the updated name. In troubleshooting, I have run the import in the final command to screen instead of outputting to CSV, so I know that the data issue rears its head on the Import-Excel rather than on the Export-Csv portion of that command.

I know how busy you must be, so I appreciate any time you can spend on finding where I've gone wrong.

Thank you!

export_issue.zip

[dfinke]

I need a simple script that reproduces the error.

I don't have the bandwidth to review that script.

[wolfman9195]

My most humble apologies. Attached is a four line script, a csv with only two columns and a handful of rows, and a spreadsheet that does a simple concatenate formula on the two fields. While continuing to troubleshoot, I've discovered the following.

The workflow for my powershell script is source.csv -> workbook.xlsx (Data worksheet) -> worksheet.xlsx (Export worksheet) -> export.csv. Data from source.csv is imported (using Import-Csv and Export-Excel) into the Data worksheet of workbook.xlsx and lives in the cells as a value. All of the cells in the Export worksheet of workbook.xlsx house formulas that display data or manipulate data from the Data worksheet. I then use Import-Excel and Export-Csv to export the Export worksheet to export.csv.

• If I run Import-Excel against the Data worksheet of workbook.xlsx (where everything lives as a value) and output it to export-data.csv, every time I get a result that exactly matches the current data in source.csv and workbook.xlsx.
• If I run Import-Excel against the Export worksheet of workbook.xlsx (where everything lives as a formula), and output it to export-export.csv, I only get a result that exactly matches the data in source.csv and workbook.xlsx the first time I run the script. Any subsequent changes to source.csv that are pushed into Excel by the script do not show up in export-export.csv.

You can reproduce the behavior by:

1. Unzip the file into a folder.
2. Open a powershell prompt and change directory to the folder you unzipped to. Run manipulate.ps1 once. Observe that it produces an export-data.csv and an export-export.csv that each match the source.csv and both worksheets of workbook.xlsx.
3. Change any data in source.csv, save the changes, and run manipulate.ps1 again.
4. Observe the following: workbook.xlsx file will reflect the changes, export-data.csv file will reflect the changes, and export-export.csv will not reflect the changes you made in source.csv.
5. You can also skip the output to the csv and just run Import-Excel -Path .\workbook.xlsx -WorksheetName Data and Import-Excel -Path .\workbook.xlsx -WorksheetName Export to observe that the changes to the Export worksheet aren't being read by Import-Excel.

It seems Import-Excel can read formulas because it does so the first time you run the script, but that it maybe stores those values somewhere that it reads going forward. Is there a variable or session I need to be clearing before running each time?

Thank you for not only your help but also the incredible development of this module!
export_issue.zip