0

I am quite new to MySQL. I have data in more than 2000 text files that I need to import into a table. I have created the table as follow: 

CREATE TABLE test1
(   
    Month TINYINT UNSIGNED,
    Day TINYINT UNSIGNED,
    Year TINYINT UNSIGNED,
    Weight FLOAT UNSIGNED,
    length FLOAT UNSIGNED,
    Site_Number CHAR(3),
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

All data in my text files are comma separated. Each file contains thousands of rows and each row include month, day, year, weight and length information. The site_number is the first three numbers of the file name. For example, if the file name is A308102316.txt, the site_number of all the records imported from this file will be 308. Also, I want the ID to be auto increment.

My question is: how can I achieve what I described above using MySQL command line client as I know nothing about PHP or other. I know how to import one text file into the table, but don't know how to write a loop to read many of them. Also, not know how to get site_number from the file name.

Thank you for any help that you can provide.

peterm
  • 91,357
  • 15
  • 148
  • 157
njwork
  • 13
  • 5
  • Short answer: you can't do this in mysql alone. For long answer see the following question and answer: http://stackoverflow.com/questions/42923999/how-can-i-load-many-files-into-mysql-automatically-add-date-information – Shadow Apr 05 '17 at 21:51

2 Answers2

0

It sounds like your text files are actually CSV. You should be able to rename to something like A308102316.csv and view it in Excel to verify.

For importing, I would suggest looking into csv.thephpleague.com

Here is an example of CSV to MySQL.

To start, what I would do is put all the text/csv files in a dedicated directory, loop over the files (in PHP) and run your import using the thephpleague/csv library.

Perhaps something like this:

use League\Csv\Reader;

//We are going to insert some data into the users table
$sth = $dbh->prepare(
    "INSERT INTO test1 (Month, Day, Year, Weight, length, Site_Number) VALUES (:month, :day, :year, :weight, :length, :site_number)"
);

$csv = Reader::createFromPath('/path/to/your/csv/A308102316.csv');
$csv->setOffset(1); // if you don't want to insert the header (first row)
$nbInsert = $csv->each(function ($row) use (&$sth) {
    //Do not forget to validate your data before inserting it in your database
    $sth->bindValue(':month', $row[0], PDO::PARAM_STR);
    $sth->bindValue(':day', $row[1], PDO::PARAM_STR);
    $sth->bindValue(':year', $row[2], PDO::PARAM_STR);
    $sth->bindValue(':weight', $row[3], PDO::PARAM_STR);
    $sth->bindValue(':length', $row[4], PDO::PARAM_STR);
    $sth->bindValue(':site_number', $row[5], PDO::PARAM_STR);
    return $sth->execute(); //if the function return false then the iteration will stop
});

Each $row[0] represents the specific column that is defined in your CSV beginning with 0 as the first one.

Community
  • 1
  • 1
casey
  • 395
  • 1
  • 8
  • My apologies. I totally missed the bit about not using PHP. Either way, you should use some scripting language to validate and possibly transform your data prior to inserting records. – casey Apr 05 '17 at 22:10
  • No worries. Thank you anyway~ – njwork Apr 07 '17 at 21:00
0

UPDATED You can do a one-liner in bash to load all your files by leveraging LOAD DATA INFILE.

for file in `ls -1`; \
do mysql databasename -h localhost -u username -e " \
LOAD DATA LOCAL INFILE '${file}' \
INTO TABLE test1 \
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' \
SET site_number = '${file:1:3}'; \
"; done

This assumes that

  • you cd'ed to the directory with the files; i.e. cd /path/to/data/files
  • your directory contains only data files; there're no subdirectories, symlinks or files that should't be loaded.
  • you have .my.cnf file setup so that you don't need to enter your password 2000 times

Naturally you need to change databasename, localhost and username to actual values.

If you're happen to be on Windows you can use git-bash.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Thanks very much for your help. Two follow up questions: 1)my files have a weird extension, for example, for data in 2011, all the files have name *.11, for data in 2012, have name *.12. I have data from 2011 to 2015. I can open them using notepad. They are in different folders. I figured if I can import one year data successfully, I could just repeat the process for 5 times. 2) To use the code you provided above, you said the data files have to be located in the current directory. How can I know what is my current directory so that I can move all the data files there? Thanks a lot! – njwork Apr 06 '17 at 21:34
  • Please see the updated answer. 1) I removed the pattern matching .txt extension. This means that all files will be processed now regardless of their extension. You can either run this command 5 times in different folders or copy/move all files into one and run it once. It's up to you. 2) Just change the directory to the one that contains files in your command prompt `cd /path/to/data/files` – peterm Apr 06 '17 at 22:08
  • Thanks again for your help. I have done some research about changing directory before asked you. It seems it won't work just by doing a cd. I have tried system cd, but it didn't work either. Just to be sure, what I tried was mysql> system cd C:/Users/Documents/Test/2015 – njwork Apr 07 '17 at 15:12
  • You cd into a directory from your OS command shell (you'd need to use git-bash as mentioned earlier because you're on Windows) not in your mysql prompt! – peterm Apr 07 '17 at 15:26
  • Thank you very much. You have been very helpful. I finally get the command running in git-bash, but get following error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) I guess this is what you mentioned about setup .my.cnf. Could you help on how to set up this file so that the error won't happen? I really appreciate your help. – njwork Apr 07 '17 at 16:48