Неожиданный токен (!) Не импортируется CSV в MySQL

@Рафик · Регистрация: 25.06.2013

Author24 — интернет-сервис помощи студентам

Всем привет.
Долго мучаюсь над проблемой, решить так и не смог, обращаюсь к вам за помощью.

Имеются некие CSV файлы, которые я пытаюсь импортировать через PhpMyAdmin в MySQL базу данных.

Некоторые файлы импортируются успешно, будь там хоть 600 строк.
Допустим даже если в файле 200 строк, 47 из них проходят успешно, а 48-я вылетает с ошибкой "Несоответствие количества столбцов в CSV данных на строке XXX". Хотя количество столбцов я уже проверил вручную десятки раз.

Если на сообщении об ошибке нажать [Изменить], открывается окно для ручного ввода запроса. Так вот, там я заметил, что при импорте некоторые запросы объединяются в один, например:

SQL

INSERT INTO `my_table`
VALUES(
    '1',
    '2',
    '3',
)
INSERT INTO `my_table`
VALUES(
    '4',
    '5',
    '6',
)

Между запросами phpmyadmin не поставил ";", вероятно поэтому ошибка. Как такое может быть? Ведь несколько десятков запросов до этого проходит успешно. Никакими особыми эти "ошибочные" строки не являются и никаких отклонений там нет. Если эти строки в CSV файле удалить, проблема точно также появляется с другой строкой в этом же месте.

@nikolay1982 · 27.10.2017, 13:22

Я смотрю, id и date в том файле - пустые строки. Так должно быть?

@Рафик · 27.10.2017, 13:28 **[ТС]**

Да. id там автоинкремент, а дата либо пустая, либо везде одинаковая, формата "2017-10-25 18:00:00"

@nikolay1982 · 27.10.2017, 13:33

Имя таблицы не четко видно на скриншоте. Пишу генерацию sql файла с запросами.

@Рафик · 27.10.2017, 13:43 **[ТС]**

Имя таблицы dle_post

@nikolay1982 · 27.10.2017, 14:50

Работает. Это парсер переводит CSV в файл ".sql"

Java

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
 
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
 
import java.io.InputStreamReader;
import java.io.FileInputStream;
 
import java.io.OutputStreamWriter;
import java.io.FileOutputStream;
 
 
 
public class CSVUtils {
 
    private static final char DEFAULT_SEPARATOR = ',';
    private static final char DEFAULT_QUOTE = '"';
    
