提问人：MPCatcher 提问时间：11/5/2023 最后编辑：SelVaziMPCatcher 更新时间：11/6/2023 访问量：69
从.txt中提取 SQL
Extracting SQL from a .txt
问：
我正在尝试从文本文件中提取值。文件内容如下：
客户
1, 'Aarón', 'Rivero', 'Gómez', 'Almería', 100
2, 'Adela', 'Salas', 'Díaz', 'Granada', 200
3, 'Adolfo', 'Rubio', 'Flores', 'Sevilla', NULL
4, 'Adrián', 'Suárez', NULL, 'Jaén', 300
5, 'Marcos', 'Loyola', 'Méndez', 'Almería', 200
6, 'María', 'Santana', 'Moreno', 'Cádiz', 100
7, 'Pilar', 'Ruiz', NULL, 'Sevilla', 300
8, 'Pepe', 'Ruiz', 'Santana', 'Huelva', 200
9, 'Guillermo', 'López', 'Gómez', 'Granada', 225
10, 'Daniel', 'Santana', 'Loyola', 'Sevilla', 125
我已经设法读取了文件并获取了行，但我无法指定以数字开头的确切行。我想拆分这些值并将它们添加到 SQL 表中。使用似乎不起作用，我没有想法。我尝试使用 a 来匹配该行，但它也不起作用。line.startsWith("^[0-9].*")matcher
答：
1赞
Oğuz Yeşil
11/5/2023
#1
这段代码对我有用
public class Main {
public static String[][] arrayedDatas(String data){
String[] datas = data.split(",");
int sizeOfData = datas.length/5;
String[][] arrayed = new String[sizeOfData][5];
int j = 0;
for(int i=0; i<arrayed.length; i++) {
arrayed[i][0] = datas[j].trim();
arrayed[i][1] = datas[j+1].trim();
arrayed[i][2] = datas[j+2].trim();
arrayed[i][3] = datas[j+3].trim();
arrayed[i][4] = datas[j+4].trim();
j += 5;
}
return arrayed;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
String datas = "1, 'Aarón', 'Rivero', 'Gómez', 'Almería', 100 2, 'Adela', 'Salas', 'Díaz', 'Granada', 200 3, 'Adolfo', 'Rubio', 'Flores', 'Sevilla', NULL 4, 'Adrián', 'Suárez', NULL, 'Jaén', 300 5, 'Marcos', 'Loyola', 'Méndez', 'Almería', 200 6, 'María', 'Santana', 'Moreno', 'Cádiz', 100 7, 'Pilar', 'Ruiz', NULL, 'Sevilla', 300 8, 'Pepe', 'Ruiz', 'Santana', 'Huelva', 200 9, 'Guillermo', 'López', 'Gómez', 'Granada', 225 10, 'Daniel', 'Santana', 'Loyola', 'Sevilla'";
String[][] arrayed = arrayedDatas(datas);
for(String[] a : arrayed) {
for(String b : a) {
System.out.println(b);
}
System.out.println("===========");
}
}
}
0赞
MPCatcher
11/5/2023
#2
public static void main(String[] args) throws IOException {
try {
String line;
String currentSection = null;
BufferedReader reader = new BufferedReader(new FileReader("C:\\Users\\nombr\\Downloads\\datos_ventas.txt"));
while ((line = reader.readLine()) != null) {
System.out.println(line);
if (line.startsWith("Cliente")) {
currentSection = "Clientes";
if (line.startsWith("^[0-9].*")){
String[] valores = line.split(",");
if (valores != null){
String valor1 = valores[0];
String valor2 = valores[1];
String valor3 = valores[2];
String valor4 = valores[3];
String valor5 = valores[4];
String valor6 = valores[5];
System.out.print("Id : " + valor1);
System.out.println("Nombre : " + valor2);
System.out.println("Apellidos : " + valor3 + " " + valor4);
System.out.println("Vivienda : " + valor5);
System.out.println("Precio : " + valor6);
}
}
}
}
2赞
Akina
11/6/2023
#3
使用下一个查询将文件加载到（临时）表中：
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csv_data.csv' -- data file
into table test -- destination table
character set utf8mb4 -- CSV file charset
fields terminated by ',' optionally enclosed by '''' -- columns data parsing parameters
lines terminated by '\r\n' -- lines terminator
(id, name1, name2, name2, city, @data) -- columns and variables list where the data will be loaded into
set data = nullif(@data, ' NULL'); -- additional processing loaded data
来自 CLI 的副本：
mysql> create table test (id int, name1 text, name2 text, name3 text, city text, data int);
Query OK, 0 rows affected (0.39 sec)
mysql> load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csv_data.csv' into table test character set utf8mb4 fields terminated by ',' optionally enclosed by '''' lines terminated by '\r\n' (id, name1, name2, name2, city, @data) set data = nullif(@data, ' NULL');
Query OK, 10 rows affected (0.07 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM test;
+------+--------------+------------+-------+-------------+------+
| id | name1 | name2 | name3 | city | data |
+------+--------------+------------+-------+-------------+------+
| 1 | 'Aarón' | 'Gómez' | NULL | 'Almería' | 100 |
| 2 | 'Adela' | 'Díaz' | NULL | 'Granada' | 200 |
| 3 | 'Adolfo' | 'Flores' | NULL | 'Sevilla' | NULL |
| 4 | 'Adrián' | NULL | NULL | 'Jaén' | 300 |
| 5 | 'Marcos' | 'Méndez' | NULL | 'Almería' | 200 |
| 6 | 'María' | 'Moreno' | NULL | 'Cádiz' | 100 |
| 7 | 'Pilar' | NULL | NULL | 'Sevilla' | 300 |
| 8 | 'Pepe' | 'Santana' | NULL | 'Huelva' | 200 |
| 9 | 'Guillermo' | 'Gómez' | NULL | 'Granada' | 225 |
| 10 | 'Daniel' | 'Loyola' | NULL | 'Sevilla' | 125 |
+------+--------------+------------+-------+-------------+------+
然后，您可以根据需要处理加载的数据。
0赞
g00se
11/6/2023
#4
您可以将其用作插入的强类型的基础，而不是将所有内容都视为 .您可以轻松地更改以读取文件而不是嵌入式输入：StringScanner
import java.util.Scanner;
public class SqlInserter {
final static String DATA = """
1, 'Aarón', 'Rivero', 'Gómez', 'Almería', 100
2, 'Adela', 'Salas', 'Díaz', 'Granada', 200
3, 'Adolfo', 'Rubio', 'Flores', 'Sevilla', NULL
4, 'Adrián', 'Suárez', NULL, 'Jaén', 300
5, 'Marcos', 'Loyola', 'Méndez', 'Almería', 200
6, 'María', 'Santana', 'Moreno', 'Cádiz', 100
7, 'Pilar', 'Ruiz', NULL, 'Sevilla', 300
8, 'Pepe', 'Ruiz', 'Santana', 'Huelva', 200
9, 'Guillermo', 'López', 'Gómez', 'Granada', 225
10, 'Daniel', 'Santana', 'Loyola', 'Sevilla', 125
""";
record Person (Integer id, String name1, String name2, String name3, String city, Integer something) {
public Person(String[] atoms) {
this("NULL".equals(atoms[0])? null : Integer.valueOf(atoms[0]),
atoms[1],
atoms[2],
atoms[3],
atoms[4],
"NULL".equals(atoms[5])? null : Integer.valueOf(atoms[5]));
}
}
public static void main(String[] args) throws Exception {
try (Scanner s = new Scanner(DATA).useDelimiter("\\R")) {
s.tokens()
.map(line -> line.replaceAll("'", ""))
.map(line ->line.split("\\s*,\\s*"))
.map(a -> new Person(a))
.forEach(System.out::println);
}
}
}
评论
line.matches(^[0-9].*)linematches