troubleshooting Question

help on script

Avatar of mcrmg
mcrmg asked on
Microsoft ExcelPowershell
8 Comments1 Solution25 ViewsLast Modified:
Hi,

I have the following script that I got help from the EEs a few months ago. It has been working well until this month.

We receive many files monthly in TXT, CSV, XSL and XLSX. I use Powershell to combine them.

It is giving me an error  "excel cannot complete this task with available resources" on the last step. It does not show the error unless I open the final combined file.

It is difficult for me to provide the files because I do not think it is files related. Here are the steps I have done:

There are over 800 files in the folder. I located the file that was stopped and removed. The error stopped on another file. When I removed other files and kept the "supposed error files", the code went well. There is no pattern.
################################################################

$downloadFolder = 'June 2021 Downloads'

################################################################

$networkSource = "\\192.168.1.10\shared\${downloadFolder}\"

$sourceFolder = "C:\Source\"
$outputFolder = "C:\OUTPUT\"


Get-ChildItem -Path $sourceFolder -Include *.* -File -Recurse | foreach { $_.Delete()}

Copy-Item -Path "${networkSource}*LPE*.*" -Destination $sourceFolder



# del existing files
$outfile = "${outputFolder}combineFile.csv"
if(Test-path $outfile){
    Remove-Item $outfile -Confirm:$false
}






$Excel = New-Object -ComObject Excel.Application 
#---------only a few XLSX files------------------------------------------------------------------------
# convert XLSX into CSV
$filesXLSX = Get-ChildItem "${sourceFolder}" -Filter *.xlsx
foreach($file in ($filesXLSX)) {
    $newname = $file.FullName -replace '\.xlsx$', '.csv'
    $Workbook = $Excel.Workbooks.Open($file.FullName) 


    $Workbook.SaveAs($newname,6)
    $Workbook.Close($false)
}
$Excel.Quit()

# here will add CSV files (Converted from previous step into outfile)
$counter = 1
$files = Get-ChildItem "${sourceFolder}" -Filter *.csv
# Check first line if "Trans ID" exists, skip lines
    foreach ($f in $files){
        $firstLine = Get-Content -Path $f.FullName -TotalCount 1
        $secondLine = Get-Content -Path $f.FullName -TotalCount 2

            (Get-Content $f.FullName | Add-Content $outfile)
            echo "File opened CSV: $counter $f"

        $counter++

    }
#---------------------------------------------------------------------------------

$files = Get-ChildItem "${sourceFolder}" -Filter *.txt
# for some reason, the replaced file ("tab" to "," cannot be combined correctly RIGHT AFTER convertion, so we do the convertion first then combine
$counter = 1
foreach ($f in $files){
    if(Get-Content $f.FullName -First 1 | Select-String -Pattern "\t")
    {
        # replace "tab" with ","
        (Get-Content $f.FullName) -replace "\t", "," | Out-File $f.FullName

        ECHO "========== tab delimited file found ========== "  $f.Name 
    }


    (Get-Content $f.FullName | Add-Content $outfile) #########################################################

    echo "File opened TXT: $counter $f"

    $counter++
}






explorer.exe "${outputFolder}"

Please help. thank you
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros