28) How to import tab delimited data into a table of a mySQL database in proline server???

Steps to import a tab delimited data:

1) Prepare your tab delimited data. Make sure that there are no headers included on the first line. File transfer in the data to proline either by WinSCP or SSH.

2) Create a table in the mySQL database in proline. Make sure that the imported data would match to the column headers that you have defined in the table respectively.

For example, "D1_00001" in my data would match to "Accession" column that I have defined in my SQL table.


3) Login to proline and SQL. (refer to previous posts 2 and 23)

4) Use the following SQL command:

LOAD DATA LOCAL INFILE 'file_with_full_pathname' INTO TABLE name_of_table

For example:

mysql> \g LOAD DATA LOCAL INFILE '/projects/denvdb/www/DENV.txt' INTO TABLE DENV


It will work even though I did not specify that the data is tab separated.

Please refer here for more information.

For comma delimited data, the sql command is the same except that you may need to add in FIELDS TERMINATED BY ','

However I have not tested this out yet.