Issue with Powershell Excel attempting to cast .csv Values

Question

At this point, I believe it may be a file I/O issue.

While utilizing a Powershell script invoking Excel methods to go through a .csv file from a website, powershell is attempting to cast placeholders for data that is too long for a cell "#######" instead of the date and time contained within the 'cell' (search engines may need 'pound sign' or 'hashtag' to reach this result).

Below is the offending portion of the script.

[DateTime]$S = $sheet.Cells.Item($rowS+$i,$colS).text
[DateTime]$G = $sheet.Cells.Item($rowG+$i,$colG).text
[DateTime]$A = $sheet.Cells.Item($rowA+$i,$colSWScan).text

The data should exist as MM/DD/YYYY HH:MM, but is being read by Powershell/PSExcelModule as #######, which is what is displayed with the Excel GUI when opening the file.

This is only a portion of what the entire script does. Any suggestions on how to resolve the error while maintaining usage of PSExcel-Module would be most helpful.

Stackoverflow seems to have an issue with me posting the verbose error message, and this is my first post. Let me know if that would be helfpul with troubleshooting.

Edit for comment #1:

# Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application         
# Open the file
$workbook = $objExcel.Workbooks.Open($file)                 
# Activate the first worksheet
$sheet = $workbook.Worksheets.Item($sheetName)              
$objExcel.Visible=$false

After getting my head out of 'Excelland', I realized it may be easier to re-write the script to utilize the .csv organization (the original imported file for the script was a .xlsx), but I am admittedly unfamiliar with .csv scripting. However, the original question still stands while I re-write the code as I may need to switch back to .xlsx imported documents. Thank you for the suggestion J E Carter II.

Answer:

$objExcel.Cells.EntireColumn.AutoFit()

Credit to J E Carter II

How are you opening the csv file? CSV is just text, so you might not need to open it in excel, and it might be faster to process as text as well. — J E Carter II
– J E Carter II, Commented Jan 11, 2018 at 21:21
Ok, if it's going to automate the OLE / COM interface for Excel, you can probably do a select all and autoresize the columns so the values displayed are correct and can then be scraped properly. CSV is just text, just comma separated values. Quotes will appear around values that contain commas, and a newline CRLF marks the end of a row. Much easier to handle with less overhead and since you're not tapping into OLE / COM, much faster. — J E Carter II
– J E Carter II, Commented Jan 11, 2018 at 21:45
The issue is that the input file is a format coming from an automated database output, and the task is to run the script without needing to modify it's contents prior to running. — LifeOfThePotty
– LifeOfThePotty, Commented Jan 11, 2018 at 21:47

J E Carter II · Accepted Answer · 2018-01-11 21:51:42Z

0

When you open an excel file as an object under windows, you're launching excel.

You might be able to add the following commands to your excel object handle to get the data to represent correctly.

$objExcel.Cells.Select
$objExcel.Cells.EntireColumn.AutoFit

Do this before getting values from cells. If that doesn't work, let me know and I can find some csv handling examples.

Those might work better on the $workbook object. I can't remember which is implicit when recording a macro (which is how I got those).

It's also possible you may need to precede those two lines with something like

 $workbook.Sheets("sheetname").Select

edited Jan 11, 2018 at 21:51

answered Jan 11, 2018 at 21:50

J E Carter II

1,5061 gold badge22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LifeOfThePotty Over a year ago

Thank you. After playing around with it and reading the related man file, I was able to find what worked with my particular input. Just had to make sure it had the "()" at the end of it.

J E Carter II Over a year ago

Glad I could help.

Collectives™ on Stack Overflow

Issue with Powershell Excel attempting to cast .csv Values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related