    static String  sqlRealEscapeString(String s){
        int le=s.length();
        StringBuffer ret=new StringBuffer();
        
        for (int i=0;i<le;i++){
            char c=s.charAt(i);
            switch(c){
                case '\\':
                    ret.append(c);
                    ret.append(c);
                    break;
                case '\0':
                    ret.append("\\0");
                    break;
                case '\"':
                    ret.append("\\\"");
                    break;
                case '\'':
                    ret.append('\\');
                    ret.append('\'');
                    break;
                case '\n':
                    ret.append('\\');
                    ret.append('n');
                    break;
                case '\r':
                    ret.append('\\');
                    ret.append('r');
                    break;
                case (char)0x1a:
                    ret.append('\\');
                    ret.append("x1a");
                    break;                  
                default:
                    ret.append(c);              
            }
        }
        return ret.toString();
    }
    
    
    static final String myTableFileds[]={
        "id",
        "autor",
        "date",
        "short_story",
        "full_story",
        "xfields",
        "title",
        "descr",
        "keywords",
        "category",
        "alt_name",
        "comm_num",
        "allow_comm",
        "allow_main",
        "approve",
        "fixed",
        "allow_br",
        "symbol",
        "tags",
        "metatitle",
    };
    static String myTableName="dle_post";
    static String getQueryData(String val, boolean isLast){
        String ret= "\'" +sqlRealEscapeString(val)+"\'";
        if (!isLast){
            ret+=',';
        }
        return ret;
    }
    static String genInsertQuery(List<String> row) throws IllegalArgumentException 
    {
        int row_size=row.size();
        if (row_size!=myTableFileds.length){
            throw new IllegalArgumentException("Invalid columns count:"+row_size+", but expected:"+myTableFileds.length);
        }
        String sql="INSERT INTO "+myTableName+"\nVALUES (";
        
        int hi_index=row_size-1;
        for (int i=0;i<row_size;i++){
            boolean isLast=(i==hi_index);
            sql+=getQueryData(
                    row.get(i),
                    isLast
                );
        }
        sql+="\n);\n";
        return sql;
    }
    static String toCsvShortString(List<String> row){
        String r="";
        for (String s: row){
            int le=s.length();
            if (le>=12) {
                le=12;
            }
            //if (le>=1)le--;
            
            String s2=s.substring(0,le);
            r+="\""+s2+"\",";
        }
        return r;
    };
    public static void main(String[] args) throws Exception {
 
        String csvFile = "C:\\files\\credit_cp.csv";//"/Users/mkyong/csv/country2.csv";
        String outSqlFile=csvFile+"_.sql";
        System.out.println("try parse:"+csvFile);
        
        if (true){
         try (BufferedReader br = new BufferedReader(//new FileReader(csvFile))
            new InputStreamReader(new FileInputStream(csvFile),"UTF-8"))
         ) {
          int linenum=0;
          String line=null;
          List<String> outSql=new ArrayList<String>();
          while ((line = br.readLine()) != null) {
                linenum++;
                // use comma as separator
                //String[] country = line.split(cvsSplitBy);
 
                System.out.println("line:"+linenum);
                List<String> row = parseLine(line);
                int columns=row.size();
                String s2=toCsvShortString(row);
                System.out.println("line:"+linenum+" columns:"+columns+" row:"+row);
                System.out.println("short_row:"+s2);
                String sql_str=genInsertQuery(row);
                outSql.add(sql_str);
            }
            OutputStreamWriter fow=new OutputStreamWriter(new FileOutputStream(outSqlFile),"UTF-8");
            for (String s:outSql){
                fow.write(s,0,s.length());
            }
            fow.flush();
            fow.close();
 
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        }
        /*Scanner scanner = new Scanner(new File(csvFile));
        int linenum=0;
        while (scanner.hasNext()) {
            linenum++;
            List<String> line = parseLine(scanner.nextLine());
            
            int columns=line.size();
            System.out.println("line:"+linenum+" columns:"+columns);
            //System.out.println("Country [id= " + line.get(0) + ", code= " + line.get(1) + " , name=" + line.get(2) + "]");
        }
        scanner.close();
        System.out.println("ok parsed:"+csvFile);
        */
 
    }
 
    public static List<String> parseLine(String cvsLine) {
        return parseLine(cvsLine, DEFAULT_SEPARATOR, DEFAULT_QUOTE);
    }
 
    public static List<String> parseLine(String cvsLine, char separators) {
        return parseLine(cvsLine, separators, DEFAULT_QUOTE);
    }
 
    public static List<String> parseLine(String cvsLine, char separators, char customQuote) {
 
        List<String> result = new ArrayList<>();
 
        //if empty, return!
        if (cvsLine == null && cvsLine.isEmpty()) {
            return result;
        }
 
        if (customQuote == ' ') {
            customQuote = DEFAULT_QUOTE;
        }
 
        if (separators == ' ') {
            separators = DEFAULT_SEPARATOR;
        }
 
        StringBuffer curVal = new StringBuffer();
        boolean inQuotes = false;
        boolean startCollectChar = false;
        boolean doubleQuotesInColumn = false;
 
        char[] chars = cvsLine.toCharArray();
 
        for (char ch : chars) {
 
            if (inQuotes) {
                startCollectChar = true;
                if (ch == customQuote) {
                    inQuotes = false;
                    doubleQuotesInColumn = false;
                } else {
 
                    //Fixed : allow "" in custom quote enclosed
                    if (ch == '\"') {
                        if (!doubleQuotesInColumn) {
                            curVal.append(ch);
                            doubleQuotesInColumn = true;
                        }
                    } else {
                        curVal.append(ch);
                    }
 
                }
            } else {
                if (ch == customQuote) {
 
                    inQuotes = true;
 
                    //Fixed : allow "" in empty quote enclosed
                    if (chars[0] != '"' && customQuote == '\"') {
                        curVal.append('"');
                    }
 
                    //double quotes in column will hit this!
                    if (startCollectChar) {
                        curVal.append('"');
                    }
 
                } else if (ch == separators) {
 
                    result.add(curVal.toString());
 
                    curVal = new StringBuffer();
                    startCollectChar = false;
 
                } else if (ch == '\r') {
                    //ignore LF characters
                    continue;
                } else if (ch == '\n') {
                    //the end, break!
                    break;
                } else {
                    curVal.append(ch);
                }
            }
 
        }
 
        result.add(curVal.toString());
 
        return result;
    }
 
}

@Рафик · 27.10.2017, 15:04 **[ТС]**

Эм... хорошо. А как мне его применить?)

@nikolay1982 · 27.10.2017, 15:05

Новая версия (Command Line Interface).
Использование:
Компиляция:

Bash

