SPL：Access FTP
FTP is a commonly-used file transfer service. Although JAVA provides APIs for reading and writing files from an FTP server, it is not convenient enough to use. Therefore, SPL provides some functions for further convenience.
Similar to the JDBC connection of relational database, SPL also connects FTP with paired "create/close".
ftp_open(server:port,user,pwd), server is the address of FTP server; port is its service port; user and pwd are the user name and password respectively, which can be omitted if they do not exist.
ftp_close(ftpConn), ftpConn is the FTP connection to be closed.
Code sample: A1 creates the connection, and A3 closes the connection after some other uploading and downloading operations in the middle steps.
| A | |
|---|---|
| 1 | =ftp_open("127.0.0.1":22) |
| 2 | …… |
| 3 | =ftp_close(A1) |
There are two modes of ftp, i.e., active/passive. And ftp_open() defaults to active mode; @d option can be used to specify the passive mode: ftp_open@d(...).
Besides, there may be situations where sftp is needed, and in this case, @s option should be added: ftp_open@s(...).
ftp_dir(ftpConn,files), ftpConn is the FTP connection; files is the wildcard (or specific name) of a file or directory on the server, which can be as many as needed.
Sample:
=ftp_dir(A1,"/")
To get all the files and directories in the root directory
=ftp_dir(A1,"**.txt","**.csv")
To get the txt and csv files in all directories, where wildcard character ** is allowed (? indicates a single character of the file name; * indicates any character of the file name; ** indicates any directory or file).
@d option can be used to get the directories only:
=ftp_dir@d(A1,"/")
@m option can be used to create a new directory:
=ftp_dir@m(A1,"/folder3")
And the result shows that it is created successfully:
@r option can be used to delete empty directory:
=ftp_dir@r(A1,"/folder3")
The operation is shown as a success when executed for the first time:
If we execute the deletion operation again, it will fail because the to-be-deleted directory does not exist or is not empty:
ftp_get(ftpConn, remoteFile, localFile), download the server file "remoteFile" as local file "localFile":
Sample:
=ftp_get(A1,"/file8.csv","d:/file8.csv")
The download is successful after execution:
The download will fail if there already exists a local file:
In this case, @f option can be added if we want to download the file by force and overwrite the local file:
=ftp_get@f(A1,"/file8.csv","d:/file8.csv")
ftp_put(ftpConn, remoteFile, localFile), upload the local file "localFile" to the server as "remoteFile":
Sample:
=ftp_put(A1,"/file8_new.csv","d:/file8.csv")
The upload is successful after execution:
Similarly, ftp_put also can be added with @f option to upload the file by force and overwrite the file that already exists on the server.
ftp_mget(ftpConn, serverFolder, localFolder, serverFiles), download "serverFolder" from the server to local as "localFolder"; the wildcard of the downloaded file is "serverFiles" which also can be multiple:
Sample:
To download all xls and xlsx files in /folder1 from the server to local d:/folder1:
=ftp_mget(A1,"/folder1","d:/folder1","*.xls","*.xlsx")
To download all directories and files in /folder1 from the server to local d:/folder1:
=ftp_mget(A1,"/folder1","d:/folder1","**")
Similar to ftp_get(), ftp_megt can also use @f option to overwrite the local files by force. In addition, @t option can be used to skip the files that already exist, and only download files that do not exist locally.
ftp_mput(ftpConn, serverFolder, localFolder, localFiles), upload "localFiles" from "localFolder" to "serverFolder" on the server, which also supports multiple wildcards:
Sample:
To upload all xls and xlsx files in local d:/folder1 to /folder on the server:
=ftp_mput(A1,"/folder1","d:/folder1","*.xls","*.xlsx")
To upload all directories and files in local d:/folder to /folder1 on the server:
=ftp_mput(A1,"/folder1","d:/folder1","**")
Similar to ftp_put(), ftp_mput can also use @f option to overwrite the server files by force, and an addition @t option to skip the files that already exist and only upload the files that do not exist on the server.
In the previous examples, the server directories are all absolute paths starting with "/". If we frequently perform the operations in a certain directory, ftp_cd() can be used to modify the current working directory, after which the server directory involved will not start with "/", but are relative to the current working directory.
ftp_cd(ftpConn, remotePath), set remotePath as the working directory.
file7.json is in /folder2, and we set it as the working directory in A2, then the whole absolute path does not need to be specified when downloading file7.json in A3:
| A | |
|---|---|
| 1 | =ftp_open("127.0.0.1":22) |
| 2 | =ftp_cd(A1, "/folder2") |
| 3 | =ftp_get(A1, "file7.json", "d:/file7.json") |
| 4 | …… |
| 5 | =ftp_close(A1) |
The relative paths used in ftp_dir(), ftp_put(), ftp_mget(), ftp_mput() and other functions are also relative to the current working directory when serve directory is involved in such operations.