How to read a CSV file from FTP server in Java and store it’s data into the database

In this article, we will learn how to read a CSV (Comma-Separated Values) file from a FTP Server, download it in the local directory and store it’s data into the database. 

What Does FTP Server Mean?

An FTP server is a computer that has a file transfer protocol (FTP) address and is dedicated to receiving an FTP connection.  The primary purpose of an FTP server is to allow users to upload and download files. 

FTP is a protocol used to transfer files via the internet between a server (sender) and a client (receiver)An FTP server is a computer that offers files available for download via an FTP protocol, and it is a common solution used to facilitate remote data sharing between computers.

How to read & download a file from FTP Server?

Now, let’s see how a file can be read & downloaded from remote FTP server.

To perform our task, any one from the following libraries can be used :-

  • JSch
  • SSHJ
  • Apache Commons VFS 

Here, we will use SSHJ library because it provides more easy and quickly understable set of steps to accomplish the task.

1. Setting up the Project

Firstly, let’s create a Spring Boot Application to implement the whole idea.

You can either create the application from https://start.spring.io/ or use an IDE to create it.

 

2. Adding necessary dependencies

  Add the following dependency to the pom.xml file. 

The latest version of sshj can be found on Maven Central

Note : Just to make the development easier, we are using Project Lombok library here.  This library auto generate getters and setters for each private field with just one-liner annotations(@Getter, @Setter).  Also, constructors can be generated using annotations such as (@AllArgsConstructor, @NoArgsConstructor and @RequiredArgsConstructor).

 

3. Adding FTP credentials in properties file

To keep the FTP related configurations on one place, we’ll define the configurations in the application.properties file.

Add the following configurations in the application.properties file.

Note : Replace the data within curly brackets { } with the actual values.

hostname represents the name or IP address of the remote server( from which you are reading your CSV file).

username & password should be replaced by the credentials of the remote server.

Remote File path : Location on the remote server, where the file is located. Ex: /home/auriga/Desktop/test.csv

Local Directory File path : Location on the local computer, where the file needs to be downloaded, after being read from the remote server. Ex: /home/chhavi/Desktop/CSV_Operations/src/main/resources/example.csv

Now, let’s create a Constant class and initialize the field values with ftp configurations (defined in application.properties file) earlier.

 

4. Setting up SSHJ

Next, let’s set up the SSHClient.

An SSH client is a program that allows establishing a secure and authenticated SSH connections to SSH servers.

SSHJ also allows us to use Password or Public Key Authentication to access the remote server. We’ll use the Password Authentication in our example.

Let’s inject the FtpConstants class object using @Autowired notation.

Function to setup Sshj –

 

5.  Downloading a File With SSHJ

Function to download a file from SSHJ –

This function requires the remote filepath and the path where that file needs to be downloaded.

After executing this function, we have successfully downloaded the CSV file from remote server to our local computer.

CSV File’s content:

Note : The file from the remote server can be removed easily after the download operation has been performed, by calling the rm method of SFTPClient :-

How to insert data from CSV to database?

We will be using MySQL as our database. You can use any database to store the data using different database connectors.

1. Adding necessary dependencies

Dependencies to be added in the pom.xml file :

  1. spring-boot-starter-data-jpaSpring Data JPA aims to significantly improve the implementation of data access layers by reducing the effort to the amount that’s actually needed. As a developer you write your repository interfaces, including custom finder methods, and Spring will provide the implementation automatically.
  2. mysql-connector-java – To integrate MySQL database to our application.

2. Setting up database configurations in properties file

Define the important properties in the application.properties file for connecting your database with the application.

Replace the datasouce url, username and password with your database configurations.

3. Creating Entity and Repository

To store the CSV content in the database, we will need to create an Entity with fields as CSV file’s headers.

Now, to perform database operations, we’ll need to create a repository for the table that we have created.

4. Storing Data into the database

No, let’s create a function to store the file’s data into the database.

Working of above method :-

  1. Read a file from the specified path using the File and BufferedReader class.
  2. Since, the CSV file is a comma-seperated file, so, to split the data, we need to define the split by String as “,”.
  3. Extract the CSV headers into the headers array.
  4. Then run a loop and extract the record into the data array.
  5. When all the data is in required format,  save the record into the database.

Now, we’ll check whether our content has been successfully saved or not in the database.

Open the terminal and look into the ’employee’ table of our database that we had created earlier.

From the result above, we can say that our CSV’s content has been successfully saved into the database.

This accomplish the purpose of our Blog.

Hope this article helped you and you had fun reading it.😊

Happy Learning!

 

 

 

 

 

 

 

 

Comments