Importing data from a tab-delimited TXT file using PHP
Posted on 2009-05-12
I'm writing a PHP-based app that requires that data be imported into a MySQL datbase from a tab delimited TXT file.
The import file will initially be in XLS format, .. but will then be exported to tab-delimited TXT format using Excel's "Save As" functionality.
One of the problems that I know I'm going to run in to is that some of the data in the TXT file will contain quotation marks as qualifiers (ie: typically wrapped around individual column values that contains commas in them, ... such as item descriptions and dollar values, etc.).
I really don't want to have to involve any kind of "custom excel macro" or anything of the sort if I can help it, .. since that's always proven to be an annoyance.
So .. here is what I'd like to accomplish. I need a PHP-based script (preferably something procedural) that has these features:
1) Reads the contents of a tab-delimited TXT file (which already resides on the server) which contains 11 specifically named columns in it -- (ie: ItemName, ItemDescription, ReservePrice ...)
2) Checks to see if a piece of column data has quotation mark qualifiers surrounding it, .. and if so, .. it removes them from the data.
3) Checks to see if a piece of column data starts with a "$" symbol, .. and if so .. remove the "$" symbol, and also remove any commas that might exist ($2,400.00 --> 2400.00)
4) Insert the column data into the database -- 1 row at a time -- using the "sanitized" data.