I'm trying to extract values from a text file. The content of the file is as follows:
Cliente
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
I've managed to read the file and get the lines, but I can't specify the exact lines that start with digits. I want to split the values and add them to an SQL table. Using line.startsWith("^[0-9].*") doesn't seem to work, and I'm out of ideas. I tried using a matcher to match the line, and it doesn't work either.
CodePudding user response：
Load your file into (temporary) table with the next query:
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
A copy from 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 |
------ -------------- ------------ ------- ------------- ------
Then you can process the loaded data as you need.
CodePudding user response：
this code worked for me
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("===========");
}
}
}
CodePudding user response：
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);
}
}
}
}
CodePudding user response：
You can use this as a basis for strong typing for your insert instead of treating everything as a String. You can easily change the Scanner to read a file instead of the embedded input:
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);
}
}
}