CODEWITHSUNDEEP
mysql
SkyVar
SkyVar

Reputation: 351

Load Data Local Infile Issues

OK i'm trying to upload a txt file into my table. My issue is with the field terminator. I have tried using several characters but nothing wants to work. My last attempt was with a '.'

txt file:

John. B. Smith. 123456789. 1955-01-09. 731 Fondren, Cary, NC. M. 30000. 333445555. 5.   
Franklin. T. Wong. 333445555. 1945-12-08. 638 Voss, Cary, NC. M. 40000. 888665555. 5. 
Alicia. J. Zelaya. 999887777. 1958-07-19. 3321 Castle, Apex, NC. F. 25000. 987654321. 4.
Jennifer. S. Wallace. 987654321. 1931-06-20. 291 Berry, Garner, NC. F. 43000. 888665555. 4.
Ramesh. K. Narayan. 666884444. 1952-09-15. 975 Fire Oak, Angier, NC. M. 38000. 333445555. 5.
Joyce. A. English. 453453453. 1962-07-31. 5631 Rice, Raleigh, NC. F. 25000. 333445555. 5.
Ahmad. V. Jabbar. 987987987. 1959-03-29. 980 Dallas, Cary, NC. M. 25000. 987654321. 4.
James. E. Borg. 888665555. 1927-11-10. 450 Stone, Cary, NC. M. 55000. NULL. 1.

MySQL code:

mysql> load data local infile 'c:/employee upload2.txt' into table employee
    -> fields terminated by '.' lines terminated by '\n';
Query OK, 8 rows affected, 80 warnings (0.02 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 80

table looks like everything is trying to be crammed into the first field, but that can only hold the first 8 characters. Any ideas?

Upvotes: 0

Views: 556

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

Your data format is not good. You need a consistent field delimiter if you want any hope of this working. You cannot use space-separated values without then escaping the spaces within other fields like address field (or name fields though not shown in sample data).

You also would not have the column headings in this file.

If you just used tab-separated values instead of space-separated, this would work just fine with the default LOAD DATA INFILE settings for field delimiters, escaping and line separators (assuming \n is actually your line separator)

The default MySQL behavior (if nothing is specified for delimiters, field encolsing characters, escape sequence or line start and end chracters is this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

You can obviously use whatever characters you like in your data set as long as you match the query to use the same settings.

If you wanted to stick with space delimiters, you could do something like this

FIELDS TERMINATED BY ' ' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

And put " around any fields that would potentially have spaces in them (or all fields) or escape all your spaces within field data with \\. you would not need to escape the spaces if they were within a field enclosed by the double quotes.

Upvotes: 1

Related Questions