0

How do you put multiple files in sftp as .csv when originally you got it as .xlsx. Manual conversion is a tedious work.

This is my script

@echo off

"C:\Program Files (x86)\WinSCP\winscp.com" /ini=nul /log=E:\automation\Logs\Winscp.log /command "open sftp://xxxx:@sftplink  -hostkey=""xxxxxxxxxxxxxxx""" "put E:\automation\Output\* /folder/output/" "exit"
Squashman
  • 13,649
  • 5
  • 27
  • 36
Zilch
  • 15
  • 7
  • 2
    You would need some conversion software to export the Excel file to csv before you upload it through your SFTP connection. – Squashman Dec 23 '19 at 19:09
  • can i do it without using any conversion software? – Zilch Dec 23 '19 at 19:32
  • 1
    Something has to do it. SFTP and FTP are protocols. They don't do data manipulation. You can most certainly do it without any third party tools as Vbscript and Powershell can both accomplish this task. That is where I would start your research. – Squashman Dec 23 '19 at 19:49
  • 1
    There are some options here that should be helpful: [link](https://stackoverflow.com/questions/5256733/convert-xlsx-file-to-csv-using-batch) – vhoang Dec 23 '19 at 20:10
  • i tried putting `*.csv` at the put command and it worked `"put E:\Output\* /folder/output/*.csv"` – Zilch Jan 08 '20 at 14:23

2 Answers2

1

From a traditional batch script only file, you aren't going to achieve this without some VB script or powershell script.

With that said you can do this in cmd (tapping into powershell):

PowerShell.exe install-module Import-Excel -scope CurrentUser -force
PowerShell.exe import-excel "C:\filepathHere" | Export-csv "C:\ExportPathHere" -Notypeinformation

The reason this works is the powershell module leverages a .dll file to actually read the excel file into a powershell object. "Export-CSV" is a native powershell command to export objects as CSV.

Using this method, you will not need any 3rd party tools.

shadow2020
  • 1,315
  • 1
  • 8
  • 30
-3

It took me a while to figure it out but here's what i did

I tried putting *.csv at the put command and it worked

"put E:\Output* /folder/output/*.csv"

Zilch
  • 15
  • 7
  • 1
    just changing the extension doesn't convert the file's content to another format. Renaming a `.jpg` to `.txt` doesn't magically do OCR, reaming `.xlsx` to .`csv` doesn't magically convert a table to text. – Stephan Jan 08 '20 at 15:38