Generate csv from java using sqlcmd command
import java.io.IOException;
public class TestSQLCMD {
/**
* Generate CSV Using SQLCMD command
* @param _SQL
* @param downloadPath
* @return
* @throws IOException
*/
public static int exportCSVviaCommand(String _SQL, String downloadPath)
throws IOException {
String[] commandArray = {"sqlcmd",
"-S", "localhost,1433", //Server and Port
"-d", "my_server", // DB Name
"-U", "sa", // DB User
"-P", "password", // DB Password
"-Q", _SQL, // Query
"-k2", // Removes all control characters from the output
"-W", // Remove tabs between columns
"-f", "65001", // for UTF-8 format
"-h", "-1", //Remove headers
"-s,", // Separator is comma ','
"-o", downloadPath}; // "" --> output to file
Process process = Runtime.getRuntime().exec(commandArray);
/**
* causes the current thread to wait, if necessary, until the process
* represented by this Process object has terminated. This method
* returns immediately if the subprocess has already terminated. If the
* subprocess has not yet terminated, the calling thread will be blocked
* until the subprocess exits. Returns: the exit value of the process.
* By convention, 0 indicates normal termination.
*/
int i = -1;
try {
i = process.waitFor();
System.out.println(i);
} catch (InterruptedException e) {
e.printStackTrace();
}
return i;
}
public static void main(String[] args) {
String sql= " select * from Employee where DeptNo=1 and ID > 10 ";
try {
StringBuilder downloadPath = new StringBuilder();
downloadPath.append("D:\\downloads\\employee.csv");
exportCSVviaCommand(sql, downloadPath.toString());
} catch (IOException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
System.out.println("Done...........");
}
}
No comments:
Post a Comment