fsyed
asked on
Want to create a .sql file from a .csv file using Java
Dear fellow Java developers:
I have a very large data set in a .csv file, which I want to use to generate an equivalent .sql file for the purpose of a sql dump in a MySQL table. I realize there are many tools that can do the conversion, I would like to know how to do this in java. The data in my .csv files have the column headings in the very first row, there are no spaces after the commas, and the data in the csv file is not surrounded by quotation marks(" "). The .csv file is structured as follows:
columnA,columnB,columnC,co lumnD,colu mnE
a1,111,-111,aaa,aa
b2,222,-222,bbb,bb
c3,333,-333,ccc,cc
d4,444,-444,ddd,dd
...
i9,999,-999,iii,ii
I want to read in a .csv file, and write out a .sql file where I am inserting into a table called "sampledata", that appears like this:
INSERT INTO `sampledata` (`columnA`, `columnB`, `columnC`, `columnD`, `columnE`) VALUES
("a1", 111, -111, 'aaa', 'aa'),
("b2", 222, -222, 'bbb', 'bb'),
("c3", 333, -333, 'ccc', 'cc'),
("d4", 444, -444, 'ddd', 'dd'),
...
("i9",999,-999,"iii","ii") ;
Thanks in advance to all who reply.
I have a very large data set in a .csv file, which I want to use to generate an equivalent .sql file for the purpose of a sql dump in a MySQL table. I realize there are many tools that can do the conversion, I would like to know how to do this in java. The data in my .csv files have the column headings in the very first row, there are no spaces after the commas, and the data in the csv file is not surrounded by quotation marks(" "). The .csv file is structured as follows:
columnA,columnB,columnC,co
a1,111,-111,aaa,aa
b2,222,-222,bbb,bb
c3,333,-333,ccc,cc
d4,444,-444,ddd,dd
...
i9,999,-999,iii,ii
I want to read in a .csv file, and write out a .sql file where I am inserting into a table called "sampledata", that appears like this:
INSERT INTO `sampledata` (`columnA`, `columnB`, `columnC`, `columnD`, `columnE`) VALUES
("a1", 111, -111, 'aaa', 'aa'),
("b2", 222, -222, 'bbb', 'bb'),
("c3", 333, -333, 'ccc', 'cc'),
("d4", 444, -444, 'ddd', 'dd'),
...
("i9",999,-999,"iii","ii")
Thanks in advance to all who reply.
Example of what I was talking about earlier -read java.csv, write java.sql and check for numeric fields by checking column names. You might wanna do more validation checking etc etc etc but this should get you going
private void CreateCSV() {
String strLine;
String baseSql;
try {
// Open CSV File
FileInputStream fstream = new FileInputStream("C:/temp/java.csv");
FileWriter fwrite = new FileWriter("C:/temp/java.sql");
DataInputStream datastream = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(datastream));
strLine = br.readLine();
// Make an note of columns
String[] columnHeadings = strLine.split(",");
System.out.println("Column Headings: " + strLine);
baseSql = "INSERT INTO mytable (";
for (int i = 0; i < columnHeadings.length; i++) {
baseSql = baseSql + columnHeadings[i];
if (i < (columnHeadings.length-1))
baseSql = baseSql + ",";
}
baseSql = baseSql + ") VALUES (";
while ((strLine = br.readLine()) != null) {
String newSql = baseSql;
String[] values = strLine.split(",");
for (int i = 0; i < values.length; i++) {
// Check for numeric columns
if (columnHeadings[i].equals("columnB") || columnHeadings[i].equals("columnC"))
newSql = newSql + values[i];
else
newSql = newSql + "'" + values[i] + "'";
if (i < (values.length-1))
newSql = newSql + ",";
}
newSql = newSql + ");";
fwrite.write(newSql);
System.out.println(newSql);
}
fwrite.flush();
fwrite.close();
fstream.close();
} catch (Exception e) {
// file not found exception...
e.printStackTrace();
}
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks rockiroads for your prompt reply. Your code appears to work, as I see the results from the System.out. It appears exactly as I want it. However, when I try to open the file, it appears to take way to long for some unusual reason. The written file appears to be only 51.2MB but it seems to really drain the system resources as I try to open the file. Any idea why this is?
Thanks again for all of your help.
Thanks again for all of your help.
I used to use XSLT for this purpose, source data being in both CSV and XML formats.
can you try opening in wordpad or msword and not notepad.
I noticed the line breaks (in 2nd solution) appear properly but shows as one line in notepad.
let me try again with solution 3 to see if that is the case and to tweak it
I noticed the line breaks (in 2nd solution) appear properly but shows as one line in notepad.
let me try again with solution 3 to see if that is the case and to tweak it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am at work right now, so I haven't had a chance to implement your chages yet, however, I noticed that you didn't use a BufferedWriter object when using FileWriter. Does that matter?
Thanks again for your prompt replies!
Thanks again for your prompt replies!
Probably better to use BufferedWriter. I think that has the newline character on the write statement unlike FileWriter. I just picked FileWriter as it was something quick to use. BufferedWriter would be more efficient as that is better to use for short writes.
ASKER
Thanks very much rockiroads for your solution, it worked! I really appreciate your help. Take care.
You could test the data value but it could be alphanumeric so still has to be in quotes.
best way is to check the matching columnHeadings and do a check on that
Sample code opens a file called c:\temp\java.csv. You change it to your filename
Then look for INSERT INTO mytable and change it to your table
Results are currently displayed in the output window
Run it first to see if this is what you are after
Open in new window