1

I'm trying to import multiple tsv files in a mysql database. Problem is that there are 8 columns but there a some empty fields.

The tsv looks like:

 Test1  3   -2  YEHD    4473    TR  94059   02948
 Test2  5   -7      4029    HDU 37465   02948
 Test2  5   -7  HDYEG   2039    UEJ     93844
 Test2  7   0   HDUI        JWI     02948
 Test3  7   1   YEHV    2843    JHW     88283

I've tried to import one of the files with LOAD DATA LOCAL INFILE but the import skips the empty fields:

 LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
 FIELDS TERMINATED BY '\t'
 ENCLOSED BY ''
 LINES TERMINATED BY '\n'

How can I import files like this in MySQL with an output like this:

 | column1 | column2 | column3 | column4 | column5 | column7 | column8 | column8 |
 ---------------------------------------------------------------------------------
 | Test1   | 3       | -2      | YEHD    | 4473    | TR      | 94059   | 02948   |
 | Test2   | 5       | -7      |         | 4029    | HDU     | 37465   | 02948   |
 | Test3   | 5       | -7      | HDYEG   | 2039    | UEJ     |         | 93844   |

Edit When I change the tab separation to a comma separation it works (MySQL load NULL values from CSV data). But is it possible to do this with a tab separated file? Or is is just easier to convert all the files from a tab to a comma (how can i do this automated)?

Community
  • 1
  • 1
Glarp
  • 103
  • 1
  • 10
  • 1
    Possible duplicate of http://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data @Giarp: Please have a look and comment here (the question there is well written IMO and the answers seem to match your situation and also hint at configuration induced complications) – Dilettant May 30 '16 at 16:45
  • @Dilettant It sure seems like a similar problem! Difference is that I'm using a tab separated file and not a comma separated file. I've tried to modify the example to "fields terminated by '\t'" but I'm having the same problem as above. When I modify the tsv file to csv file is works. But is it possible to do this with a tsv file? – Glarp May 30 '16 at 17:20
  • @Giarp thanks for investigating it is presumeably possible but it may need some digging. Sometimes trivial things like separator variables become broken until someone notices ;-) – Dilettant May 30 '16 at 17:25
  • 1
    A wild guess: You might try `... FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ''...` (thus adding the token `OPTIONALLY` ...) no guarantees, but the documentation at http://dev.mysql.com/doc/refman/5.7/en/load-data.html gave me on skimming this as a possibility to try. This of course does not explain the `\t` versus `,` paradox you are wondering about, but in case it works ... – Dilettant Jun 02 '16 at 12:46

0 Answers0