CODEWITHSUNDEEP
phpcsvparsingimportdouble-quotes
jerebear
jerebear

Reputation: 6655

Parse csv data which contains commas inside of quote-enclosed text

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.

Upvotes: 2

Views: 293

Answers (2)

OIS
OIS

Reputation: 10033

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.

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 7

Related Questions