Export Excel data directly to web database

Question

Possible Duplicate:
What is the easiest way to import an excel sheet into mysql

I would like to take an excel file and add the data contained in the file into a mysql database. This excel file is generated by lab software and the layout is customizable. However, I would like the data to be auto-transferred to the database without a user needing to log in or enter data manually. The lab software generates the excel sheet at the end of each sample analysis, and there is an option to run a command. I was transferring the data to a networked Access database, but now I am wanting this to be all web-based.

http://stackoverflow.com/questions/1026584/what-is-the-easiest-way-to-import-an-excel-sheet-into-mysql — NickSlash, Dec 14 '12 at 23:20
This is a pretty broad question. Any client side language which is capable of posting a file to a web server would be fine (assuming you know how to deal with it once its there). What languages do you know? — Tim Williams, Dec 15 '12 at 03:17

score 0 · Answer 1 · answered Dec 15 '12 at 03:31

You can use LOAD DATA INFILE to load a file directly to MySQL, however you can't use excel format, but if you can export that excel to csv (this is usually supported by most software and if not possible, you can do so using SAVE AS on excel) then it is pretty easy to use.

Definition of LOAD DATA command:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

Example of LOAD DATA INFILE usage to load a csv separated by , and enclosed by "

LOAD DATA INFILE 'myfile.csv'
    INTO TABLE tbl_name
    FIELDS
    ENCLOSED BY '"'
    TERMINATED BY ','
    LINES
    TERMINATED BY '\r\n'

More info here http://dev.mysql.com/doc/refman/5.0/es/load-data.html

Export Excel data directly to web database

1 Answers1