将CSV文件导入SQLite

Question

我正试图将CSV文件导入我的SQLite数据库，但出现以下错误。

file.csv

EMP0003|sample|S@mple|SATO|TEST|destination |00008888|ST0001|EMP0003| East位置|0000001XABC---->这是我的csv文件，但它只能转到E'|'M'|'P'|'0'|'0'|'0''3'|'|'|'s'|'a'|'M'不是全部文本

String columns = "employee_num, username, password, firstname, lastname, address, contact, van, salesman_code, station_code, machine_id";
String str1 = "INSERT INTO " + tableName + " (" + columns + ") values(" ;
String str2 = ");";
mydb.beginTransaction();
while ((line = buffer.readLine()) !=null ) {
StringBuilder sb = new StringBuilder(str1);
String[] str = line.split("'|' ");
sb.append("'" + str[0] + "'|'");
sb.append(str[1]+ "'|'");
sb.append(str[2]+ "'|'");
sb.append(str[3]+ "'|'");
sb.append(str[4]+ "'|'");
sb.append(str[5]+ "'|'");
sb.append(str[6]+ "'|'");
sb.append(str[7]+ "'|'");
sb.append(str[8]+ "'|'");
sb.append(str[9]+ "'|'");
sb.append(str[10]+ "'|'");
sb.append(str[11].replace("'", "") + "'");
mydb.execSQL(sb.toString());
}

错误

Error : E/SQLiteLog: (1) near "'m'": syntax error
/ W/System.err: android.database.sqlite.SQLiteException: near "'m'": syntax error (code 1): , while compiling: INSERT INTO tbl_user (employee_num, username, password, firstname, lastname, address, contact, van, salesman_code, station_code, machine_id) values('','E'|'M'|'P'|'0'|'0'|'0'|'3'|'|'|'s'|'a'|'m'
/ W/System.err:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
/ W/System.err:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
/ W/System.err:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
/ W/System.err:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
/ W/System.err:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
/ W/System.err:     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
/ W/System.err:     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1677)
W/System.err:     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)

Answer 1

我相信您有很多问题。

1. 您没有正确拆分值，我认为您希望String[] str = line.split("\|")在|处拆分数据
2. SQL中的值之间使用了不正确的分隔符。每个值应与另一个值用逗号分隔
3. 你省略了右括号

但是

您可以使用SQLiteDatabaseinsert方便的方法来简化事务，该方法将代表您构建SQL。

