Connecting to the SFTP server

[shaik mahammed ziaullah]

Hi Team,

     Can any one please provide the shell script to connect to the sftp server with below requirement-

1) Connect to sftp server

2) Convert the excel into csv 

3) Copying the data from csv file

4) Insert the csv file data into the DB table.

Thanks & Regards,

Shaikh

[Daryl Brown]

You're referring to a "shell script", so I assume that means you'll be running this job on a unix agent?

1 - Connect to sftp server -- presumably you have an agent installed on the sftp server in question?  if so, you would just configure your job to run against that particular agent.

2 - Convert the excel into csv -- this isn't core Automic functionality; you'd probably need to google for a way to do this.  Something like this could work: https://stackoverflow.com/questions/10557360/convert-xlsx-to-csv-in-linux-with-command-line

3 - Copying the data from csv file -- you'll need to be more explicit about what you mean by this.  Copy what data?  Copy it where?  

4 - Insert the csv file data into the DB table -- Presumably you have the DB login info you need here already?  You didn't mention what kind of DB you're dealing with or what DB tools you'd have access to, so this is not something I can answer.  (e.g., is sqlldr an option here?  Or are we just doing a single row insert here?  I would look to your DBA for some pointers here.) 

[shaik mahammed ziaullah]

Hi @Daryl Brown,

        We don't have any Agent installed in the SFTP Server.

this is the user requirement to do the Automation. Is there anyway to connect to the SFTP server without agent installed ?

Thanks & Regards,

Shaikh

[Daryl Brown]

Okay, I think you need to provide more clarification here, then.

Is the idea here that you're downloading a file from a remote site via sftp, and then you need to import it?  If so, does that mean you're looking for an sftp script to do the download?
Or has the file already been downloaded and is sitting on your local sftp server (where you don't have an agent installed)?  If so, then is the file on the sftp server accessible from any of the servers where you do have any agent installed?

[Eric Lontz]

Hi,

I know you asked for a script to do this, but... I think there are several pieces of Automic you could leverage instead of a shell script.

For Step 1. I would use the IG or RA FTP Agent so you don't need an Agent on the source server. Then setup a FTP JOBS to download the file.
For Step 2, I would recommend a UNIX JOBS using the information Daryl provided to convert your file
For Step 3, I am taking this to be getting the data ready for read into a DB, so I would be transferring this file to a place on the DB server.
For Step 4, I would use a SQL Agent with SQL JOBS to load the CSV file into the DB, like with a MS SQL SSIS Package 

I understand this isn't very specific but it would break apart a single step into multiple steps which are easier to troubleshoot when thing invariably go wrong.