I'm having a helluva time with data importing (PHP MySQL)

Question

I'm building an app that pulls data in from an excel .csv file and applying various levels of formatting, moving and mapping. Most everything is figured out except for one hitch with cleaning the data. Here is an example of the data from Excel:

1. GREAT PERFORMANCES,GREAT PERFORMANCES,57744 ROUND LAKE RD,NEW YORK,NY
2. "GUASTAVINO'S, INC",GUASTAVINO'S,8250 WESTHEIMER RD,NEW YORK,NY
3. THE CLARKES GROUP LLC,HUGO'S FROG BAR,915 3RD AVE,CHICAGO,IL
4. TRIOMPHE RESTAURANT CORP,"GEORGE'S,JEAN",1309 E PUTNAM AVE,NEW YORK,NY

I can't do a straight explode() because of line 3's 2 and 4. There is a "string, string" on both of those lines and I have no control over the data exporting, just cleaning on import.

I've tried a number of non-graceful options and it's killing the processing time. Can anyone think of an elegant solution?

I can't use the MySQL IMPORT functionality, it has to be handled via PHP unfortunately.

Answer 1

You can also try executing a SQL statement:

LOAD DATA INFILE 'mydata.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"';

I hear you about using PHP, but you can run the statement above from PHP. Or do you mean that the input is a stream resulting from reading CSV, not a CSV file? Unfortunately LOAD DATA INFILE requires a filename, it can't take standard input.

Answer 2

Man do I feel foolish. I did research on available functions but somehow missed that one.

That's perfect, and I owe you some cookies.

Answer 3

I'm pulling it in from a .csv file and I've done the MySQL LOAD DATA INFILE but my users aren't savvy enough to provide clean, well-formatted data so I'm building a small UI that allows them to upload what they want and then work on applied formatting, field mapping, etc. and I have the option to add some forced settings of my own.

I tested fgetcsv for the initial read and it works beautifully.

Thank you for your fast response.

Answer 4

I see your question has been answered. But if you wanted to read the file into PHP for use, you could just have used fgetcsv.