FTP report

FTP report

by Matt Smith -
Number of replies: 1

Hi all, 

Is it possible for a report we have running using this block to be sent via FTP as a XLS, csv file to a local server. 

Or run the report and save it locally on the server as a csv XLS file? 

Thanks,
Matt 

Average of ratings: -
In reply to Matt Smith

Re: FTP report

by Kelly Thomas -

Here's how we do something similar. I'm using Windows with IIS and MySQL database. The default prefix we use is "mdl_".

 

1. On the server should be a folder "C:\Program Files\MySQL\MySQL Server 5.5\bin"

2. Take the SQL commands you have written and copy-and-paste them to a text file in this folder called 'XPORT.SQL' (make sure to change "prefix_" to "mdl_" in the file)

3. Create a batch file in that folder called 'XPORT.BAT' with the following command:

mysql.exe --host=HOSTNAME --user=USERNAME --password=PASSWORD --database=moodle19 < XPORT.SQL > OUTPUT.DAT

Where:

  • HOSTNAME is the servername (or could be 'localhost')
  • USERNAME is the name of the user of the database
  • PASSWORD is the password of the above-named user
  • moodle19 is the name of the database (yours could be different)
  • XPORT.SQL is the file you created above with your SQL commands
  • OUTPUT.DAT is the TAB-DELIMITED file to be created (it will still open in Excel)

4. Execute XPORT.BAT at the command line. This will create OUTPUT.DAT

5. Once you have the OUTPUT.DAT created you can create an FTP command file. It will have the commands needed to send the file to the recipient. You only need to create this file once. It should look something like this:

OPEN ftp.whatever.com
USER username
PASS password
HASH
BINARY
SEND OUTPUT.DAT
(other commands necessary)
CLOSE
BYE

Where:

  • OPEN <site> - makes the connection to the recipients server
  • USER <username> - the username they probably gave you to login
  • PASS <password> - the password for the account
  • HASH - will show "#" characters as each block is sent (this line is optional)
  • BINARY - indicates to the recipients FTP server that the file you are sending is binary
  • SEND <filename> - this command tells the receiving FTP server that you are sending a file (use GET if this is a file you are getting from them)
  • CLOSE - disconnectes from the remote server
  • BYE - quits the local FTP program

 

6. You then execute this file by using the following command at the command line:

FTP -S:XPORT.TXT

This can be added to the end of the XPORT.BAT file, if you like.

I'm unsure of your general skill level so if you have problems with any of this, let me know.

Average of ratings: -