我对Java还是很陌生,并且一直在尝试读取一个非常困难的.txt文件并将其输入到我的MySQL DB中。
对我来说,文件有一些非常奇怪的定界规则。定界符似乎全是逗号,但其他部分则毫无意义。这是一些例子:
" "," "," "," "," "
" ",,,,,,," "
" ",0.00," "
" ",," ",," ",," "
我所知道的是,所有包含字母的字段都是正常,"text",
格式。
所有只有数字的列都将遵循此格式:,0.00,
除了第一列遵循常规格式外"123456789",
然后,没有数据的任何内容将在,,
或之间交替," ",
我已经能够使用java.sql.Statement正确读取程序,但是我需要它才能与java.sql.PreparedStatement一起使用
我可以让它只与选定的几列一起工作,但是我需要它与100多个列一起工作,并且某些字段包含逗号,例如 "Some Company, LLC"
这是我目前拥有的代码,但是我对下一步的工作一无所知。
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
public class AccountTest {
public static void main(String[] args) throws Exception {
//Declare DB settings
String dbName = "jdbc:mysql://localhost:3306/local";
String userName = "root";
String password = "";
String fileName = "file.txt";
String psQuery = "insert into accounttest"
+ "(account,account_name,address_1,address_2,address_3) values"
+ "(?,?,?,?,?)";
Connection connect = null;
PreparedStatement statement = null;
String account = null;
String accountName = null;
String address1 = null;
String address2 =null;
String address3 = null;
//Load JDBC Driver
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e) {
System.out.println("JDBC driver not found.");
e.printStackTrace();
return;
}
//Attempt connection
try {
connect = DriverManager.getConnection(dbName,userName,password);
}
catch (SQLException e) {
System.out.println("E1: Connection Failed.");
e.printStackTrace();
return;
}
//Verify connection
if (connect != null) {
System.out.println("Connection successful.");
}
else {
System.out.println("E2: Connection Failed.");
}
BufferedReader bReader = new BufferedReader(new FileReader(fileName));
String line;
//import file into mysql DB
try {
//Looping the read block until all lines in the file are read.
while ((line = bReader.readLine()) != null) {
//Splitting the content of comma delimited file
String data[] = line.split("\",\"");
//Renaming array items for ease of use
account = data[0];
accountName = data[1];
address1 = data[2];
address2 = data[3];
address3 = data[4];
// removing double quotes so they do not get put into the db
account = account.replaceAll("\"", "");
accountName = accountName.replaceAll("\"", "");
address1 = address1.replaceAll("\"", "");
address2 = address2.replaceAll("\"", "");
address3 = address3.replaceAll("\"", "");
//putting data into database
statement = connect.prepareStatement(psQuery);
statement.setString(1, account);
statement.setString(2, accountName);
statement.setString(3, address1);
statement.setString(4, address2);
statement.setString(5, address3);
statement.executeUpdate();
}
}
catch (Exception e) {
e.printStackTrace();
statement = null;
}
finally {
bReader.close();
}
}
}
抱歉,如果格式化不正确,我仍在学习,经过几天的忙碌试图找出答案后,我没有再打扰它了。
我的问题是,使用这样混乱的文件是否可能发生类似的事情?如果是这样,我该怎么做呢?另外,我对准备好的语句并不完全熟悉,我是否必须声明每一列还是有一种更简单的方法?
在此先感谢您的帮助。
编辑:为了澄清什么,我需要的是我需要上传一个txt文件到MySQL数据库,我需要一种方法来读和拆分(除非有更好的方法)的基础上无论是数据","
,,,,,
,,0.00,
并且仍然保持场一起有现场逗号Some Company, LLC
。我需要使用100多个列来执行此操作,并且文件的范围从3000到6000行不等。需要将其作为准备好的语句来执行。我不确定这是否可行,但我感谢任何人对此事的任何投入。
EDIT2:由于rpc1,我能够弄清楚如何整理出混乱的文件。而不是String data[] = line.split("\",\"");
我曾经用过,String data[] = line.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)");
我仍然不得不写出每个变量以将其链接到,data[]
然后statement.setString
为每列写出每个变量,并为每列写出一个变量,replaceALL("\"", "");
但是我使它起作用了,我找不到使用预备语句的另一种方法。谢谢你的帮助!
通过一点点代码,我就能找出我遇到的两个问题。再次感谢您的所有帮助!
for (String line = bReader.readLine(); line != null; line = bReader.readLine()) {
//Splitting the content of comma delimited file
String data[] = line.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)");
//Iterating through the file and updating the table.
statement = connect.prepareStatement(psQuery);
for (int i =0; i < data.length;i++) {
temp = data[i];
temp = temp.replaceAll("\"", "");
statement.setString(i+1, temp);
}
statement.executeUpdate();
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句