主页 / computer / 问题 / 1840442
Accepted
Maslie
Asked: 2024-04-26 16:56:01 +0800 CST

使用powershell导出excel数据进行robocopy

  • 772

我手上有一个 300 行的 excel 表，其中包含用于 robocopy 的源路径和目标路径，为了避免将所有路径复制粘贴到 .bat 文件上，我尝试制作此 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"

当然，我复制了 excel 文件并重新构建它，以方便脚本读取

构造示例

来源 目的地
源文件路径 目的地 filapath
源文件路径 目的地 filapath

我期待的结果如下

SET _source1="Filepath to source"

SET  _destsge1="Filepath to destination"

SET _source2="Filepath to source"

SET  _destsge2="Filepath to destination"

ETC...

我目前得到的结果

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

我检查了什么：

  • powershell正确读取了excel，就是这样。
  • 我的PS版本是5.1.22621.2506
  • 以管理员身份执行脚本。
  • Excel 版本：版本 2403（内部版本 17425.20146）

我想指出的是，txt 文件和 excel 文件位于具有脚本的同一文件夹中。

有人可以知道为什么脚本无法读取数据我找不到任何特定于此问题的内容：

我使用的来源：https://www.sharepointdiary.com/2021/03/import-excel-file-in-powershell.html# :~:text=To%20import%20an%20Excel%20file%2C%20follow%20these %20steps%3A，%20Excel%20文件。%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 个回答

  1. Best Answer

    在@MT1的帮助下，问题是elseif的使用。

    相反，最好使用两个 if 来进行此配置。

    这里更新的脚本允许从 Excel 获取数据并相应地准备 robocopy 命令。请注意，您必须手动将日志文件输入到 Robocopy.txt

    Excel 文件的第一行必须有源，目标有标题，以便脚本获取信息

    # 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

相关问题