I don't know if this is the right place to post this, but I'm looking for someone who can create a process to convert a daily csv file to xml that can be imported into our billing software.
I'm just looking for a way to find someone to pay to get this set up for me (the company I work for). I've tried searching and have come up empty, so am reaching out to reddit where I get all of my questions answered!
To sum up, I'm looking for where I would search to find someone to write a program / process for me to use to convert a csv file into xml to be able to be imported into a billing software program.
Thanks!
Convert XML to (meaningful) CSV
Conversion of CSV to XML with JAVA - Stack Overflow
XML Parts List convert to CSV?
Looking for easy ways to convert different kinds of Data formats to CSVs
Videos
Hello Redditors. I have a question that has landed on my desk, and which way outside of my practical skill set.
One party has an application that they are heavily invested in. They need to send data - say a list of orders - to another party. The only option for doing so is an XML export file. Let us presume that the vendor for this software is no longer available, so the XML is what it is.
The second party is setup in a similar way. They are used to ingesting lists of orders into their software by way of CSV files, setup in a particular format. Each column has a header and represents a given data set.
The question that I am looking at is how difficult would it be to convert source XML A into CSV B, with the appropriate data fields in the right places. We would need to do this on a regular basis. Keeping in mind that we have little to know development ability on staff, now might we achieve something along these lines? Specifically...
What might this process look like? Excel macro? Web app?
Where might we look to develop this process, whatever it looks like?
Any thoughts at all, given all of this rambling?
Thanks in advance guys.
I'd agree with Kennet.
I simply added
aTransformer .setOutputProperty(OutputKeys.INDENT, "yes");
aTransformer .setOutputProperty(OutputKeys.METHOD, "xml");
aTransformer .setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
This added a new line between the elements and allowed for indentation.
UPDATED
Let's start with the fact that the file you're presented isn't a CSV (Comma separated value) file and I'll let you worry about that problem...
List<String> headers = new ArrayList<String>(5);
File file = new File("Names2.csv");
BufferedReader reader = null;
try {
DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
Document newDoc = domBuilder.newDocument();
// Root element
Element rootElement = newDoc.createElement("XMLCreators");
newDoc.appendChild(rootElement);
reader = new BufferedReader(new FileReader(file));
int line = 0;
String text = null;
while ((text = reader.readLine()) != null) {
StringTokenizer st = new StringTokenizer(text, " ", false);
String[] rowValues = new String[st.countTokens()];
int index = 0;
while (st.hasMoreTokens()) {
String next = st.nextToken();
rowValues[index++] = next;
}
//String[] rowValues = text.split(",");
if (line == 0) { // Header row
for (String col : rowValues) {
headers.add(col);
}
} else { // Data row
Element rowElement = newDoc.createElement("row");
rootElement.appendChild(rowElement);
for (int col = 0; col < headers.size(); col++) {
String header = headers.get(col);
String value = null;
if (col < rowValues.length) {
value = rowValues[col];
} else {
// ?? Default value
value = "";
}
Element curElement = newDoc.createElement(header);
curElement.appendChild(newDoc.createTextNode(value));
rowElement.appendChild(curElement);
}
}
line++;
}
ByteArrayOutputStream baos = null;
OutputStreamWriter osw = null;
try {
baos = new ByteArrayOutputStream();
osw = new OutputStreamWriter(baos);
TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();
aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
Source src = new DOMSource(newDoc);
Result result = new StreamResult(osw);
aTransformer.transform(src, result);
osw.flush();
System.out.println(new String(baos.toByteArray()));
} catch (Exception exp) {
exp.printStackTrace();
} finally {
try {
osw.close();
} catch (Exception e) {
}
try {
baos.close();
} catch (Exception e) {
}
}
} catch (Exception e) {
e.printStackTrace();
}
Now I've used a List instead of a Map here. You'll need to decide how best to approach the missing values problem. Without knowing the structure of the file in advance, this is not going to be a simple solution.
Any way, I end up with
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<XMLCreators>
<row>
<Name>chi</Name>
<Age>23</Age>
<Sex/>
</row>
<row>
<Name>kay</Name>
<Age>19</Age>
<Sex>male</Sex>
</row>
<row>
<Name>John</Name>
<Age>male</Age>
<Sex/>
</row>
</XMLCreators>
Updated with merge
public class XMLCreators {
// Protected Properties
protected DocumentBuilderFactory domFactory = null;
protected DocumentBuilder domBuilder = null;
public XMLCreators() {
try {
domFactory = DocumentBuilderFactory.newInstance();
domBuilder = domFactory.newDocumentBuilder();
} catch (FactoryConfigurationError exp) {
System.err.println(exp.toString());
} catch (ParserConfigurationException exp) {
System.err.println(exp.toString());
} catch (Exception exp) {
System.err.println(exp.toString());
}
}
public int convertFile(String csvFileName, String xmlFileName,
String delimiter) {
int rowsCount = -1;
try {
Document newDoc = domBuilder.newDocument();
// Root element
Element rootElement = newDoc.createElement("XMLCreators");
newDoc.appendChild(rootElement);
// Read csv file
BufferedReader csvReader;
csvReader = new BufferedReader(new FileReader(csvFileName));
// int fieldCount = 0;
// String[] csvFields = null;
// StringTokenizer stringTokenizer = null;
//
// // Assumes the first line in CSV file is column/field names
// // The column names are used to name the elements in the XML file,
// // avoid the use of Space or other characters not suitable for XML element
// // naming
//
// String curLine = csvReader.readLine();
// if (curLine != null) {
// // how about other form of csv files?
// stringTokenizer = new StringTokenizer(curLine, delimiter);
// fieldCount = stringTokenizer.countTokens();
// if (fieldCount > 0) {
// csvFields = new String[fieldCount];
// int i = 0;
// while (stringTokenizer.hasMoreElements()) {
// csvFields[i++] = String.valueOf(stringTokenizer.nextElement());
// }
// }
// }
//
// // At this point the coulmns are known, now read data by lines
// while ((curLine = csvReader.readLine()) != null) {
// stringTokenizer = new StringTokenizer(curLine, delimiter);
// fieldCount = stringTokenizer.countTokens();
// if (fieldCount > 0) {
// Element rowElement = newDoc.createElement("row");
// int i = 0;
// while (stringTokenizer.hasMoreElements()) {
// try {
// String curValue = String.valueOf(stringTokenizer.nextElement());
// Element curElement = newDoc.createElement(csvFields[i++]);
// curElement.appendChild(newDoc.createTextNode(curValue));
// rowElement.appendChild(curElement);
// } catch (Exception exp) {
// }
// }
// rootElement.appendChild(rowElement);
// rowsCount++;
// }
// }
// csvReader.close();
//
// // Save the document to the disk file
// TransformerFactory tranFactory = TransformerFactory.newInstance();
// Transformer aTransformer = tranFactory.newTransformer();
// Source src = new DOMSource(newDoc);
// Result result = new StreamResult(new File(xmlFileName));
// aTransformer.transform(src, result);
// rowsCount++;
int line = 0;
List<String> headers = new ArrayList<String>(5);
String text = null;
while ((text = csvReader.readLine()) != null) {
StringTokenizer st = new StringTokenizer(text, delimiter, false);
String[] rowValues = new String[st.countTokens()];
int index = 0;
while (st.hasMoreTokens()) {
String next = st.nextToken();
rowValues[index++] = next;
}
if (line == 0) { // Header row
for (String col : rowValues) {
headers.add(col);
}
} else { // Data row
rowsCount++;
Element rowElement = newDoc.createElement("row");
rootElement.appendChild(rowElement);
for (int col = 0; col < headers.size(); col++) {
String header = headers.get(col);
String value = null;
if (col < rowValues.length) {
value = rowValues[col];
} else {
// ?? Default value
value = "";
}
Element curElement = newDoc.createElement(header);
curElement.appendChild(newDoc.createTextNode(value));
rowElement.appendChild(curElement);
}
}
line++;
}
ByteArrayOutputStream baos = null;
OutputStreamWriter osw = null;
try {
baos = new ByteArrayOutputStream();
osw = new OutputStreamWriter(baos);
TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();
aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
Source src = new DOMSource(newDoc);
Result result = new StreamResult(osw);
aTransformer.transform(src, result);
osw.flush();
System.out.println(new String(baos.toByteArray()));
} catch (Exception exp) {
exp.printStackTrace();
} finally {
try {
osw.close();
} catch (Exception e) {
}
try {
baos.close();
} catch (Exception e) {
}
}
// Output to console for testing
// Resultt result = new StreamResult(System.out);
} catch (IOException exp) {
System.err.println(exp.toString());
} catch (Exception exp) {
System.err.println(exp.toString());
}
return rowsCount;
// "XLM Document has been created" + rowsCount;
}
}
UPDATED with use of OpenCSV
public class XMLCreators {
// Protected Properties
protected DocumentBuilderFactory domFactory = null;
protected DocumentBuilder domBuilder = null;
public XMLCreators() {
try {
domFactory = DocumentBuilderFactory.newInstance();
domBuilder = domFactory.newDocumentBuilder();
} catch (FactoryConfigurationError exp) {
System.err.println(exp.toString());
} catch (ParserConfigurationException exp) {
System.err.println(exp.toString());
} catch (Exception exp) {
System.err.println(exp.toString());
}
}
public int convertFile(String csvFileName, String xmlFileName,
String delimiter) {
int rowsCount = -1;
BufferedReader csvReader;
try {
Document newDoc = domBuilder.newDocument();
// Root element
Element rootElement = newDoc.createElement("XMLCreators");
newDoc.appendChild(rootElement);
// Read csv file
csvReader = new BufferedReader(new FileReader(csvFileName));
//** Now using the OpenCSV **//
CSVReader reader = new CSVReader(new FileReader("names.csv"), delimiter.charAt(0));
//CSVReader reader = new CSVReader(csvReader);
String[] nextLine;
int line = 0;
List<String> headers = new ArrayList<String>(5);
while ((nextLine = reader.readNext()) != null) {
if (line == 0) { // Header row
for (String col : nextLine) {
headers.add(col);
}
} else { // Data row
Element rowElement = newDoc.createElement("row");
rootElement.appendChild(rowElement);
int col = 0;
for (String value : nextLine) {
String header = headers.get(col);
Element curElement = newDoc.createElement(header);
curElement.appendChild(newDoc.createTextNode(value.trim()));
rowElement.appendChild(curElement);
col++;
}
}
line++;
}
//** End of CSV parsing**//
FileWriter writer = null;
try {
writer = new FileWriter(new File(xmlFileName));
TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();
aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
Source src = new DOMSource(newDoc);
Result result = new StreamResult(writer);
aTransformer.transform(src, result);
writer.flush();
} catch (Exception exp) {
exp.printStackTrace();
} finally {
try {
writer.close();
} catch (Exception e) {
}
}
// Output to console for testing
// Resultt result = new StreamResult(System.out);
} catch (IOException exp) {
System.err.println(exp.toString());
} catch (Exception exp) {
System.err.println(exp.toString());
}
return rowsCount;
// "XLM Document has been created" + rowsCount;
}
}
Next update (2022)
- Added support for OpenCSV 5.*
- Replace white spaces in header (at least as many as I can think of that will fit into a regular expression)
So, for example, using something like...
Reports Device Name IP Address Interface Name Interface Description Time Traffic Utilization Out Traffic bps
Device1 1.1.1.1 5-Apr Mon May 09 23:00:00 UTC 2022 0 0 0
Device2 1.1.1.1 5-Apr Mon May 09 23:00:00 UTC 2022 0 0 0
It will generate an output of something like...
<XMLCreators>
<row>
<Reports>1</Reports>
<Device_Name>2</Device_Name>
<IP_Address>3</IP_Address>
<Interface_Name>4</Interface_Name>
<Interface>5</Interface>
<Description>6</Description>
<Time>7</Time>
<Traffic>8</Traffic>
<Utilization>9</Utilization>
<Out_Traffic>10</Out_Traffic>
<bps_>11</bps_>
</row>
<row>
<Reports>Device1</Reports>
<Device_Name>1.1.1.1</Device_Name>
<IP_Address>5-Apr</IP_Address>
<Interface_Name>Mon May 09 23:00:00 UTC 2022</Interface_Name>
<Interface>0</Interface>
<Description>0</Description>
<Time>0</Time>
</row>
<row>
<Reports>Device2</Reports>
<Device_Name>1.1.1.1</Device_Name>
<IP_Address>5-Apr</IP_Address>
<Interface_Name>Mon May 09 23:00:00 UTC 2022</Interface_Name>
<Interface>0</Interface>
<Description>0</Description>
<Time>0</Time>
</row>
</XMLCreators>
Runnable example
import com.opencsv.CSVParser;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Main {
public static void main(String[] args) {
new Main();
}
public Main() {
new XMLCreators().convertFile("Test.csv", "Test.xml", '\t');
}
public class XMLCreators {
// Protected Properties
protected DocumentBuilderFactory domFactory = null;
protected DocumentBuilder domBuilder = null;
public XMLCreators() {
try {
domFactory = DocumentBuilderFactory.newInstance();
domBuilder = domFactory.newDocumentBuilder();
} catch (FactoryConfigurationError exp) {
System.err.println(exp.toString());
} catch (ParserConfigurationException exp) {
System.err.println(exp.toString());
} catch (Exception exp) {
System.err.println(exp.toString());
}
}
public int convertFile(String csvFileName, String xmlFileName, char delimiter) {
int rowsCount = -1;
BufferedReader csvReader;
try {
Document newDoc = domBuilder.newDocument();
// Root element
Element rootElement = newDoc.createElement("XMLCreators");
newDoc.appendChild(rootElement);
// Read csv file
csvReader = new BufferedReader(new FileReader(csvFileName));
//** Now using the OpenCSV **//
CSVParser parser = new CSVParserBuilder()
.withSeparator(delimiter)
.build();
CSVReader reader = new CSVReaderBuilder(new FileReader(csvFileName))
.withCSVParser(parser)
.build();
//CSVReader reader = new CSVReader(csvReader);
String[] nextLine;
int line = 0;
List<String> headers = new ArrayList<String>(5);
while ((nextLine = reader.readNext()) != null) {
if (line == 0) { // Header row
for (String col : nextLine) {
headers.add(col);
}
} else { // Data row
Element rowElement = newDoc.createElement("row");
rootElement.appendChild(rowElement);
int col = 0;
for (String value : nextLine) {
String header = headers.get(col).replaceAll("[\\t\\p{Zs}\\u0020]", "_");
Element curElement = newDoc.createElement(header);
curElement.appendChild(newDoc.createTextNode(value.trim()));
rowElement.appendChild(curElement);
col++;
}
}
line++;
}
//** End of CSV parsing**//
FileWriter writer = null;
try {
writer = new FileWriter(new File(xmlFileName));
TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();
aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
Source src = new DOMSource(newDoc);
Result result = new StreamResult(writer);
aTransformer.transform(src, result);
writer.flush();
} catch (Exception exp) {
exp.printStackTrace();
} finally {
try {
writer.close();
} catch (Exception e) {
}
}
// Output to console for testing
// Resultt result = new StreamResult(System.out);
} catch (IOException exp) {
exp.printStackTrace();
} catch (Exception exp) {
exp.printStackTrace();
}
return rowsCount;
// "XLM Document has been created" + rowsCount;
}
}
}
public int convertFile(String csvFileName, String xmlFileName, String delimiter) {
int rowsCount = -1;
try {
Document newDoc = domBuilder.newDocument();
// Root element
Element rootElement = newDoc.createElement("XMLCreators");
newDoc.appendChild(rootElement);
// Read csv file
BufferedReader csvReader;
csvReader = new BufferedReader(new FileReader(csvFileName));
int fieldCount = 0;
String[] csvFields = null;
String[] csvValues = null;
// Assumes the first line in CSV file is column/field names
// The column names are used to name the elements in the XML file,
// avoid the use of Space or other characters not suitable for XML element
// naming
String curLine = csvReader.readLine();
if (curLine != null) {
csvFields=curLine.split(",", -1);
}
// At this point the coulmns are known, now read data by lines
int i =0;
while ((curLine = csvReader.readLine()) != null) {
csvValues=curLine.split(",", -1);
Element rowElement = newDoc.createElement("record-"+i);
try {
for(fieldCount=0;fieldCount<csvValues.length;fieldCount++)
{
String curValue = csvValues[fieldCount];
Element curElement = newDoc.createElement(csvFields[fieldCount]);
curElement.appendChild(newDoc.createTextNode(curValue));
rowElement.appendChild(curElement);
}
} catch (Exception exp) {
}
rootElement.appendChild(rowElement);
i++;
}