Solved

Want to create a .sql file from a .csv file using Java

Posted on 2010-09-23
11
786 Views
Last Modified: 2013-12-29
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,columnD,columnE
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.




0
Comment
Question by:fsyed
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33751130
this is a very simple example. it treats everything as a string. unless you know what field is numeric or not then you can change accordingly

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
private void CreateCSV() {
		String strLine;
		String baseSql;

		try {
			// Open CSV File
			FileInputStream fstream = new FileInputStream("C:/temp/java.csv");
			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++) {
					newSql = newSql + "'" + values[i] + "'";
					if (i < (values.length-1)) {
						newSql = newSql + ",";
					}
				}
				newSql = newSql + ");";
				System.out.println(newSql);
			}
			fstream.close();
			
		} catch (Exception e) {
			// file not found exception...
			e.printStackTrace();
		}
	}

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33751156
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();
		}
	}

Open in new window

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 475 total points
ID: 33751188
ok, just saw how you wanted the data

What I did was create several INSERT INTO statements

So this change should try make the sql look like what you want.
private static 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 sampledata (";
			for (int i = 0; i < columnHeadings.length; i++) {
				baseSql = baseSql + columnHeadings[i];
				if (i < (columnHeadings.length-1))
					baseSql = baseSql + ",";
			}
			baseSql = baseSql + ") VALUES ";
			fwrite.write(baseSql);
			
			strLine = br.readLine();
			while (strLine != null) {
				String newSql = "(";
				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 + ",";
				}
				strLine = br.readLine();
				if (strLine != null)
					newSql = newSql + "),";
				else
					newSql = newSql + ");";
				fwrite.write(newSql);
				fwrite.flush();
				System.out.println(newSql);
			}
			fwrite.close();
			fstream.close();
			
		} catch (Exception e) {
			// file not found exception...
			e.printStackTrace();
		}
	}

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 26

Assisted Solution

by:ksivananth
ksivananth earned 25 total points
ID: 33751380
use some opensourced framework like OpenCSV for preparing csv file, it will make you job easier and less error prone.
0
 

Author Comment

by:fsyed
ID: 33751567
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.
0
 
LVL 10

Expert Comment

by:Hegemon
ID: 33751736
I used to use XSLT for this purpose, source data being in both CSV and XML formats.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33754937
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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 475 total points
ID: 33755323
solution 3 code update forced the new line
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");
			String newLine = System.getProperty("line.separator");
			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 sampledata (";
			for (int i = 0; i < columnHeadings.length; i++) {
				baseSql = baseSql + columnHeadings[i];
				if (i < (columnHeadings.length-1))
					baseSql = baseSql + ",";
			}
			baseSql = baseSql + ") VALUES ";
			fwrite.write(baseSql);
			
			strLine = br.readLine();
			while (strLine != null) {
				String newSql = "(";
				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 + ",";
				}
				strLine = br.readLine();
				if (strLine != null)
					newSql = newSql + "),";
				else
					newSql = newSql + ");";
				newSql = newSql + newLine;
				fwrite.append(newSql);
				fwrite.flush();
				System.out.println(newSql);
			}
			fwrite.close();
			fstream.close();
			
		} catch (Exception e) {
			// file not found exception...
			e.printStackTrace();
		}
	}

Open in new window

0
 

Author Comment

by:fsyed
ID: 33755501
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!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33755812
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.
0
 

Author Closing Comment

by:fsyed
ID: 33759297
Thanks very much rockiroads for your solution, it worked!  I really appreciate your help.  Take care.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
servlet and mdb, jms error 1 57
Java basic valueOf question 1 33
Why is my $_POST not going to results page 10 36
Delete image(s) associated with record(s) 16 26
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question