1	javac CSVUtils.java

Перевод CSV в SQL файлы:

Bash

1	java -cp . CSVUtils C:\files\credit_cp.csv

Вывод в консоли:
try parse:C:\files\credit_cp.csv
OK saved to C:\files\credit_cp.csv_.sql CSV lines:10

Java

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
 
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
 
import java.io.InputStreamReader;
import java.io.FileInputStream;
 
import java.io.OutputStreamWriter;
import java.io.FileOutputStream;
 
 
 
public class CSVUtils {
 
    private static final char DEFAULT_SEPARATOR = ',';
    private static final char DEFAULT_QUOTE = '"';
    
    static String  sqlRealEscapeString(String s){
        int le=s.length();
        StringBuffer ret=new StringBuffer();
        
        for (int i=0;i<le;i++){
            char c=s.charAt(i);
            switch(c){
                case '\\':
                    ret.append(c);
                    ret.append(c);
                    break;
                case '\0':
                    ret.append("\\0");
                    break;
                case '\"':
                    ret.append("\\\"");
                    break;
                case '\'':
                    ret.append('\\');
                    ret.append('\'');
                    break;
                case '\n':
                    ret.append('\\');
                    ret.append('n');
                    break;
                case '\r':
                    ret.append('\\');
                    ret.append('r');
                    break;
                case (char)0x1a:
                    ret.append('\\');
                    ret.append("x1a");
                    break;                  
                default:
                    ret.append(c);              
            }
        }
        return ret.toString();
    }
    
    
    static final String myTableFileds[]={
        "id",
        "autor",
        "date",
        "short_story",
        "full_story",
        "xfields",
        "title",
        "descr",
        "keywords",
        "category",
        "alt_name",
        "comm_num",
        "allow_comm",
        "allow_main",
        "approve",
        "fixed",
        "allow_br",
        "symbol",
        "tags",
        "metatitle",
    };
    static String myTableName="dle_post";
    static String getQueryData(String val, boolean isLast){
        String ret= "\'" +sqlRealEscapeString(val)+"\'";
        if (!isLast){
            ret+=',';
        }
        return ret;
    }
    static String genInsertQuery(List<String> row) throws IllegalArgumentException 
    {
        int row_size=row.size();
        if (row_size!=myTableFileds.length){
            throw new IllegalArgumentException("Invalid columns count:"+row_size+", but expected:"+myTableFileds.length);
        }
        String sql="INSERT INTO "+myTableName+"\nVALUES (";
        
        int hi_index=row_size-1;
        for (int i=0;i<row_size;i++){
            boolean isLast=(i==hi_index);
            sql+=getQueryData(
                    row.get(i),
                    isLast
                );
        }
        sql+="\n);\n";
        return sql;
    }
    static String toCsvShortString(List<String> row){
        String r="";
        for (String s: row){
            int le=s.length();
            if (le>=12) {
                le=12;
            }
            //if (le>=1)le--;
            
            String s2=s.substring(0,le);
            r+="\""+s2+"\",";
        }
        return r;
    };
    public static void main(String[] args) throws Exception {
        if (args.length==0) {
            System.out.println("\nUsage: CSVUtils <CSV file>");
            return;
        }
        String csvFile = args[0];//"C:\\files\\credit_cp.csv";//"/Users/mkyong/csv/country2.csv";
        String outSqlFile=csvFile+"_.sql";
        System.out.println("try parse:"+csvFile);
        
        if (true){
         try (BufferedReader br = new BufferedReader(//new FileReader(csvFile))
            new InputStreamReader(new FileInputStream(csvFile),"UTF-8"))
         ) {
          int linenum=0;
          String line=null;
          List<String> outSql=new ArrayList<String>();
          while ((line = br.readLine()) != null) {
                linenum++;
                // use comma as separator
                //String[] country = line.split(cvsSplitBy);
 
                //System.out.println("line:"+linenum);
                List<String> row = parseLine(line);
                int columns=row.size();
                String s2=toCsvShortString(row);
                //System.out.println("line:"+linenum+" columns:"+columns+" row:"+row);
                //System.out.println("short_row:"+s2);
                String sql_str=genInsertQuery(row);
                outSql.add(sql_str);
            }
            OutputStreamWriter fow=new OutputStreamWriter(new FileOutputStream(outSqlFile),"UTF-8");
            for (String s:outSql){
                fow.write(s,0,s.length());
            }
            fow.flush();
            fow.close();
            System.out.println("OK saved to "+outSqlFile+" CSV lines:"+outSql.size());
 
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        }
        /*Scanner scanner = new Scanner(new File(csvFile));
        int linenum=0;
        while (scanner.hasNext()) {
            linenum++;
            List<String> line = parseLine(scanner.nextLine());
            
            int columns=line.size();
            System.out.println("line:"+linenum+" columns:"+columns);
            //System.out.println("Country [id= " + line.get(0) + ", code= " + line.get(1) + " , name=" + line.get(2) + "]");
        }
        scanner.close();
        System.out.println("ok parsed:"+csvFile);
        */
 
    }
 
    public static List<String> parseLine(String cvsLine) {
        return parseLine(cvsLine, DEFAULT_SEPARATOR, DEFAULT_QUOTE);
    }
 
    public static List<String> parseLine(String cvsLine, char separators) {
        return parseLine(cvsLine, separators, DEFAULT_QUOTE);
    }
 
    public static List<String> parseLine(String cvsLine, char separators, char customQuote) {
 
        List<String> result = new ArrayList<>();
 
        //if empty, return!
        if (cvsLine == null && cvsLine.isEmpty()) {
            return result;
        }
 
        if (customQuote == ' ') {
            customQuote = DEFAULT_QUOTE;
        }
 
        if (separators == ' ') {
            separators = DEFAULT_SEPARATOR;
        }
 
        StringBuffer curVal = new StringBuffer();
        boolean inQuotes = false;
        boolean startCollectChar = false;
        boolean doubleQuotesInColumn = false;
 
        char[] chars = cvsLine.toCharArray();
 
        for (char ch : chars) {
 
            if (inQuotes) {
                startCollectChar = true;
                if (ch == customQuote) {
                    inQuotes = false;
                    doubleQuotesInColumn = false;
                } else {
 
                    //Fixed : allow "" in custom quote enclosed
                    if (ch == '\"') {
                        if (!doubleQuotesInColumn) {
                            curVal.append(ch);
                            doubleQuotesInColumn = true;
                        }
                    } else {
                        curVal.append(ch);
                    }
 
                }
            } else {
                if (ch == customQuote) {
 
                    inQuotes = true;
 
                    //Fixed : allow "" in empty quote enclosed
                    if (chars[0] != '"' && customQuote == '\"') {
                        curVal.append('"');
                    }
 
                    //double quotes in column will hit this!
                    if (startCollectChar) {
                        curVal.append('"');
                    }
 
                } else if (ch == separators) {
 
                    result.add(curVal.toString());
 
                    curVal = new StringBuffer();
                    startCollectChar = false;
 
                } else if (ch == '\r') {
                    //ignore LF characters
                    continue;
                } else if (ch == '\n') {
                    //the end, break!
                    break;
                } else {
                    curVal.append(ch);
                }
            }
 
        }
 
        result.add(curVal.toString());
 
        return result;
    }
 
}

@Рафик · 27.10.2017, 15:08 **[ТС]**

Подскажите пожалуйста чайнику, что мне делать с этим парсером? Как его запустить?

@nikolay1982 · 27.10.2017, 15:30

Вы не умете работать с консолью?
Пуск->Выполнить->cmd
Лучше использовать ConEmu вместо cmd
Надо иметь JDK(Java SDK) или JRE.
На почту кинул бинарник.
В консоли перейдите в какую-нибудь рабочую директорию, без русских букв в пути.
cd C:\myWork
Киньте туда бинарник из архива (в эту папку)
Далее запускаете с нужными параметрами:

Bash

1	java -cp . CSVUtils Полный_путь_к_вашему_CSV_файлу

И все ! И будет создан: Полный_путь_к_вашему_CSV_файлу_.sql
Этот sql файл использовать через PHPMyAdmin или mysql CLI.

Добавлено через 4 минуты
Но эта программа будет работать только для таблицы dle_post из 20 полей.
Чтобы с другими таблицами - нудно править ".java" файл.

@Рафик · 27.10.2017, 15:40 **[ТС]**

Допустим, кидаю файлы "CSVUtils.class", "CSVUtils.java" и мой CSV файл "text.csv" в папку "C:\test"
Пишу в командной строке: "java -cp . CSVUtil C:\test\test.csv", мне выдаёт "Error: Could not find or load main class CSVUtil".
JRE установлен.

@nikolay1982 · 27.10.2017, 15:44

Опечатка у вас:
java -cp . CSVUtil C:\test\test.csv
Должно быть:
java -cp . CSVUtils C:\test\test.csv

буква s в конце имени класса

@Рафик · 27.10.2017, 15:49 **[ТС]**

Да, это помогло.
Файл на 10 строк успешно обработался.
А вот, когда попытался закинуть файл 237 строк, выдало ошибку:

Bash

try parse:C:\test\test.csv
java.lang.IllegalArgumentException: Invalid columns count:49, but expected:20
        at CSVUtils.genInsertQuery(CSVUtils.java:101)
        at CSVUtils.main(CSVUtils.java:157)

@nikolay1982 · 27.10.2017, 15:56

Данная программа работает только на 20 колонок.
Этот CSV содержит больше колонок? может это другая таблица, не dle_post?
можете на почту кинуть файл, я посмотрю.

@Рафик · 27.10.2017, 15:58 **[ТС]**

Файл отправил на почту.
Этот CSV файл также рассчитан на 20 колонок. В ту же таблицу.

@nikolay1982 · 27.10.2017, 16:26

Присланный CSV не корретен начиная с 5 -й строки.
В строках есть типа : "<p class="for_ban">
А ведь кавычки должны дублироваться.
Каким способом вы получили этот файл?

@Рафик · 27.10.2017, 16:43 **[ТС]**

<p class="for_ban"> есть в каждой строке по нескольку раз.
Этот файл генерируется специальной программой, собственно конкретно "<p class="for_ban">" прописан почти вручную.
В коротком файле из 10 строк вроде тоже были эти "<p class="for_ban">"

Отправил вам на почту другой такой же файл, где вместо "," знак |, а вместо " знак ¤

Добавлено через 10 минут
В 5-й строке я заметил лишнюю кавычку, в тексте

Bash

1	регистрации права. заявке."></p><h3>

, которая непонятно откуда там взялась. Может в тексте ещё где-то есть такие лишние кавычки. Поэтому я и старался использовать символы, которые не должны были появляться в тексте случайно.
p.s. я написал вам в лс.

@Рафик · 31.10.2017, 09:26 **[ТС]**

Проблема решена.
В итоге nikolay1982 создал программу, которая исправляет некорректные CSV файлы.
Спасибо.

@nikolay1982 127 / 126 / 59 Регистрация: 22.01.2014 Сообщений: 462
	27.10.2017, 13:22	21
	Я смотрю, id и date в том файле - пустые строки. Так должно быть? 1

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 15:58 [ТС]	34
	Файл отправил на почту. Этот CSV файл также рассчитан на 20 колонок. В ту же таблицу. 0

@nikolay1982 127 / 126 / 59 Регистрация: 22.01.2014 Сообщений: 462
	27.10.2017, 16:26	35
	Присланный CSV не корретен начиная с 5 -й строки. В строках есть типа : "<p class="for_ban"> А ведь кавычки должны дублироваться. Каким способом вы получили этот файл? 1

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 13:28 [ТС]	22
	Да. id там автоинкремент, а дата либо пустая, либо везде одинаковая, формата "2017-10-25 18:00:00" 0

@nikolay1982 127 / 126 / 59 Регистрация: 22.01.2014 Сообщений: 462
	27.10.2017, 13:33	23
	Имя таблицы не четко видно на скриншоте. Пишу генерацию sql файла с запросами. 0

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 13:43 [ТС]	24
	Имя таблицы dle_post 0

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 15:04 [ТС]	26
	Эм... хорошо. А как мне его применить?) 0

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 15:08 [ТС]	28
	Подскажите пожалуйста чайнику, что мне делать с этим парсером? Как его запустить? 0

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	27.10.2017, 15:40 [ТС]	30
	Допустим, кидаю файлы "CSVUtils.class", "CSVUtils.java" и мой CSV файл "text.csv" в папку "C:\test" Пишу в командной строке: "java -cp . CSVUtil C:\test\test.csv", мне выдаёт "Error: Could not find or load main class CSVUtil". JRE установлен. 0

@nikolay1982 127 / 126 / 59 Регистрация: 22.01.2014 Сообщений: 462
	27.10.2017, 15:44	31
	Опечатка у вас: java -cp . CSVUtil C:\test\test.csv Должно быть: java -cp . CSVUtils C:\test\test.csv буква s в конце имени класса 1

@nikolay1982 127 / 126 / 59 Регистрация: 22.01.2014 Сообщений: 462
	27.10.2017, 15:56	33
	Данная программа работает только на 20 колонок. Этот CSV содержит больше колонок? может это другая таблица, не dle_post? можете на почту кинуть файл, я посмотрю. 1

@Рафик 2 / 2 / 1 Регистрация: 25.06.2013 Сообщений: 279
	31.10.2017, 09:26 [ТС]	37
	Проблема решена. В итоге nikolay1982 создал программу, которая исправляет некорректные CSV файлы. Спасибо. 0