考虑以下工作示例(至少插入有问题的行(：-

数据库帮助程序(最显著的是addUser方法(：-

public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
public static final String TABLENAME = "tbl_user";
public static final String USER_COL_EMPLOYEENUM = "employee_num";
public static final String USER_COL_USERNAME = "username";
public static final String USER_COL_PASSWORD = "password";
public static final String USER_COL_FIRSTNAME = "firstname";
public static final String USER_COL_LASTNAME = "lastname";
public static final String USER_COL_ADDRESS = "address";
public static final String USER_COL_CONTACT = "contact";
public static final String USER_COL_VAN = "van";
public static final String USER_COL_SALESMANCODE = "salesman_code";
public static final String USER_COL_STATIONCODE = "station_code";
public static final String USER_COL_MACHINEID = "machine_id";
SQLiteDatabase mDB;
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String user_crt = "CREATE TABLE If NOT EXISTS " + TABLENAME + "(" +
USER_COL_EMPLOYEENUM + " TEXT PRIMARY KEY," +
USER_COL_USERNAME + " TEXT UNIQUE," +
USER_COL_PASSWORD + " TEXT," +
USER_COL_FIRSTNAME + " TEXT," +
USER_COL_LASTNAME + " TEXT," +
USER_COL_ADDRESS + " TEXT," +
USER_COL_CONTACT + " TEXT," +
USER_COL_VAN + " TEXT," +
USER_COL_SALESMANCODE + " TEXT, " +
USER_COL_STATIONCODE + " TEXT," +
USER_COL_MACHINEID + " TEXT" +
")";
db.execSQL(user_crt);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long addUser(String employeeNumber,
String userName,
String password,
String firstname,
String lastname,
String address,
String contact,
String van,
String salesmanCode,
String stationCode,
String machineId) {
ContentValues cv = new ContentValues();
cv.put(USER_COL_EMPLOYEENUM,employeeNumber);
cv.put(USER_COL_USERNAME,userName);
cv.put(USER_COL_PASSWORD,password);
cv.put(USER_COL_FIRSTNAME,firstname);
cv.put(USER_COL_LASTNAME,lastname);
cv.put(USER_COL_ADDRESS,address);
cv.put(USER_COL_CONTACT,contact);
cv.put(USER_COL_VAN,van);
cv.put(USER_COL_SALESMANCODE,salesmanCode);
cv.put(USER_COL_STATIONCODE,stationCode);
cv.put(USER_COL_MACHINEID,machineId);
return mDB.insert(TABLENAME,null,cv);
}
public void logAll() {
Cursor csr = mDB.query(TABLENAME,null,null,null,null,null,null);
while (csr.moveToNext()) {
StringBuilder sb = new StringBuilder(" Row ").append(String.valueOf(csr.getPosition()));
for (int i =0; i < csr.getColumnCount(); i++) {
sb.append("ntColumn ").append(csr.getColumnName(i)).append(" has a value of ").append(csr.getString(i));
}
Log.d("TABLEINFO",sb.toString());
}
csr.close();
}
}

这是在一个活动中使用的：-

public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
String currentline = "EMP0003|sample|S@mple|SATO|TEST|destination|00008888|ST0001|EMP0003|East Location|0000001XABC";
String[] values = currentline.split("\|"); //<<<<<<<<<< SPLT at each |
// Only use if there are the 11 columns
if (values.length == 11) {
mDBHlpr.addUser(
values[0],
values[1],
values[2],
values[3],
values[4],
values[5],
values[6],
values[7],
values[8],
values[9],
values[10]
);
}
mDBHlpr.logAll(); //<<<<<<<<<< Write the data in the table to the log
}
}

当运行时，这将产生(这似乎是你想要的结果(：-

11-06 08:34:29.022 1574-1574/? D/TABLEINFO:  Row 0
Column employee_num has a value of EMP0003
Column username has a value of sample
Column password has a value of S@mple
Column firstname has a value of SATO
Column lastname has a value of TEST
Column address has a value of destination
Column contact has a value of 00008888
Column van has a value of ST0001
Column salesman_code has a value of EMP0003
Column station_code has a value of East Location
Column machine_id has a value of 0000001XABC

正如您所看到的，构建SQL不需要玩游戏，它是为您构建的。

• P.S.我建议员工编号应该是唯一的，因此将其作为PRIMARY KEY(这将停止添加重复项(

您可以使用(在包含addUser方法或等效方法后，根据您的列名(强烈建议您对列名采用常量，而不是硬编码(来调整以上内容(：-

while ((line = buffer.readLine()) !=null ) {
String[] values = line.split("\|"); //<<<<<<<<<< SPLIT at each |
// Only use if there are the 11 columns
if (values.length == 11) {
mydb.addUser(
values[0],
values[1],
values[2],
values[3],
values[4],
values[5],
values[6],
values[7],
values[8],
values[9],
values[10]
);
}
}

Answer 2

单引号'在这里是个问题。这些会生成无效的SQL查询。不应该编写原始SQL查询，而应该为此使用ContentValues或编译语句。

使用准备好的报表

String sql="INSERT INTO"+tableName+"(名称，描述(VALUES(？，？("；SQLiteStatement stmt=db.comileStatement(sql(；stmt.bindString(1，"value"(；stmt.execute((

使用内容值

ContentValues values=new ContentValues((；长返回值=0；values.put("_id"，id_here(；values.put("文本"，your_text_here(；db.insert("table"，null，values(；//请在此处查看文档中的正确参数