CSV Import/Export in Liferay using a custom database

  • In Liferay, we will export/import data into different formats and one of the format is CSV (Comma-separated values). Similar way, you can also import/export data using excel file.
  • This blog is on how to export/import data as CSV in Liferay Portlet Development.
  • Here we will export all users details as CSV formatted file from database and import user details from CSV formatted file to the database.

Data Exports as CSV Format in Liferay

Step 1: We get all users list using UserLocalServiceUtil.

List<User> usersList = UserLocalServiceUtil.getUsers(0,UserLocalServiceUtil.getUsersCount());

Step 2: Then we prepare each column and record as CSV format like each column separated by comma(“,”) and each record as a new line.

String[] columnNames = { "UserId","EmailAddress", "FirstName", "LastName", "Birth Date" };

public static final String COMMA = ",";


StringBundler sb = new StringBundler();

for (String columnName : columnNames) {

sb.append(CharPool.QUOTE);

sb.append(StringUtil.replace(columnName, CharPool.QUOTE,StringPool.DOUBLE_QUOTE));

sb.append(CharPool.QUOTE);

sb.append(COMMA);

}

sb.setIndex(sb.index() - 1);

sb.append(CharPool.NEW_LINE);

Step 3: And then we export this data as CSV file.

byte[] bytes = sb.toString().getBytes();
String contentType = ContentTypes.APPLICATION_TEXT;
PortletResponseUtil.sendFile(resourceRequest, resourceResponse,"EmployeeData.csv",
bytes, contentType);

 

Portlet View

CSV File (EmployeeData.csv)

Here is the sample module link for csv export
https://github.com/MitiPatel1701/csv-import-export/tree/master/user-csv-export-web

  • Like this we retrieved the data from the database and prepared them as CSV.

 

Import User Data to Database From CSV

Step 1 : Upload CSV file.

<%@ include file="/init.jsp" %>
<portlet:actionURL var="userCSVDataUploadURL" name="userCSVDataUpload"></portlet:actionURL>
<p>
<b>Add user to Liferay DB from CSV</b>
</p>
<form action="${userCSVDataUploadURL}" enctype="multipart/form-data" method="post" id="csvDataFileForm">
<div>
<label>Upload User Data CSV :</label>
<input type="file" name='<portlet:namespace/>csvDataFile' id="csvDataFile"></input>
</div>
<div>
<input type="submit"></input>
</div>
</form>

Step 2: Read CSV File, which returns data in the form of JSONArray.

public void userCSVDataUpload(ActionRequest actionRequest, ActionResponse actionResponse)
throws IOException, PortletException {
String filePath = "D:\\CSV\\Import\\User.csv";
try (FileOutputStream fOut = new FileOutputStream(filePath);) {
UploadPortletRequest uploadRequest = PortalUtil.getUploadPortletRequest(actionRequest);
InputStream is = uploadRequest.getFileAsStream("csvDataFile");
int i;
while ((i = is.read()) != -1) {
fOut.write(i);
}
File csvFile = new File(filePath);
log.info("CSV File ===> " + csvFile);
JSONArray csvDataArray= JSONFactoryUtil.createJSONArray();
if (Validator.isNotNull(csvFile)) {
if (csvFile.getName().contains(".csv")) {
try (InputStream targetStream = new FileInputStream(csvFile);
InputStreamReader isr = new InputStreamReader(targetStream);) {

CSVFormat csvFormat = CSVFormat.newFormat(',').withIgnoreEmptyLines().withTrim(true);
CSVParser csvParser = csvFormat.parse(isr);
if (csvParser != null) {
JSONObject rowObject = null;
for (CSVRecord record : csvParser) {
rowObject = JSONFactoryUtil.createJSONObject();
for (int j = 0; j < record.size(); j++) {
rowObject.put(String.valueOf(j), record.get(j));
}
csvDataArray.put(rowObject);
}
log.info("CSV Data : " + csvDataArray.toString());
}

} catch (IOException e) {
log.error("Exception while reading file : ", e);
throw e;
}
} else {
log.error("Uploaded File is not CSV file.Your file name is ----> ");
}

}
} catch (Exception e) {
log.error("Exception in CSV File Reading Process :: ", e);
}
}
}

Step 3: Create a bean and set that CSV data in the bean.

if (Validator.isNotNull(csvDataArray)) {
log.info("Data Array Length ===> " + csvDataArray.length());
for (int i = 1; i < csvDataArray.length(); i++) {
JSONObject jsonObject = csvDataArray.getJSONObject(i);
log.info("Json Object ===> " + jsonObject);
UsersBean csvToBean = new UsersBean();
csvToBean.setEmployeeId(jsonObject.getString("0"));
if (Validator.isNotNull(jsonObject.getString("1"))) {
csvToBean.setEmailId(jsonObject.getString("1").trim());
}
if (Validator.isNotNull(jsonObject.getString("2"))) {
csvToBean.setFirstName(jsonObject.getString("2"));
}
if (Validator.isNotNull(jsonObject.getString("3"))) {
csvToBean.setLastName(jsonObject.getString("3"));
}
Date dob = null;
if (Validator.isNotNull(jsonObject.getString("4"))) {
dob = dateFormate.parse(jsonObject.getString("4"));
}
csvToBean.setDob(dob);
csvToBean.setMobileNumber(jsonObject.getString("5"));
csvToBean.setPortalUrl(portalUrl);
}
}

Step 4: Validate the user that the user is already exist or not.

User userScreenName = UserLocalServiceUtil.getUserByScreenName(companyId,usersBean.getMobileNumber());
log.info("User Screen Name ===> " + userScreenName);
if (userScreenName != null) {
userExists = true;
log.info(" User already Exists : " + userExists);
}

Step 5: If the user is not exist then add that user in the database.

if (Validator.isNumber(usersBean.getMobileNumber())&& Validator.isNotNull(usersBean.getDob())) {
try {
if (Validator.isNull(user)) {
String password = "test";
user=UserLocalServiceUtil.addUser(adminUser.getUserId(),companyId,false,password,password,false, usersBean.getMobileNumber().trim(),usersBean.getEmailId(),0L,StringPool.BLANK, adminUser.getLocale(),usersBean.getFirstName(),usersBean.getMiddleName(),usersBean.getLastName(),0L,0L,true,usersBean.getDob().getMonth(),usersBean.getDob().getDate(),usersBean.getDob().getYear(), StringPool.BLANK, null, null, null, null, false,new ServiceContext());
log.info("User added Successfully in database : " + usersBean.getEmailId());
}
} catch (PortalException e) {
log.error("Error while adding user in database : " + e.getStackTrace());
}
}

 

Sample of CSV File (User.csv - Located at "D:\CSV\Import")

Portlet View

Choose the CSV file here and submit it.

After submitting CSV check logs or you can also check in the database that the user is successfully added or not.

 

Here is the sample module link for csv import
https://github.com/MitiPatel1701/csv-import-export/tree/master/user-csv-import-web

  • Like this we read the data from CSV and add it into the database.

 

 

Blogs