Início / computer / Perguntas / 1840442
Accepted
Maslie
Asked: 2024-04-26 16:56:01 +0800 CST

Exportando dados do Excel com PowerShell para Robocopy

  • 772

Tenho em mãos uma planilha excel de 300 linhas com caminho de origem e caminho de destino para robocopy, para evitar copiar e colar todo o caminho em um arquivo .bat tento fazer este script powershell:

# Import the Excel module
Import-Module ImportExcel

# Path to the Excel file
$excelPath = 'classeur.xlsx'

# Import data from the Excel file and check for null values
$data = Import-Excel -Path $excelPath
if ($data -eq $null) {
    Write-Host "No data was imported from the file. Please check if the file is empty."
    return
}

# Display imported data for verification
Write-Host "Imported data preview:"
$data | Format-Table -AutoSize

# File to store the source and destination paths
$outputFile = 'robocopy.txt'
if (Test-Path $outputFile) {
    Remove-Item $outputFile
}

# Iterate over each row of data
foreach ($row in $data) {
    $source = $row.Source
    $destination = $row.Destination

    # Check if either source or destination is empty
    if ([string]::IsNullOrWhiteSpace($source) -or [string]::IsNullOrWhiteSpace($destination)) {
        Write-Host "Empty source or destination found, skipping..."
    } else {
        # Format the line to write to the file
        $lineToWrite = "Source: `"$source`" - Destination: `"$destination`""
        Write-Host "Writing to file: $lineToWrite"
        $lineToWrite | Out-File -FilePath $outputFile -Append -Encoding UTF8
    }
}

Write-Host "All lines processed. Paths are stored in $outputFile"

É claro que faço uma cópia do arquivo Excel e o reconstruo para simplificar a leitura do script

Um exemplo de construção

Fonte Destino
Caminho do arquivo de origem Filapata de destino
Caminho do arquivo de origem Filapata de destino

O resultado que espero é o seguinte

SET _source1="Filepath to source"

SET  _destsge1="Filepath to destination"

SET _source2="Filepath to source"

SET  _destsge2="Filepath to destination"

ETC...

O resultado que estou tendo atualmente

Empty source or destination found, skipping...
Empty source or destination found, skipping...
Empty source or destination found, skipping...
Empty source or destination found, skipping...

O que eu verifiquei:

  • Que o excel foi lido corretamente pelo powershell, que é o caso.
  • Minha PSVersion que é 5.1.22621.2506
  • Execute o script como administrador.
  • Versão Excel: Versão 2403 (Compilação 17425.20146)

Quero ressaltar que o arquivo txt e o arquivo excel estão na mesma pasta que contém o script.

Alguém pode saber por que o script não consegue ler os dados. Não consigo encontrar nada específico para esse problema:

Fonte que uso: https://www.sharepointdiary.com/2021/03/import-excel-file-in-powershell.html#:~:text=To%20import%20an%20Excel%20file%2C%20follow%20these %20etapas%3A,o%20Excel%20arquivo.%20For%20example%3A%20Import-Excel%20-Path%20C%3APathToExcelFile.xlsx

https://www.it-connect.fr/comment-manipuler-des-fichiers-excel-avec-powershell/

microsoft-excel

1 respostas

  1. Best Answer

    Com a ajuda do @MT1, o problema era o uso do elseif.

    Em vez disso, é melhor usar dois if para esta configuração.

    Aqui está o script atualizado que permite obter dados do Excel e preparar o comando robocopy de acordo. observe que você deve inserir manualmente o arquivo de log no Robocopy.txt

    A primeira linha do seu arquivo Excel deve ter origem e destino tem cabeçalho para que o script obtenha as informações

    # Import the Excel module
Import-Module ImportExcel

# Path to the Excel file
$excelPath = 'classeur.xlsx'

# Import data from the Excel file and check for null values
$data = Import-Excel -Path $excelPath
if ($data -eq $null) {
    Write-Host "No data was imported from the file. Please check if the file is empty."
    return
}

# Display imported data for verification
Write-Host "Imported data preview:"
$data | Format-Table -AutoSize

# File to store the source and destination paths and robocopy commands
$outputFile = 'robocopy.txt'
if (Test-Path $outputFile) {
    Remove-Item $outputFile
}

# Writing initial setup commands to the file
$initialCommands = @(
    "chcp 1252 > nul",
    "@ECHO OFF",
    "SETLOCAL"
    "SET _LogFile=Robocopy.log"
    "SET _options=/E /XX /SEC /COPYALL /PURGE /R:2 /W:5 /LOG:%_LogFile% /TEE /V"
    "SET _options2=/E /XX /SEC /COPYALL /PURGE /R:2 /W:5 /LOG+:%_LogFile% /TEE /V"
)
$initialCommands | Out-File -FilePath $outputFile -Encoding UTF8

# Initialize a counter to create numbered variable names
$counter = 1

# Array to store ROBOCOPY commands
$robocopyCommands = @()

foreach ($row in $data) {
    $source = $row.Source
    $destination = $row.Destination

    # Check if the source is empty
    if ([string]::IsNullOrWhiteSpace($source)) {
        Write-Host "Empty source found, skipping..."
        continue
    }

    # Check if the destination is empty
    if ([string]::IsNullOrWhiteSpace($destination)) {
        Write-Host "Empty destination found, skipping..."
        continue
    }

    # Format the line to write to the file with SET command, including quotes around the paths
    $sourceLine = "SET `_source$counter=`"$source`""
    $destinationLine = "SET `_destst$counter=`"$destination`""
    $sourceLine | Out-File -FilePath $outputFile -Append -Encoding UTF8
    $destinationLine | Out-File -FilePath $outputFile -Append -Encoding UTF8

    # Determine which options variable to use
    $optionsVar = if ($counter -eq 1) { "_options1" } else { "_options2" } #Option 1 erase the content of the log file while option 2 write next to it
    # Prepare the ROBOCOPY command using the determined options variable and store it in the array
    $robocopyCommands += "ROBOCOPY %_source$counter% %_destst$counter% %$optionsVar%"

    # Increment the counter for the next pair of variables
    $counter++
}

# Append ROBOCOPY commands to the file after all SET commands
$robocopyCommands | Out-File -FilePath $outputFile -Append -Encoding UTF8

Write-Host "All lines processed. Paths and robocopy commands are stored in $outputFile"
    • 1

relate perguntas