i'm reading CSV file from java and inserting into database mysql,while reading csv file i'm getting data with inverted commas but i want to read data without inverted commas can any one help me .....!
"03_lab3_lsi1_kw","06-02-2017 12:02:13",7243.732,1,42772501540.0694
Here is my code:
package com.savecsvtopostgres;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class SaveCsvDataToPostgres {
List responseList = new ArrayList();
public static void main(String[] args){
BufferedReader csvBuffer = null;
List csvlist = new ArrayList();
try {
String csvLine;
csvBuffer = new BufferedReader(new FileReader("C:\\Users\\DVSabareesh\\testresults.csv"));
while ((csvLine = csvBuffer.readLine()) != null) {
System.out.println("Raw CSV data: " + csvLine);
OauthResponse oauthResponse = csvtoArrayList(csvLine);
csvlist.add(oauthResponse);
}
insert(csvlist);
System.out.println("Converted ArrayList data: " + csvlist.size() + "\n");
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (csvBuffer != null) csvBuffer.close();
} catch (IOException exception) {
exception.printStackTrace();
}
}
}
// Utility which converts CSV to ArrayList using Split Operation
public static OauthResponse csvtoArrayList(String csvLine) {
OauthResponse oauthResponse = new OauthResponse();
if (csvLine != null) {
String[] splitData = csvLine.split(",");
int datalength = splitData.length;
System.out.println(datalength);
oauthResponse.setId(splitData[0]);
oauthResponse.setValue2(splitData[1]);
oauthResponse.setTicketName(splitData[2]);
oauthResponse.setTicketResponse(splitData[3]);
oauthResponse.setResponseId(splitData[4]);
oauthResponse.setResponseMessage(splitData[5]);
oauthResponse.setOperations(splitData[6]);
oauthResponse.setFileType(splitData[7]);
oauthResponse.setBooleanValue(splitData[8]);
oauthResponse.setId2(splitData[9]);
oauthResponse.setId3(splitData[10]);
oauthResponse.setOauthUrl(splitData[11]);
if(datalength == 13){
oauthResponse.setValue13(splitData[12]);
}else{
oauthResponse.setValue13("");
}
}
return oauthResponse;
}
// Utility which used to insert data into postgres
public static void insert(List responseList) throws SQLException {
Connection dbConnection = null;
PreparedStatement ps = null;
try {
dbConnection = getDBConnection();
dbConnection.setAutoCommit(false);
String query = "INSERT INTO csvvalues("
+ "testgeneratorid, "
+ "ticketvalues1, "
+ "ticketname, "
+ "ticketresponsecode, "
+ "ticketresponsemessage, "
+ "testcaseresults, "
+ "resultformat, "
+ "booleanresult, "
+ "ticketvalues2, "
+ "ticketvalues3, "
+ "oauthurl, "
+ "ticketvalues4, "
+ "ticketvalues5) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
ps = dbConnection.prepareStatement(query);
System.out.println("Records are getting inserted into TestSuiteResults.TestCase table!");
for(OauthResponse res : responseList){
ps.setString(1,res.getId());
ps.setString(2,res.getValue2());
ps.setString(3,res.getTicketName());
ps.setString(4,res.getTicketResponse());
ps.setString(5,res.getResponseId());
ps.setString(6,res.getResponseMessage());
ps.setString(7,res.getOperations());
ps.setString(8,res.getFileType());
ps.setString(9,res.getBooleanValue());
ps.setString(10,res.getId2());
ps.setString(11,res.getId3());
ps.setString(12,res.getOauthUrl());
ps.setString(13,res.getValue13());
ps.addBatch();
}
ps.executeBatch();
dbConnection.commit();
System.out.println("Record is inserted into DBUSER table!");
} catch (SQLException e) {
e.getNextException().printStackTrace();
dbConnection.rollback();
} finally {
if (ps != null) {
ps.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
// Utility which gets connection to postgres
public static Connection getDBConnection() {
Connection dbConnection = null;
try {
//Class.forName("org.postgresql.Driver");
//dbConnection = DriverManager.getConnection(
//"jdbc:postgresql://localhost:5432/Database4TesSuitetResults", "postgres", "santhosh");
Class.forName("com.mysql.jdbc.Driver");
dbConnection = DriverManager.getConnection(
"jdbc:mysql://localhost:3309/csv","root","system");
System.out.println("DB connected");
return dbConnection;
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
Answer
Use OpenCSV(.jar) CsvFileReader to read the data from csv file
CSVReader csvfile = new CSVReader("FilePath\\.csv");
String[] csvdata = csvfile.readnext(); // To read the row one by one
// Read all row from csv
List csvdata1 = csvfile.readAll(); //To Read all data from csv file
String[] csvdata = csvdata1.get(0);//Read first row
No comments:
Post a Comment