Read Comma separated file containing commas, single/ double quotes, line feed characters.
Read Comma separated
file containing commas, single/ double quotes, line feed characters.
While working in
iSupplier implementation, we need to upload comma separated file containing ASN
data provided by the client into database. While using comma separated file,
user had restrictions not to put comma or any special characters in the file.
But what if user needs to upload his address information into database. Address
may contain commas and/or special characters. Below is the code that reads CSV
file containing commas, special characters/quotes and inserts filtered data
into database.
/*************************************************************************************************************
*
*
Reading Data from comma separated file, this code will filter the data
from comma separated
*
file and then insert that data into table.
*
*
*
**********************************************************************************************************/
public String readCSVFile(String filePath,
String TableName, String ColumnList,String skipperValue,String UserId)
{
String LS =
System.getProperty("line.separator");
String fileContent = null;
String ErrorCount = null;
int skipper = Integer.parseInt(skipperValue);
String Contentwoquotes = "";
int beginIndex = 0,endIndex = 0,i = 0;
String splitString = "";
String BufferData = "";
int charFound;
int countNull = 1;
String quoteString = "";
int trackQuotes = 0;
String q2string = "";
try
{
FileReader fr = new
FileReader(filePath);
BufferedReader reader = new
BufferedReader(fr);
String line;
int rowNumber = 0;
while( skipper != 0 )
{
reader.readLine();
skipper--;
}
while ((line =
reader.readLine()) != null)
{
fileContent = line;
BufferData = "";
q2string = "";
quoteString = "";
Contentwoquotes =
"";
countNull = 0;
splitString = "";
int quoteTracker = 0;
while(line.indexOf(",") > -1 )
{
endIndex =
line.indexOf(",");
if (line.charAt(0) ==
'"')
{
i = 0;
quoteTracker = 0;
splitString =
"";
while ( i <
line.length() )
{
if
(line.charAt(i) == '"' )
{
if
(trackQuotes == 1 )
{
q2string = q2string +
line.charAt(i);
//
System.out.println("Quote String
At ::"+q2string);
//
System.out.println("Counter :"+trackQuotes);
i++;
trackQuotes = 0;
}
else
{
trackQuotes++;
quoteTracker = 0;
//
System.out.println("Track Quotes ::"+trackQuotes);
i++;
}
}
else if
("'".equals(line.substring(i,i+1)))
{
q2string
= q2string + line.charAt(i) + "'" ;
//
System.out.println("Quote String
At ::"+q2string);
//
System.out.println("Counter :"+trackQuotes);
i++;
trackQuotes = 0;
}
else
{
if
(line.charAt(i) == ',' && trackQuotes == 1)
{
charFound = checkASCII(q2string);
quoteTracker = 1;
if (charFound == 1)
{
BufferData = BufferData + "'" +q2string + "',";
Contentwoquotes
= Contentwoquotes + q2string + "
";
}
else
{
BufferData =
BufferData + q2string;
Contentwoquotes = Contentwoquotes + q2string + " ";
}
// q2string = "";
//
System.out.println("Line Data ::"+line);
//
System.out.println("Value of I ::"+i);
// System.out.println("Counter
:"+trackQuotes);
//
System.out.println("Line Length ::"+line.length());
//
line = line.substring(i+1);
// System.out.println("Remaining
line ::"+line);
//
if ("".equalsIgnoreCase(line))
//
BufferData = BufferData + "'',";
i
= 0 ;
break;
}
else
{
if (line.charAt(i) == ',')
{
endIndex = line.indexOf(",");
// System.out.println("End
index ::"+endIndex);
if (endIndex == 0 && quoteTracker != 0)
{
q2string = q2string + "'',";
}
else
q2string = q2string +
line.charAt(i);
}
else
{
q2string = q2string + line.charAt(i);
//
System.out.println("Quote String
At ::"+q2string);
//
System.out.println("Counter :"+trackQuotes);
// System.out.println("Value of I
::"+i);
//
System.out.println("Line length ::"+line.length());
//
trackQuotes++;
}
i++;
}
trackQuotes = 0;
/*
if
(line.charAt(i) == ',' && trackQuotes == 1)
{
splitString = q2string;
q2string
= "";
}
else
{
q2string = q2string + line.charAt(i);
System.out.println("Quote
String At ::"+q2string);
System.out.println("Counter :"+trackQuotes);
}*/
}
if(i ==
line.length() && trackQuotes == 0 && i != 0 && quoteTracker == 0)
{
i = 0;
line =
reader.readLine();
//
System.out.println("Line data......"+line);
if (line
== null )
break;
}
}
splitString =
splitString + q2string;
//
System.out.println("splitString ::"+splitString);
// Contentwoquotes =
Contentwoquotes + splitString + "
";
endIndex =
line.indexOf(",");
//
System.out.println("Buffer Data ::"+BufferData);
// System.out.println("End
Index ::"+endIndex);
if (endIndex != -1
&& endIndex != 0 )
{
if (line !=
null)
{
line =
line.substring(i+2);
endIndex =
line.indexOf(",");
}
}
else
{
line =
line.substring(i);
}
//
System.out.println("Remaining Line Data "+line);
}
else
{
endIndex =
line.indexOf(",");
splitString =
line.substring(beginIndex,endIndex);
i = 0;
quoteString = "";
trackQuotes = 0;
//
System.out.println("Split String catched :: "+splitString);
if
((splitString.indexOf("'")!= -1 ))
{
while ( i <
splitString.length() )
{
if
("'".equals(splitString.substring(i,i+1)))
{
quoteString = quoteString +
splitString.charAt(i) + "'" ;
//
System.out.println("Quote String
At ::"+quoteString);
i++;
//trackQuotes = 0;
}
else
{
quoteString = quoteString +
splitString.charAt(i);
//
System.out.println("Quote String
At ::"+quoteString);
i++;
//trackQuotes = 0;
}
}
splitString =
quoteString;
/*BufferData
= BufferData + "'" +quoteString + "',"; */
}
else
{
if (line !=
null)
{
if (endIndex
!= 0 )
{
charFound = checkASCII(splitString);
if
(charFound == 1)
{
BufferData = BufferData + "'" + splitString +
"',";
}
else
{
BufferData = BufferData + splitString + ",";
}
}
else
{
// If
string has null value
if(endIndex != -1)
{
countNull++;
//
System.out.println("Null Counter ::"+countNull);
BufferData
= BufferData + "'',";
}
}
}
else
{
charFound =
checkASCII(splitString);
if
(charFound == 1)
{
BufferData = BufferData + "'" + splitString +
"'";
}
else
{
BufferData = BufferData + splitString;
}
}
}
Contentwoquotes =
Contentwoquotes + splitString + "
";
if(line.length()
> endIndex)
line = line.substring(endIndex+1);
}
}
//
System.out.println("Content w/o quotes ::"+Contentwoquotes);
//
System.out.println("Line with quotes ::"+BufferData);
//
System.out.println("Line Data ::"+line);
//
System.out.println("Begin Index ::"+beginIndex);
// System.out.println("End
Index ::"+endIndex);
if (line == null )
break;
/** Locating last field in
the line */
if (line != null )
{
if
("".equalsIgnoreCase(line))
countNull++;
int
isQuoteFinished = 0;
int charFlag = 0;
int flag = 0;
/** Checking at
end of the Line */
//
System.out.println("Line length ::"+fileContent.length());
//
System.out.println("Null Counter ::"+countNull);
int contentlength
= fileContent.length() + 1;
// System.out.println("Now
outside the loop........");
if (countNull !=
contentlength)
{
if
("".equalsIgnoreCase(line))
BufferData = BufferData +
"''";
else if
(line.charAt(0) == '"')
{
q2string = "";
i = 0;
//
System.out.println("Double quotes at 0th index.");
while
( i < line.length() )
{
if (line.charAt(i) == '"' )
{
if (trackQuotes == 1 )
{
q2string = q2string + line.charAt(i);
//
System.out.println("Quote String
At ::"+q2string);
// System.out.println("Counter
:"+trackQuotes);
i++;
trackQuotes = 0;
}
else
{
trackQuotes++;
isQuoteFinished = 0;
i++;
}
charFlag = 0;
}
else
if ("'".equals(line.substring(i,i+1)))
{
q2string = q2string + line.charAt(i) + "'" ;
// System.out.println("Quote
String At ::"+q2string);
//
System.out.println("Counter :"+trackQuotes);
i++;
charFlag
= 0;
trackQuotes = 0;
}
else
{
if (line.charAt(i) == ',' && trackQuotes == 1)
{
charFound = checkASCII(q2string);
isQuoteFinished = 1;
if (charFound == 1)
{
BufferData = BufferData +
"'" +q2string + "',";
Contentwoquotes = Contentwoquotes + q2string + " ";
}
else
{
BufferData = BufferData + q2string + ",";
Contentwoquotes =
Contentwoquotes + q2string + "
";
}
q2string = "";
//
System.out.println("Line Data ::"+line);
//
System.out.println("Value of I ::"+i);
// System.out.println("Counter
:"+trackQuotes);
//
System.out.println("Line Length ::"+line.length());
line = line.substring(i+1);
//
System.out.println("Remaining line ::"+line);
if ("".equalsIgnoreCase(line))
BufferData = BufferData + "''";
i = 0;
}
else
{
if (line.charAt(i) == ',')
{
endIndex = line.indexOf(",");
// System.out.println("End
index ::"+endIndex);
//
System.out.println("Char Flag ::"+charFlag);
if (endIndex == 0 && isQuoteFinished != 0 && charFlag !=
1 )
{
flag = 1;
q2string = q2string + "'',";
}
else {
q2string = q2string + line.charAt(i);
//
System.out.println("Char Flag ::"+charFlag);
charFlag = 0;
}
}
else
{
q2string
= q2string + line.charAt(i);
charFlag = 1;
//
System.out.println("Quote String
At ::"+q2string);
//
System.out.println("Counter :"+trackQuotes);
trackQuotes++;
}
i++;
}
trackQuotes = 0;
}
//
System.out.println("Counter Value revs :"+trackQuotes);
if(i == line.length() &&
trackQuotes == 0 && i != 0 && isQuoteFinished == 0 )
{
i = 0;
line = reader.readLine();
/* New line Comes */
if (line == null )
break;
}
//
System.out.println("Buffer Data ::"+BufferData);
} // end of while ( i < line.length() )
if
(flag == 1)
line = line.substring(i-1);
//
System.out.println("Remaining Line Data ::"+line);
//
System.out.println("String ::"+q2string);
if
(",".equalsIgnoreCase(line))
{
BufferData = BufferData + q2string + "''";
}
else
{
charFound = checkASCII(q2string);
if (charFound == 1 )
{
BufferData = BufferData + "'" + q2string + "'";
}
else
{
BufferData = BufferData + q2string;
}
}
Contentwoquotes = Contentwoquotes + q2string + " ";
//
} // end
of if (line.charAt(0) == '"')
else
{
i = 0;
quoteString = "";
if
((line.indexOf("'")!= -1 ))
{
//
System.out.println("String having quotes.......");
while ( i < line.length() )
{
if ("'".equals(line.substring(i,i+1)))
{
quoteString = quoteString + line.charAt(i) + "'" ;
//
System.out.println("Quote String
At ::"+quoteString);
i++;
}
else
{
quoteString = quoteString + line.charAt(i);
// System.out.println("Quote
String At ::"+quoteString);
i++;
}
}
Contentwoquotes = Contentwoquotes + quoteString + " ";
BufferData = BufferData + "'" +quoteString + "'";
} // end of if
((line.indexOf("'")!= -1 ))
else
{
charFound = checkASCII(line);
if (charFound == 1){
BufferData = BufferData + "'"+ line + "'";
}
else{
BufferData = BufferData
+ line;
}
Contentwoquotes = Contentwoquotes + line
+ " ";
} //
end of nested else
} // end
of main else
//
System.out.println("Line length ::"+line.length());
//
System.out.println("line.charAt(beginIndex)
::"+line.charAt(beginIndex));
//
System.out.println("line.lastIndexOf('\"')::"+line.lastIndexOf('"'));
// System.out.println("Content
w/o quotes ::"+Contentwoquotes);
//
System.out.println("Line with quotes ::"+BufferData);
//
System.out.println("Line Data ::"+line);
// System.out.println("Begin Index
::"+beginIndex);
// System.out.println("End
Index ::"+endIndex);
insertIntoTable(BufferData,TableName,ColumnList,Contentwoquotes,UserId,rowNumber);
if (line
== null )
break;
}
}
else {
insertIntoTable(BufferData,TableName,ColumnList,Contentwoquotes,UserId,rowNumber);
}
} // end of while ((line =
reader.readLine()) != null)
rowNumber++;
}
catch (IOException e) {
e.printStackTrace();
}
/**********************************************************************
Below method inserts line into table.
**********************************************************************/
public void insertIntoTable(String RowData,String TableName, String ColumnList, String Contentwoquotes,String UserId, int rowNumber)
{
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getDBTransaction();
int beginIndex = 0,endIndex = RowData.indexOf(","),i = 0;
String splitString = "";
String Line = RowData;
String BufferData = "";
int charFound;
String InsertStmt;
ArrayList Value = new ArrayList();
CallableStatement oraclecallablestatement;
try
{
oraclecallablestatement = oadbtransactionimpl.createCallableStatement(insertstmt, -1);
oraclecallablestatement.execute();
System.out.println("Insert Command Executed Successfully.");
oraclecallablestatement.close();
}
catch (SQLException e)
{
String insertintoErrorTabHead = "INSERT INTO XXKOP.STGGING_ERROR_DTLS(ERROR_MSG,COLUMNNAME) " + " Values('"+e.getMessage().substring(e.getMessage().indexOf(" ")+1)+"','"+Contentwoquotes+"')";
try
{
oraclecallablestatement = oadbtransactionimpl.createCallableStatement(insertintoErrorTabHead, -1);
oraclecallablestatement.execute();
oraclecallablestatement.close();
}
catch (SQLException exp)
{
throw new OAException(e.getMessage() + " " + insertstmt,OAException.ERROR);
}
}
}
Thanks for sharing the useful information and good points were mentioned in this article and for the further information visit our site
ReplyDeleteOracle Fusion Financials Training
Thank you very much. The post was lengthy, but I loved to read it till the last word. That is a good tip especially to those new to the blog here. Brief but very accurate information…
ReplyDeleteOracle Fusion HCM Technical Coaching
Hi,
ReplyDeleteThis is a very interesting blog.Thanks for sharing such a
good blog.
oracle fusion SCM online training