Shell Programming and Scripting

This script doesn't look right.

First of all, it's got a password kludged in with expect. Having to use expect to do it is a subtle hint, in mile-high neon letters, that you really shouldn't be doing this. ssh, sftp, scp, su, and sudo all require a tty for passwords as a security precaution, not because they're badly written. They did this because because stored passwords are an extremely bad idea. ssh also provided a safe alternative, shared keys.

If you use shared keys, you won't have to kludge anything with expect. Just having the right files in the right places on the client and server will allow sftp username@host to connect without prompting. There's articles all over the net for passwordless ssh/scp/sftp (all the same protocol connecting the same way). It'd make your code much less convoluted, too.

I also see what you mean about having the files in the same directory, now. Your script is downloading these files, it's not fixed data. It should be putting them in a separate directory somewhere, because you don't want the script to have write-access to the directory it's running itself from. It'd be capable of accidentally deleting itself if something really messed up.

Also, just renaming xls to csv doesn't convert the data from xls to csv any more than putting a book in a different cover changes the contents. The usual way to convert excel to csv usually involves perl scripts. I'll see if I can find the right module.

Code:

#!/bin/sh

sftp username@host <<EOF
cd /remote/path/
lcd /local/path/
mget GET_FILE*
EOF

# This doesn't do what you think it does.  Renaming a file doesn't change
# the contents, it's still an Excel file!
for I in GET_FILE.*
do
        mv $I GET_FILE_123${I#GET_FILE}.csv
done

# SEND AN EMAIL TO THE PARTICULAR USER THAT FILE HAS BEEN MOVED #TO THE /usr/sap/NXA/COMM/interface/FICO/. PLZ SEE THE DIRECTORY.
# Not sure these filenames are quite right.  And isn't there actually more than one?
uuencode GET_FILE_123.CSV GET_FILE_123.CSV.MAIL | mail -s "GET_FILE_123.CSV" VENKAT@yahoo.com

---------- Post updated at 10:58 AM ---------- Previous update was at 10:49 AM ----------

The obvious thing to search for was xls2csv, and here it is.

I understand that expect is a patched solution with no.of alternatives to avoid a stored passwords.
Since you are throwing a constructive answers for my problem, could you plz make me to stand out of the crowde while writing the complete code in reply using the shared keys (SSH)?
As you are aware, the previous script helps you in giving you the complete file name, Host names, passwords (if required in ssh - I beleive this is a password less) and the exact requirements as mentioned the pseudo code in comments.
I am really nagging myself and in confusion mode to take a right decision.

Also, as conversion of the extension is one of my requirement, i need it in .csv why because SAP BI accepts .CSV only ofcourse accepts others too but risky procedure.

Question: Let us assume, if we dont mention the password in the SSH (script) then how could it be login to FTP Server and moves it to the application server. Hope it make you a clear. i am only looking for the complete code at the moment instead. Thank you.

That's the beauty of it, it is the complete code. You don't have to tell sftp, scp, and ssh to use keys, they check for you. Just having the right files in the right places is enough.

On the client: $ ssh-keygen -t rsa and just keep hitting enter, letting it save in the default location of ~/.ssh/id_rsa and giving it no password.

Then ssh-copy-id username@server. It will prompt you for a password, log into the server, and copy the necessary bits of information into that users ~/.ssh/ directory on the server.

ssh/sftp/scp username@host should now go through without prompting whenever you're logged into that account.

If the program assumes the files are comma-seperated values, it will either crash, give up, or produce garbage when you feed it that excel spreadsheet. Renaming xls to csv doesn't make the binary data inside become comma-separated text, the same way that renaming .jpg to .mpg doesn't turn a still picture into an animated feature.

You'll have to use something more sophisticated, something that understands xls files and can output comma-separated text, such as the program I linked you.

I am now confusing that understanding of the solution. is it possible for you to drop your contact number? i really need to speak to you to figure out the stuff ASAP. Thanks

I've given you all the details I have to give. I've given you all the resources needed to finish. I don't know where you're confused. No. My cell phone connection stinks anyway. It'd help if you responded to this topic more often.

First i should say you very sorry to keep on troubling you. Because this is the real time application i am here working on. i am scrwed up here at this step.

Ok now i go by simple questions. I am expecting the 100% practicle code instead of theriotical explanation:

Q1) #!/bin/sh

sftp FICO@share.dteenergy.com<<EOF
cd /remote/path/ (as i written in previous code -
"
# CONNECT TO FTP SERVER.
Set timeout -1
spawn $env(SHELL)
match_max 100000
send -- "sftp FICO@share.dteenergy.com\r"
expect -exact "Connecting to share.dteenergy.com...\r
FICO@share.dteenergy.com's password: "
send -- "ABC123\r"
expect -exact "\r
sftp> "
"
I am also not sure that i have a remote path. i asked my team no.of times for the same. They said like below:
(The above FTP server doesnt have a remote path (/remote/path/) because the above code uses the user ID and PW along with the Host name and log into it. after you login, you will have only one file. that is a "FLAT FILE" (test file). so finally we will get(move) that file from that remote server to the local path.
-----------------------------------------------------------------------
lcd /usr/sap/NXA/COMM/interface/FICO/
mget GET_FILE*
EOF

Q2) plz suggest me how could i detect the remote path if you think that we can find the same after log in to FTP server? means step by step process. FYI, this is a tool on windows O.S. and file here is with .xls extension (GET_FILE.xls)

Q3) After we find the remote path, the below code is enough to go head and push the file (GET_FILEP) from remote path to local path. FYI, local path is on Linux O.S.

Code:
sftp FICO@share.dteenergy.com <<EOF
cd " " (remote path)
lcd /usr/sap/NXA/COMM/interface/FICO/ (local path)
mget GET_FILE*
EOF

Q4) since i should convert the flat file (GET_FILE.xls) extension into .csv, could you plz help me in sending the extensions conversion code and how i should club these both (moves the file and conversion) together to get the proper out put?

I feel it is not only help but more than that. i am certainly here to help you in return if i can. Thank you so much again. venkat

what is all the expect and spawn stuff for?

The code for sftp with shared keys is easy.

Code:

 sftp username@host <<EOF
stuff
EOF

no expect madness required. That's the whole point. Then don't use one. I was just trying to be complete. Do you actually need a remote path? Can you just sftp into username@server then MGET *.xls? Have you actually tried? Try just typing it into a terminal and see what happens.

I linked the code to use for that several posts ago. You have been reading the things I linked to you, right?