[SOLVED] CSV to array

[inactive]

Hey guys,

 

I've seem a million different scripts/functions out there that build on fgetscsv, but they don't do exactly what I want.

 

I'm looking to convert a CSV file to an array, where the field end char is " (double quote) and decimeter is is , (comma), but where field data may contain commas or double quotes, but this should not indicate end of field, unless it is specifically end-of-field,delimiter,start of field (i.e. "," [double-quote, comma, double-quote]).

 

So for example the following data:

 

"james","foo,bar","baby"face","trollop"

 

Would produce the following array:

 

[0] => james
[1] => foo,bar
[2] => baby"face
[3] => trollop

 

The data would always be in this format (i.e. each field will be surrounded by double quotes, and then delimited by a comma).

 

Any ideas? As I would prefer not to trim of the start and end double-quotes and then split by "," if you know what I mean.

 

Thanks guys. Looking forward to response.

[cooldude832]

The point of delimetres is to be a reserved character that won't be used in the "cells"

you need to adjust you deliminator system

[inactive]

Very good point, especially as there are standards associated with proper CSVs

 

However unfortunately I do not have control over the data I have to process, and as such both the delimeter char and start/end field char may be somewhere in the field values, and I need to be able to work around this.

 

Any suggestions?

[cooldude832]

well the only thing I can say is either

a) Make a better delim such as (;, (*^*) that isn't going to be in a string

b) Escape reserved characters in string values

[BillyJim]

Change the delimiter in Excel and save again as .csv or do a search/replace with e.g. sed or any text editor and replace "," with "<yourdelimiter>".

[inactive]

Good suggestions thanks BillyJim and cooldude, however as previously indicated I do not have any control over the data, so I cannot change the delimiter or dump it into Excel first.

 

A bit of background might clear this up slighly (sorry for being confusing previously):

 

I will be using php to access a CSV file located on another server (http access, using curl) which I have no control over. I have to then convert this CSV file into an array for further processing. This process will run every 10 minutes, permanently, and the CSV file is about 3Mb, so as you can see it is not possible to jump in half way with Excel. Besides, as the file is already in CSV format, Excel reads is incorrectly anyway (no fault of Excel, just bad CSV formatting, but unfortunately I cannot change that fact).

 

What am I left with?

[cooldude832]

well then don't call it a CSV file since its not a csv file!

 

You might able to build a fancy regex that replaces every comma in side quotes with a placeholder character. and then use it as a csv

[inactive]

Sorry yes, one other thing is that this file I access using curl is then saved as a .CSV file on the server, so yes it is a CSV file. Sorry for confusion.

 

So apart from regex or split (splitting by ","), no other suggestions?

[benjaminbeazy]

doesn't get much simpler than this....

 

$string = ' "You cannot really call this "data"", "more crap"';

$parts = explode('",', $string);
foreach($parts as $key => $val){
  $parts[$key] = substr_replace($val, '', 1, -strlen($val));
}

[inactive]

Hey thanks benjaminbeazy, yeah I thought I'd have to split( or explode( or something. Just a quick query, but what were you trying to achieve with the foreach loop? It doesn't seem to do anything at the moment. I assume you were trying to get rid of the extra quotes left over? Being a noob and all, I have no idea if there's a typo or anything lol...

 

Thanks mate.

[benjaminbeazy]

$string = ' ""You cannot rea,lly call this "data"", "more crap"';

$parts = explode('",', $string);
for($i=0;$i<count($parts);$i++){
  $val = trim($parts[$i]);
  $parts[$i] = substr_replace($val, '', 0, -(strlen($val)-1));
  if( ($i+1) == count($parts) ){
    $parts[$i] = substr($parts[$i],0,-1);  
  }
}
echo $parts[1];

 

my mistake, use this code instead, some crap was wrong with the last one

[inactive]

Thanks again. Only minor issue is that it doesn't handle an empty field very well (will show up as a double-quote in the array instead of NULL). For some reason this isn't a problem for the last field.

 

For example the following string:

 

"Home","","Wingrove"," Lyn"," ","2/125 Albatross"

 

Will show come out in the array as:

 

    
    [0] => Home
    [1] => "
    [2] => Wingrove
    [3] =>  Lyn
    [4] => "
    [5] => 2/125 Albatross

 

However:

 

"Home","","Wingrove"," Lyn"," ","2/125 Albatross",""

 

Will show come out in the array as:

 

    
    [0] => Home
    [1] => "
    [2] => Wingrove
    [3] =>  Lyn
    [4] => "
    [5] => 2/125 Albatross
    [6] =>

(Last empty quote goes into array correctly)

 

A little more tricky than you suspected?

 

Thanks heaps.

[benjaminbeazy]

man you're picky i'm tired, youll have to forgive my lack of foresight. this should fix that

$string = ' ""You cannot rea,lly call this "data"", " ", "more crap", " "';

$parts = explode('",', $string);
for($i=0;$i<count($parts);$i++){
  $val = trim($parts[$i]);
  if($val == '"'){
    $parts[$i] = NULL;
  }else{
    $parts[$i] = substr_replace($val, '', 0, -(strlen($val)-1));
  }
  if( ($i+1) == count($parts) ){
    $parts[$i] = substr($parts[$i],0,-1);
    $val = trim($parts[$i]);
    if($val == '') $parts[$i] = NULL;
  }
}
print_r($parts);

 

im absolutely positive it could be a little more efficient, but it does the job...

[inactive]

Thanks ben. Your code works well with the data you provided. However my data will not contain spaces after each field.

 

The data I'm likely to get could be as bad as this:

"Home","","Wingrove"," Lyn"," ","2/125 Albatross","","hdfsh"sfs","sdff,bb","fsdfs,","ssdf""","dfd"

 

In which case the following code works best:

<?php

$string = '"Home","","Wingrove"," Lyn"," ","2/125 Albatross","","hdfsh"sfs","sdff,bb","fsdfs,","ssdf""","dfd"';

$parts = explode('","', $string);

$parts[0] = substr($parts[0], 1);
$parts[count($parts)-1] = substr($parts[count($parts)-1], 0, -1);

print_r($parts);

?>

 

Which returns:

Array
(
    [0] => Home
    [1] => 
    [2] => Wingrove
    [3] =>  Lyn
    [4] =>  
    [5] => 2/125 Albatross
    [6] => 
    [7] => hdfsh"sfs
    [8] => sdff,bb
    [9] => fsdfs,
    [10] => ssdf""
    [11] => dfd
)

Which is 100% correct.

 

So basically I'm exploding by "," (which will always be between each field), and then removing the the quotes (") from before the first field and and also from the end of the last field.

 

I thought I may have to do this in the first place, but I was hoping there was a more elegant solution. However this works 100% so I will settle for this.

 

Thanks heaps to all those who helped out and suggested ideas.

 

~Mitch.