Batch upload primers

Thanks for your reply. Yes, I would love to know the exact steps as I'm not proficient in MySQL. I believe it would be useful for other users out there as well.

How to batch-upload multiple records from an Excel file using phpMyAdmin

1. phpMyAdmin is a free PHP-based web application to interact with MySQL databases. Downloads, installation, and usage instructions are available on the phpMyAdmin website (http://www.phpmyadmin.net/). Once set up, one uses a browser to go to the phpMyAdmin front page, e.g., at http://localhost/phpmyadmin/. All databases, including the one LabStoRe uses, should be visible and interactable.

2. Item records in LabStoRe are stored in category-based tables like 'cell-lines', 'plasmids' and 'chemicals' in the MySQL database whose name is specified in the LabStoRe software file 'config.php'.

3. As an example, let us assume that we want to batch-upload some records for the category 'chemicals'. Using phpMyAdmin, or other means, one can see the structure (number and types of columns) of the 'chemicals' table. One can manually copy the structure [not the data itself] into an Excel table. But the best way is to use the 'export' option:

* On phpMyAdmin front-page, choose (click) LabStoRe's database from the database-list on the left.

* On the ensuing page, choose the 'chemicals' table from the table-list on the left.

* On the ensuing page, choose 'export' from the top horizontal command menu bar.

* On the ensuing page, select 'CSV for MS Excel', 'Put field names in first row',  and 'Save as file', and click 'Go'.

* The CSV file should now get downloaded.

4. Open the CSV file in Excel. Remove all rows except the top one (field names). Add the new items, one per row. Save the file, in CSV format.

There are six fields you have to be careful about:

* ID -- leave it empty
* added_on and modified_on -- have to be empty, or dates with format yyyy-mm-dd. You can use 0000-00-00.
* added_by and modified_by -- can be empty, or a user's name [this name should be there under the 'name' field of 'users' table; you can use phpMyAdmin to browse that table]
* ID_user -- can be 0, or a user's name, like above

  Note: Excel tends to auto-format date values. If it converts a value to, say, the dd/mm/yyyy format, then that value may not get inserted. You may then have to use applications other than Excel to edit the file. Excel may also insert multiple empty lines at the end of the file -- this can cause an import issue in phpMyAdmin. I usually let Excel do it's thing, but re-check the CSV file by opening it in some simple text editor application [not Word].

6. Depending on the version/configuration of Excel, the CSV file may be using a particular character to designate termination of rows, separation of fields, etc. If you know those characters, make a note. If not, use a good plain text editor like Notepad++ to figure it out.

7. Following steps similar to those in step 3, go to 'import' page. Choose the CSV file, select 'CSV', choose to skip row 1 (field names), and set 'Fields terminated by' (would likely be a comma), 'Fields enclosed by' (likely just ''), etc. [most probably you have to change just the first one], and choose 'Go'. If the import is successful, the new records would now be there when you go to your LabStoRe website. An unsuccessful import usually arises if the 'Field sterminated by', etc., values are not properly specified, if there are extra empty lines in the CSV file, etc.

  Note: You may want to save a copy of the 'chemicals' table before you do the import. Use the 'export' option to do so; the copy can be saved as an sql file, a CSV file, etc.

8. Besides phpMyAdmin, there are other means to import CSV/Excel files. Some are commercial applications. Some are more easy to use. E.g., with the Navicat application you can use an existing Excel file with any type of table structure; the application lets you map the fields from that table to the fields in the MySQL table.

9. A similar approach will work for any category of items. In any case, it is important to get an idea of the MySQL table structure [required fields, types of fields (date-type, e.g.), auto-added fields (primary ID fields, usually), etc.].

On the 'import' page of phpMyAdmin (I have version 2.10), look for 'Number of records (queries) to skip from start' under 'partial import'; set it to 1.

Alternately, you can also delete the first row from the CSV file in Excel.

Note that sometimes Excel can be finicky when dealing with CSV. E.g., consider a row in a table of five columns, with the last three fields empty. When a comma (,) is the field separator, the row should be saved in the CSV file on a single line like this:

'field_1_value','field_2_value','','',''

But Excel might save the line as:

'field_1_value','field_2_value'

So consider this if you still have an import problem. If the last fields of all the rows have non-empty values, this problem should not happen. If it happens, an option might be to save the CSV file as an Excel .xls file using Excel, and then using this online converter -- http://www.iconv.com/xls2csv.htm (I haven't tried it).