In diesem Abschnitt findest du Information zu
Ein Beispielhaftes Skirpt zum Umformatieren einer Datei und dem Upload über WINSCP
Die Software WINSCP (hier zum Download) hat eine Schnittstelle zu Powershell.
Das heißt, dass man eine mögliche CSV-Datei Formatierung und den Upload auch über ein Skript laufen lassen kann und damit letztendlich automatisieren.
Im folgenden findest Du beispielhaft ein Skript welches Du als Grundlage nutzen könntest.
Hast Du Fragen, kannst Du uns jederzeit kontaktieren.
Bei Bedarf entwicklet und Easy Connect Team das Skript für Dich.
# Set Execution Policy
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process -Force
# Define file paths
$input_file_path = 'C:\Users\FlorianBaar\XXX.csv'
$output_file_path = 'C:\Users\FlorianBaar\XXX.csv'
$winscp_exe_path = 'C:\Users\FlorianBaar\AppData\Local\Programs\WinSCP\WinSCP.exe'
$winscp_log_path = 'C:\Users\FlorianBaar\XXX\WinSCP.log'
# WinSCP connection details
$sftp_server = '31.24.145.67'
$sftp_port = '2022'
$sftp_user = 'bXXXXXXXXXXXXXX' # Username from BEARING X
$sftp_password = 'XXXXXXXXXXXXXXXXX' # Password from BEARING X
$remote_path = '/Stockupload - BEARING X.csv' # Replace with the actual remote path
# Import the CSV data
Write-Output "Importing CSV data from: $input_file_path"
$input_data = Import-Csv -Delimiter ';' -Path $input_file_path
# Process the data and select the required columns
Write-Output "Processing CSV data"
$output_data = $input_data | Select-Object @{
Name='buySell';Expression={'S'}
}, @{
Name='OrderType';Expression={'Order'}
}, @{
Name='quantity';Expression={$_.'Verfügbarer_Bestand'}
}, @{
Name='pricePerUnit';Expression={$_.'Nettobetrag'}
}, @{
Name='minimumPartialQuantity';Expression={$_.'Mindestabnahme'}
}, @{
Name='itemNumber';Expression={$_.'Artikelbez1'}
}, @{
Name='brand';Expression={$_.'Hersteller'}
}, @{
Name='location';Expression={'Germany'}
}, @{
Name='productionYear';Expression={''}
}, @{
Name='origin';Expression={$_.'Ursprungsland'}
}, @{
Name='articleNumber';Expression={''}
}, @{
Name='fixedPrice';Expression={''}
}
# Convert the processed data to CSV format with semicolon delimiter
Write-Output "Converting processed data to CSV format"
$csv_data = $output_data | ConvertTo-Csv -NoTypeInformation -Delimiter ';' | % { $_ -replace '"', '' }
# Write the CSV data to the output file without BOM
Write-Output "Writing CSV data to: $output_file_path"
$utf8NoBomEncoding = New-Object System.Text.UTF8Encoding($false)
[System.IO.File]::WriteAllText($output_file_path, $csv_data -join [Environment]::NewLine, $utf8NoBomEncoding)
# Create WinSCP script content with proper variable interpolation
$winscp_script = @"
open sftp://${sftp_user}:${sftp_password}@${sftp_server}:${sftp_port}
option transfer ascii
put `"$output_file_path`" `"$remote_path`"
exit
"@
# Save the WinSCP script to a temporary file
$temp_winscp_script_path = [System.IO.Path]::GetTempFileName() + ".txt"
Set-Content -Path $temp_winscp_script_path -Value $winscp_script
# Upload the CSV file using WinSCP with logging enabled
Write-Output "Uploading CSV data to SFTP server"
$winscp_command = "`"$winscp_exe_path`" /log=`"$winscp_log_path`" /script=`"$temp_winscp_script_path`""
Start-Process -FilePath $winscp_exe_path -ArgumentList "/log=$winscp_log_path /script=$temp_winscp_script_path" -NoNewWindow -Wait
# Clean up temporary WinSCP script file
Remove-Item -Path $temp_winscp_script_path
# Output completion message
Write-Output 'Processing and upload complete.'