Syntax Error while Importing CSV into MariaDB

Question

Afternoon,

I'm trying to generate a new migration to my DB to import new sets of data with a CSV file sadly I'm facing a few syntax error that I don't seem to find awnsers to online.

I'm using TypeOrm to generate my migrations:

import {MigrationInterface, QueryRunner} from 'typeorm';

export class UploadTechnicalInformationsRectangular1604481796356 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query('LOAD DATA LOCAL INFILE `../data/RSH-rectangular.csv` INTO TABLE `technical-information` FIELDS TERMINATED BY `;` ENCLOSED BY `"` LINES TERMINATED BY `\r\n`',undefined);
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
    }

}

Where I try to load a file stored localy on my project and simply inject it into my running DB Table on the background I get the following error:

QueryFailedError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '../data/RSH-rectangular.csv INTO TABLE technical-information FIELDS TERMINA...' at line 1

I'm running out of ideas what's wrong with my syntax I've tried removing the backquotes, replacing with single quotes/double quotes.

The CSV columns names are identical to my table columns names. The only difference on the data is that depending on the type of data set I import I will have data in some columns instead of others. But I'm not even there yet ^^

I've also tried different CSV Format while exporting it from Excel (MS-DOS, semi-colon, comma) all seem to react the same way so far.

Gods of the webs, show me the way 🙌🏼

Final anwser based on @bill-karwin solution:

await queryRunner.query("LOAD DATA LOCAL INFILE './src/data/CSH.csv' INTO TABLE `technical-information` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS  (type, outsideDiameter, thickness, massUnitLength, crossSectionalArea, secondMomentArea, gyrationRadius, elasticSectionModulus, plasticSectionModulus, torsionalIniertiaConstant, torsionalModulusConstant, superficialAreaMeterLength, availibilityUpon, dimensionOutsideValidRange)", undefined);

Answer 1

In MySQL:

• Back-ticks ( ` ) are for delimiting identifiers, such as column names or table names.

• Single-quotes ( ' ) are for delimiting values such as string literals. Actually, only string literals, though datetime values and number values are commonly written as strings.

• Double-quotes ( " ) are nonstandard in MySQL. These may act either like back-ticks (to delimit identifers) or like single-quotes (to delimit strings), depending on your configured sql_mode.

In your query, you are using quotes incorrectly. You have back-ticks around string values, which makes MySQL treat them as identifiers. Don't do that.

But you do need back-ticks around your table name technical-information because it contains a hyphen character. This requires the identifier to be delimited.

The following may work:

await queryRunner.query("""
    LOAD DATA LOCAL INFILE '../data/RSH-rectangular.csv' 
    INTO TABLE `technical-information` 
    FIELDS TERMINATED BY ';' ENCLOSED BY '"' 
    LINES TERMINATED BY '\r\n'""", undefined);

Notice the use of single-quotes to delimit the strings (including the input file's name, which is a string to MySQL). Also the use of back-ticks to delimit the table name.

In Python, use the triple-double-quotes for a long string that may contain other types of quotes. Otherwise you end up having to escape quotes and it gets confusing.

---

If the fields in your CSV file don't match the columns of your SQL table exactly, you can override the mapping by listing the columns to import into, corresponding to the fields of the input file. This way you can customize the order of columns. If there are columns in the input that don't need to be imported, then name a variable instead of a column. If there is a column of your table that isn't represented by the input data, then use the SET clause to set it.

Example:

LOAD DATA LOCAL INFILE '../data/RSH-rectangular.csv' 
INTO TABLE `technical-information` 
FIELDS TERMINATED BY ';' ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(col1, col2, @dummy, col3)
SET col4 = NOW();

Read the section on "Input Preprocessing" in https://dev.mysql.com/doc/refman/8.0/en/load-data.html for more details. All this is covered in the documentation.

Answer 2

You have backticks where they don't belong.

Backticks denote MySQL identifiers (database, table, column, alias, etc), not text strings.

Try ' characters instead for your text strings. You have backticks in many places in your query. Here's what to do

1. remove all backticks from your query, replacing them with ' quotes.
2. restore the backticks so they surround only MySQL identifiers (in your query the name of your table).
3. Figure out how to use your entire query as a text string in your programming language. Wrapping it in `"`` double quotes instead of single quotes may work for you.

This, not debugged, might do it for you.

const q = 
   "LOAD DATA LOCAL INFILE '../data/RSH-rectangular.csv' \
        INTO TABLE `technical-information` \
        FIELDS TERMINATED BY ';' \
        ENCLOSED BY '\"' \
        LINES TERMINATED BY '\r\n';";
await queryRunner.query(q,undefined);

If the first line of your CSV file contains your column names add IGNORE 1 ROWS to your LOAD commands.

In Javascript, backticks denote a template string. You don't need a template string, just an ordinary string. So delimit it with " quotes and escape the newlines in it by putting \ at the end of every line.

In MySQL, backticks denote MySQL identifiers, and single quotes denote text strings.

SQL is one language and Javascript another. You're embedding SQL commands in your Javascript. Getting the delimiters and escapes right is a notorious pain in the xxx neck. Use console.log() liberally while you're debugging.