MySQL fields terminated by tab

Question

I am trying to upload a tab delimitted file with MySQL. I want a query something likes this: LOAD DATA LOCAL INFILE 'file' INTO TABLE tbl FIELDS TERMINATED BY 'TAB' Is there something I can subsitute for TAB to make this work?

Superstructure

Answer 1

have you tried '\t' the escape sequence + "T" is considered tab... haven't tried, but might be what you need

Ironmonger

That's the one. MySQL uses the standard 'C' escape sequences for special characters. – Frazzle

still doesn't work... I should be able to use FIELDS TERMINATED BY '/t' or FIELDS TERMINATED BY 'T'? – Superstructure

FIELDS TERMINATED BY '\t' is what you want to use , not FIELDS TERMINATED BY '/t'. – Sevier

Answer 2

Just tried to find the answer to this question myself to save re-saving my file with commas separating instead of tabs...

From an old MySQL reference manual, a long way down the page, you can find that TAB is the default separater for files loaded using LOAD DATA on MySQL.

See: http://dev.mysql.com/doc/refman/4.1/en/load-data.html

I just loaded a CSV file in this way into MySQL5.1.

BW

Woke

I cannot find any such statement on this page you've linked (target document may have changed). Regardless, this comment and/or link should probably be added as a comment on the previously provided answer about how to explicitly use tab as the delimiter, rather than a new answer in itself. – Gab

The statement is on the page. It says: If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this: FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' – Marra

Answer 3

fields terminated by '\t'

Try this one

Note :

Field and Line Handling

For both the LOAD DATA and SELECT ... INTO OUTFILE statements, the syntax of the FIELDS and LINES clauses is the same. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them. Arguments to these clauses are permitted to contain only ASCII characters.

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

LINES TERMINATED BY '\n' STARTING BY ''

Backslash is the MySQL escape character within strings in SQL statements. Thus, to specify a literal backslash, you must specify two backslashes for the value to be interpreted as a single backslash. The escape sequences '\t' and '\n' specify tab and newline characters, respectively.

In other words, the defaults cause LOAD DATA to act as follows when reading input:

Look for line boundaries at newlines.

Do not skip any line prefix.

Break lines into fields at tabs.

Do not expect fields to be enclosed within any quoting characters.

Interpret characters preceded by the escape character \ as escape sequences. For example, \t, \n, and \ signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.

Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

Write tabs between fields.

Do not enclose fields within any quoting characters.

Use \ to escape instances of tab, newline, or \ that occur within field values.

Write newlines at the ends of lines.

see: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

for more details.

Acton

You need to add more details to your question such as why does it work, what went wrong in the OP, etc. – Ragnar