How Do I Import A Data File Into Mysql With Fields Termiated By A Space-character?

[Erland]

do I need to use an escape or ascii character code?

[Martino]

COMMANDS   /tmp> cat testLoad.sql Ken Holm Zeke Blackwell Clay Richmond Kathy Cathy Marilyn Julia BettyLou Sutherland /tmp>mysql -u someUser --password=somePassword someHost Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1848 to server version: 3.23.58-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> describe testTable;+-----------+-------------+------+-----+---------+----------------+ | Field     | Type        | Null | Key | Default | Extra          | +-----------+-------------+------+-----+---------+----------------+ | id        | int(5)      |      | PRI | NULL    | auto_increment | | firstName | varchar(25) |      |     |         |                | | lastName  | varchar(25) |      |     |         |                | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set(0.00 sec) mysql> select * from testTable\GEmpty set(0.00 sec) mysql> LOAD DATA INFILE '/tmp/myLoad.sql'     -> INTO TABLE testTable     ->FIELDS TERMINATED BY ' '     ->LINES TERMINATED BY '\n'     ->(firstName, laastName);Query OK, 8 rows affected(0.00 sec) Records: 8  Deleted: 0  Skipped: 0  Warnings: 4 mysql> select * from testTable\G*************************** 1. row ***************************        id: 1 firstName: Ken  lastName: Holm *************************** 2. row ***************************        id: 2 firstName: Zeke  lastName: Blackwell *************************** 3. row ***************************        id: 3 firstName: Clay  lastName: Richmond *************************** 4. row ***************************        id: 4 firstName: Kathy  lastName: *************************** 5. row ***************************        id: 5 firstName: Cathy  lastName: *************************** 6. row ***************************        id: 6 firstName: Marilyn  lastName: *************************** 7. row ***************************        id: 7 firstName: Julia  lastName: *************************** 8. row ***************************        id: 8 firstName: BettyLou  lastName: Sutherland 8 rows in set(0.00 sec) mysql> quit Bye

[Waverly]

Use "load data infile" COMMANDS   /tmp> cat testLoad.sql Ken Holm Zeke Blackwell Clay Richmond Kathy Cathy Marilyn Julia BettyLou Sutherland /tmp>mysql -u someUser --password=somePassword someHost Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1848 to server version: 3.23.58-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> describe testTable;+-----------+-------------+------+-----+---------+----------------+ | Field     | Type        | Null | Key | Default | Extra          | +-----------+-------------+------+-----+---------+----------------+ | id        | int(5)      |      | PRI | NULL    | auto_increment | | firstName | varchar(25) |      |     |         |                | | lastName  | varchar(25) |      |     |         |                | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set(0.00 sec) mysql> select * from testTable\GEmpty set(0.00 sec) mysql> LOAD DATA INFILE '/tmp/myLoad.sql'     -> INTO TABLE testTable     ->FIELDS TERMINATED BY ' '     ->LINES TERMINATED BY '\n'     ->(firstName, laastName);Query OK, 8 rows affected(0.00 sec) Records: 8  Deleted: 0  Skipped: 0  Warnings: 4 mysql> select * from testTable\G*************************** 1. row ***************************        id: 1 firstName: Ken  lastName: Holm *************************** 2. row ***************************        id: 2 firstName: Zeke  lastName: Blackwell *************************** 3. row ***************************        id: 3 firstName: Clay  lastName: Richmond *************************** 4. row ***************************        id: 4 firstName: Kathy  lastName: *************************** 5. row ***************************        id: 5 firstName: Cathy  lastName: *************************** 6. row ***************************        id: 6 firstName: Marilyn  lastName: *************************** 7. row ***************************        id: 7 firstName: Julia  lastName: *************************** 8. row ***************************        id: 8 firstName: BettyLou  lastName: Sutherland 8 rows in set(0.00 sec) mysql> quit Bye kenholm3 85 months ago Please sign in to give a compliment. Please verify your account to give a compliment. Please sign in to send a message. Please verify your account to send a message.

[Jacob]

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values(CSV) format, such that lines have fields separated by commas and enclosed within double quotes. If lines in such a file are terminated by newlines, the statement shown here illustrates the field- and line-handling options you would use to load the file:LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Sources: http://dev.mysql.com/doc/refman/5.0/en/load-data.html newbie425331 85 months ago Please sign in to give a compliment. Please verify your account to give a compliment. Please sign in to send a message. Please verify your account to send a message.

[Barthel]

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values(CSV) format, such that lines have fields separated by commas and enclosed within double quotes. If lines in such a file are terminated by newlines, the statement shown here illustrates the field- and line-handling options you would use to load the file:LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';