running queries on csv file

[shocker-z]

Hi,

 

I've recently been trying to create a php script which can run actual queries on a csv file, my only chance ive found so far is getting "Microsoft text driver (*.txt; *.csv) working using ODBC but i've been unable to accomplish this.

 

What methods would rou reccomend, I cant use MySQL as it's being converted to exe using http://www.bambalam.se/bamcompile/ and also will potentially be used on over 1000 PC's. I'm parsing results from an assessment i develop and support for a college.

 

Regards

Liam

[Daniel0]

What do you mean with queries? Like SQL queries?

[shocker-z]

Yes that's what im after.

 

Sorry i was in a rush (my little 3month old girl was crying)

 

She's smiling now.. bit like her mum mood swings lol

 

Thanks

Liam

[shocker-z]

*bump*

Anyone any idea's, or is it back to the drawing board?

 

Liam

[corbin]

Hrmmm unless you can find an already made extension or package, you'll most likely have to code it your self....

 

You would basically just have to parse things....  Like:

 

SELECT col1 FROM file3.csv WHERE col1 = 'Corbin'

 

Could be turned into something like:

 

$file = file('file3.csv');
$heading = trim(array_shift($file));
$cols = explode(',', $heading);
$cols_keys = array();
foreach($cols as $k => $v) $cols_keys[$v] = $k;

$out = array();

foreach($file as $line) {
    $e = explode(',', trim($line));
    if($e[$cols_keys['col1']] == 'Corbin') {
        $out[] = $line;
    }
}

 

You would of course want to make that dynamic though where the file name column and what not where pulled from the query.  And, of course, you would want to add support through some kind of loop for mutliple conditions and ORs and stuff.....  But hopefully that shows the general idea I would go with.

 

Edit:  Oh....  I should probably also mention.... this way will be EXTREMELY slow, especially with larger files.

[shocker-z]

That's great help thanks

 

I think im going around this the long way anyway as what im doing is..

 

Looping through a HTML results directory (student results seperated by _ in the filename e.g. studentref_forname_surname_1)

so at the moment i have Student reference number, Forname and Surname but there is also a CSV file with the same filename but different extension so i can access that to pull out extra information. There should only ever be 1 line of results.

 

I was trying this method of querying the csv files as there is also a file called fulldata.csv which has ever student's reccord inside so i could addapt/re-write the scriipt and it would be alot better.

 

Come to think of it I could just open the seperate csv files and look at line 2 and seperate the values.

 

Any way i could automatically parse a csv file and create an array with the headings as key value?

 

e.g. array('Name'=>'Liam','DOB'=>'22/01/1985' etc...)

 

It would need to be created dynamically as there arre a few different assessments with different colomn names.

 

Hope i haven't mutterd on so much that i've made no sence.

 

Regards

Liam