Salesforce supports the conversion of only csv files utilizing Apex. However if you use their LWC - js platform that provides wide options to bring in libraries to parse all kinds of file types.
Here in this script i would be providing basic idea of functions utilizing the parsing logic along with options to iterate through each row for validations.
public class CSVParser {
public Integer index;
public Integer eof;
public String delimiter;
public List<String> header;
@testVisible private List<String> outputSuccessHeader;
@testVisible private List<String> outputErrorHeader;
/**
* @description Initializes the file setting the end of file to the files length and its index to 0, and instantiates
* the files header.
*/ public void start() {
if (file == null) file = '';
eof = file.length();
index = 0;
header = getRow();
}
public String file {
get;
set;
}
public String name {
get;
set;
}
/**
* @description Adds success and error columns to the header.
* @param headers a List<String> of success columns to add to the header
* @param errorHeader a List<String> of error columns to add to the header
*/ public void setHeaders(List<String> headers, List<String> errorHeader) {
if (!new Set<String>(errorHeader).contains(errors)) {
errorHeader.add(errors);
}
outputSuccessHeader = headers;
outputErrorHeader = errorHeader;
Set<String> existingColumns = new Set<String>(header);
for (String column : headers) {
if (!existingColumns.contains(column)) {
existingColumns.add(column);
header.add(column);
}
}
for (String column : errorHeader) {
if (!existingColumns.contains(column)) {
existingColumns.add(column);
header.add(column);
}
}
}
/**
* @description Retrieves the number of rows requested.
* If there are less than the requested amount the remaining rows will be returned.
* @param rowCount an Integer representing the number of rows to recieve.
* @return a List<Row> retrieved from the file.
*/ public List<Row> getRows(Integer rowCount) {
List<Row> rows = new List<Row>();
List<String> row;
for (Integer n = 0; n < rowCount; n++) {
if (index == eof) return rows;
row = getRow();
if (row.isEmpty()) continue;
rows.add(new Row(row, this));
}
return rows;
}
/**
* @description Gets a single row from the file.
* If the end of file is reached an empty row will be returned.
* @return a List<String> representing the retrieved row.
*/ @testVisible
private List<String> getRow() {
List<String> row = new List<String>();
String character = '';
String cell = '';
Boolean escapeCharacters = false;
while(index != eof) {
if (file.charAt(index) == 65279) {
index++;
}
character = file.substring(index, index + 1);
index++;
if (character == '"') {
escapeCharacters = !escapeCharacters;
}
if (!escapeCharacters) {
if(character == '\n') {
row.add(cell.trim().unescapeCsv());
if (isEmpty(row)) {
row = new List<String>();
cell = '';
} else {
return row;
}
} else if ((character == ',' || character == '\t') && delimiter == null) {
delimiter = character;
row.add(cell.trim().unescapeCsv());
cell = '';
} else if(character == delimiter) {
row.add(cell.trim().unescapeCsv());
cell = '';
} else {
cell += character;
}
} else {
cell += character;
}
}
row.add(cell.trim().unescapeCsv());
return isEmpty(row) ? new List<String>() : row;
}
/**
* @description Returns a Boolean indicating if a cell in the row given has an empty or #N/A value.
* @return A Boolean indicating if a cell in the row given has an empty or #N/A value.
*/ @testVisible
private static Boolean isEmpty(List<String> row) {
for (String cell : row) {
if (!String.isEmpty(cell) && cell != '#N/A') {
return false;
}
}
return true;
}
/**
* @description Gets duplicates in the column given.
* @param column a String representing the column to find duplicates for.
* @return A Set<String> of duplicate values.
*/ public Set<String> getDuplicates(String column) {
return getDuplicates(column, new Set<String>());
}
/**
* @description Gets duplicates in the column given.
* @param column a String representing the column to find duplicates for.
* @param foundCells a Set<String> of already found cell values.
* @return A Set<String> of duplicate values.
*/ public Set<String> getDuplicates(String column, Set<String> foundCells) {
Set<String> duplicates = new Set<String>();
List<String> rowText = new List<String>();
Integer startingIndex = index;
index = 0;
getRow(); // Skips the header
while (index != eof) {
rowText = getRow();
if (rowText.isEmpty()) continue;
Row row = new Row(rowText, this);
String value = row.get(column);
if (foundCells.contains(value)) {
duplicates.add(value);
} else {
foundCells.add(value);
}
}
index = startingIndex;
return duplicates;
}
/**
* @description Gets the index of the requested column.
* @param column a String representing the column to search for.
* @param columns a List<String> of columns to search thgough
* @return The index of the column.
*/ public static Integer getColumnIndex(String column, List<String> columns) {
for (Integer n = 0; n < columns.size(); n++) {
if (columns[n] == column) return n;
}
return -1;
}
/**
* @description Joins the cells given into a csv format.
* @param cells a List of String to turn into a csv.
* @return A String representing the comma joined cells.
*/ public static String joinCSV(List<String> cells) {
List<String> csvCells = new List<String>();
for (String cell : cells) {
csvCells.add(cell.escapeCSV());
}
return String.join(csvCells, ',');
}
/**
* @description Retrieves a column from the List of Row given.
* @param rows a List of Row to retrieve the column values from.
* @param column a String representing the column to retrieve.
* @return A Set<String> representing the values in the column requested.
*/ public static Set<String> pluckColumns(List<Row> rows, String column) {
Set<String> cells = new Set<String>();
for (Row row : rows) {
cells.add(row.get(column));
}
return cells;
}
public class Row {
private final CSVParser parent;
@testVisible private final List<String> cells;
public final Set<String> errors = new Set<String>();
@testVisible public Boolean isChild { get; private set; }
/**
* @description Constructor: sets up a child row from the row given.
* @param other a Row representing the rewly created rows parent.
*/ public Row(Row other) {
parent = other.parent;
cells = new List<String>();
isChild = true;
while (cells.size() < parent.header.size()) {
cells.add('');
}
}
/**
* @description Constructor: sets up a row from the cells and parent file given.
* @param cells a List<String> representing the cells of the row.
* @param parent a CSVParser representing the rows parent.
*/ public Row(List<String> cells, CSVParser parent) {
this.cells = cells;
this.parent = parent;
this.isChild = false;
while (this.cells.size() < parent.header.size()) {
this.cells.add('');
}
}
/**
* @description Gets the value at the column requested.
* @param column a String representing the column value to retrieve.
* @return A String representing the value found at the column
*/ public String get(String column) {
Integer index = CSVParser.getColumnIndex(column, parent.header);
if (index == -1) return '';
return cells[index];
}
/**
* @description Gets the value at the column requested.
* @param column an Integer representing the cell to retrieve.
* @return A String representing the value found at the cell
*/ public String get(Integer index) {
return cells[index];
}
/**
* @description Gets the decimal value at the column requested.
* @param column a String representing the column value to retrieve.
* @return A Decimal representing the value found at the column.
*/ public Decimal getCurrency(String column) {
try {
return Decimal.valueOf(get(column).replaceAll('[\\$,\\ ]', ''));
} catch (TypeException ex) {
return null;
}
}
/**
* @description Gets the value at the column specified.
* @param column a String representing the column to set.
* @param value a String representing the new value of the column.
*/ public void set(String column, String value) {
Integer index = CSVParser.getColumnIndex(column, parent.header);
if (index == -1) {
throw new CSVParserException('no column found: ' + column);
}
cells[index] = value;
}
/**
* @description Generates a CSV from the values in the success headers.
*/ public String getSuccessCSV() {
List<String> csv = new List<String>();
for (String column : parent.outputSuccessHeader) {
csv.add(get(column));
}
return joinCSV(csv);
}
/**
* @description Generates a CSV from the values in the error headers.
*/ public String getErrorCSV() {
set(errors, String.join(new List<String>(errors), ', '));
List<String> csv = new List<String>();
for (String column : parent.outputErrorHeader) {
csv.add(get(column));
}
return joinCSV(csv);
}
}
public class CSVParserException extends Exception { }
}