Upload file from desktop to UNIX server via putty

I have a requirement of following -

  1. Transfer excel spreadsheet from desktop to unix server
  2. Open the spreadsheet and read the sql row by row at a time
  3. Run each sql on database using unix and then send output back to spreadsheet that was uploaded earlier and then transfer file back to desktop results.

I'm dummy and need help

Hi Analabhatla,

Welcome to the forum.
It would be great if you could answer the following questions.

1, Is this task a one time activity or you want to automate this?
2, Do you know whether your proxy/network/firewall supports ftp/sftp/both/none?
3, Is there any restriction/Policy which blocks uploading data local to remote?
4, Do you have at least basic Unix OS experience?

If everything above works, then, Is it allowed if you upload plaintext file (csv) instead of xls to the server? that would save lot of your work.

1, Is this task a one time activity or you want to automate this? No ; its an Automation requirement
2, Do you know whether your proxy/network/firewall supports ftp/sftp/both/none? - Yes ftp and sftp
3, Is there any restriction/Policy which blocks uploading data local to remote? No
4, Do you have at least basic Unix OS experience? - yes but not having the scripting experience.

I tried scp and ftp host name of windows (using cmd>>ipconfig info) but none of them have worked.

Did you try other way? Try connecting from Unix box.

i'm able to transfer file from unix server to desktop by using ftp from cmd. the viceversa is not working.

I tried ftp / scp method from unix server the following way and nothing has worked out. Looks like i'm missing something could you please guide me?

  1. logged in to putty
  2. then tried ftp <<hostname of desktop >> (got the host name from cmd >>ipconfig /all)

it throws error saying as the hostname not known .

It's not always necessary that vice versa works. Check whether the ports are open at desktop end.

telnet <HOST> <PORT>

default port for ftp and sftp is 21 and 22

I took the hostname of the desktop from cmd and run the command on Unix and it says the hostname not found. any other suggestions?

Could we have an option to cut the columns from the .csv file once uploaded in to unix? I need the last column in the excel sheet to run against the database.

So you mean to say, get works but put doesn't?

Do you have permission to write on the remote directory?

You can save-as your excel file as 'csv' and upload that. In MS-Excel, file->save-as and select CSV in filetype down list.

Later on the unix server, you can extract the last column.

Thanks CLX. I'm able to upload the .csv file in to unix server directory and then run the cut command to extract last column. However i need your help with the following ? sorry for troubling .

  1. I'm able to ftp the file from desktop to Unix directory via windows cmd. Will there be any way to perform this FTP operation via UNIX directly? I meant logging in to putty and then ftp to desktop location and then get the file to unix directory. This way it will help me to automate the uploading process for other csv files.

  2. The csv file has the 4 fields and sql's that i need to run on database are present in 3rd field. I need help in writing the command / script which reads the sql in 3rd field for all the records and run it against the database and fill the result back in to last field of the file?

Sorry but cant help you in this, this is something you need to contact your network/admin to check at firewall side whether the relative port is open or not. you need to provide the windows hostname/ipaddress to them.

Does this mean you want to extract third filed from CSV? If yes,
do something like,

while IFS=, read first second third forth
do
 # do stuffs here with any field you want
 # with $first, $second, $third and $forth variables
done < your_csv_file

automating a ftp job from your local system to a unix system... hmm... unless your .csv's are uniquely timestamped it might not work in your favor. you can always cd into the folder from command line (windows) and then ftp to the remote server. use the options:

prompt
mput *

or use a ftp tool like filezilla (i usually suggest this to any users who are non-tech savvy as it has a gui interface) and drag and drop.

this is untested as I just wrote it, but more or less should work from a unix system but you will have to adjust it for your needs:

#! /usr/bin/ksh

ftp=/usr/bin/ftp

PD=<local directory> 
TS=`/usr/bin/date +%Y%m%d`

$ftp -inv <host> <<__EOFTP > path/to/log/ftp.$TS.log 2>&1
user <username> <password>
lcd $PD
cd <path to directory>
ascii <or use whichever format you want
put `<filename>`
bye
__EOFTP

but keep in mind that is only for a specific filename. you would have to use

 prompt and mput

if you are importing or getting all files from that folder.

be sure to try this out manually first line by line prior to making this a consistent job.

how to over come the mv:missing destination file operand in ubuntu

mv: missing destination file operand after `sumo-0.12.3/usr/local/src'
Try `mv --help' for more information.

I know this is a little off topic but i use the ssh app form ssh.com, it allows for sftp from a windows screen , very handy