Scripting

Using ADO & PHP to query a CSV file

by Lewis · Sun 18th September, 2005

OK, so I finished my book on VBScript. While interesting and informative it certainly didn’t cover all the aspects of VBSCript that I’d like to know about. Scouring MSDN for clues just isn’t my idea of fun either and quite frankly gets me a little irritated. Why it can’t be like the PHP Website is beyond me.

While the book did touch on ADSI and WMI, it didn’t really give me much in the way of detail about actually doing anything with the information I can gather. So I can tell who’s logged on to a computer if I issue a WMI query specifically to that computer, so what!?

I know, I’ll write a script to grab a list of computers from Active Directory and then iterate through the list of computers to see who’s logged on to each computer!

Oh joy, the script takes 1 minute and 36 seconds on average to run through the eighty-six nodes on the network. I can’t be running that every time I want to see who’s logged on to a machine.

Hmm? Aha! I’ll schedule it to run every hour or so and write the information off to a CSV file each time, then at least I’ve got a good chance of knowing the correct machine when I get a support call.

So I’ve got my CSV file, with eighty-six results in it. Now, my speed reading is pretty good but can I be arsed to open the file and read its contents every time I want to connect to a user’s machine?! Err, no.

Wait! I think I remember reading something on The Scripting Guys about connecting to a CSV file with ADO so I can throw SQL queries at the file, let’s have a butchers at that.

OK, so that looks easy(ish) but it’s all in VBSCript which means it outputs to either a command prompt or a dialog box. So it would actually work but surely it’d be better if it were available on the intranet and in a central location accessible at the click of a mouse?

In step PHP5’s new COM functionalities. PHP5’s COMfromthewhatnow? Admittedly, before I picked up the VBScript book I would have looked at you like you were talking Japanese if you said COM to me but, thanks to the book, I have sufficient knowledge to bungle my way through creating a PHP script to do it.

Thanks to the helpful souls that post on the PHP Website, I’ve managed to cobble a PHP script together. It opens a CSV file with ADO, queries it with SQL and then spits out the results into an HTML table. I’m sure it will be more useful than parsing it with explode()s and in_array()s anyway!

I’ve tried to comment it as much as possible for anyone wanting to use the code. If you’re having trouble just let me know and I’ll see what I can do to help.

CSV ADO SQL PHP HTML

// Set the query we'll be using on the file.
$adoQuery = "SELECT * from myfile.csv";

// Create the ADO connection and ADO recordset
$adoConn = new COM("ADODB.Connection") or die("Can't start ADO");
$recordSet = new COM("ADODB.RecordSet") or die("Can't create record set");

// Open the connection and set parameters for the query
$adoConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\To\Thefile\;Extended Properties=\"text;HDR=YES;FMT=Delimited\"");

// Execute the query against the connection (results are stored in the recordset!)
$recordSet->Open($adoQuery, $adoConn);

// Count the number of field returned in the recordset (allows us to iterate through)
$intFieldCount = $recordSet->Fields->Count;

// Tabularise the data returned
echo ''."\n\tRecord No.\n"; // I have included a TH for a record count field too!
for ($i=0; $i< $intFieldCount; $i++) {
echo "\t".ucfirst($recordSet->Fields($i)->Name).""."\n";
}

$j = 1; // Set a counter to show record numbers in the record count field.

while (!$recordSet->EOF) {
echo "\t".''."\n\t\t\n";
for ($i=0; $i< $intFieldCount; $i++) {
echo "\t\t".$recordSet->Fields($i)->Value.""."\n";
}
echo "\t".''."\n";
$j++;
$recordSet->MoveNext();
}
echo '$j'."\n";

// End tabularising data

// Free resources
$adoConn->Close();
unset($adoConn);
unset($recordSet);

// Set the query we'll be using on the file.

$adoQuery = "SELECT * from myfile.csv";

// Create the ADO connection and ADO recordset

$adoConn = new COM("ADODB.Connection") or die("Can't start ADO");

$recordSet = new COM("ADODB.RecordSet") or die("Can't create record set");

// Open the connection and set parameters for the query

$adoConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\To\Thefile\;Extended Properties=\"text;HDR=YES;FMT=Delimited\"");

// Execute the query against the connection (results are stored in the recordset!)

$recordSet->Open($adoQuery, $adoConn);

// Count the number of field returned in the recordset (allows us to iterate through)

$intFieldCount = $recordSet->Fields->Count;

// Tabularise the data returned

echo ''."\n\tRecord No.\n"; // I have included a TH for a record count field too!

for ($i=0; $i< $intFieldCount; $i++) {

echo "\t".ucfirst($recordSet->Fields($i)->Name).""."\n";

}

$j = 1; // Set a counter to show record numbers in the record count field.

while (!$recordSet->EOF) {

echo "\t".''."\n\t\t\n";

for ($i=0; $i< $intFieldCount; $i++) {

echo "\t\t".$recordSet->Fields($i)->Value.""."\n";

}

echo "\t".''."\n";

$j++;

$recordSet->MoveNext();

}

echo '$j'."\n";

// End tabularising data

// Free resources

$adoConn->Close();

unset($adoConn);

unset($recordSet);

The script above is very generic and can be used for any CSV file that uses a comma as a delimiter. If your file uses a different delimiter, have a look at the Scripting Guys’ column to see what part of the script you need to change.

Doing something similar in VBScript is a little easier but slightly less useful in my opinion. If there is more than one domain administrator, each of them would need a copy or each would need to have a mapped drive to run the script.
Here’s the VBScript. Admittedly this VBScript isn’t quite as versatile as the PHP above. Mainly because I am assuming that the fields being queried are called computer, status and username. If you’ve got your wits about you, you’ll see how to change it to be as versatile as the one above.

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Path\To\TheFile\"
strCSVFile = "myfile.csv"

strQuery = InputBox("Please type your query." & vbcrlf & vbcrlf _
& "The FROM part of the query MUST be :" & vbcrlf & "FROM " & strCSVFile, _
"Query Required", "SELECT * FROM myfile.csv")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open strQuery, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

WScript.Echo objRecordSet.recordCount & " records returned."

Do Until objRecordset.EOF
Wscript.Echo "Computer: " & objRecordset.Fields.Item("computer")
Wscript.Echo "Status: " & objRecordset.Fields.Item("status")
Wscript.Echo "Username: " & objRecordset.Fields.Item("username")
objRecordset.MoveNext
Loop

On Error Resume Next

Const adOpenStatic = 3

Const adLockOptimistic = 3

Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Path\To\TheFile\"

strCSVFile = "myfile.csv"

strQuery = InputBox("Please type your query." & vbcrlf & vbcrlf _

& "The FROM part of the query MUST be :" & vbcrlf & "FROM " & strCSVFile, _

"Query Required", "SELECT * FROM myfile.csv")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & strPathtoTextFile & ";" & _

"Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open strQuery, _

objConnection, adOpenStatic, adLockOptimistic, adCmdText

WScript.Echo objRecordSet.recordCount & " records returned."

Do Until objRecordset.EOF

Wscript.Echo "Computer: " & objRecordset.Fields.Item("computer")

Wscript.Echo "Status: " & objRecordset.Fields.Item("status")

Wscript.Echo "Username: " & objRecordset.Fields.Item("username")

objRecordset.MoveNext

Loop

noisymime says:

Thu 13th April, 2006 at 02:27

Great work on this Lewis, its the only complete example for ADO CSV connections in PHP that I can find!

I’m fairly new to PHP but I think there’s a small mistake in the lines that access the ADO resultSet:
echo “\t”.ucfirst($recordSet->Fields($i)->Name).””.”\n”;
and
echo “\t\t”.$recordSet->Fields($i)->Value.””.”\n”;

The Fields($i) should be Fields[$i]
I know its small but it can drive a newby crazy 🙂

Thanks again!
Lewis says:

Thu 13th April, 2006 at 13:25

Hi noisymime,

Thanks for your post, it’s the first sensible non-spam comment I’ve had on this particular post!

I see what you’re saying and it could potentially cause confusion but I think in this instance it’s because of the way you have to access the recordSet COM object that we use a normal bracket “()” instead of PHP’s square brackets “[]”. You’re actually calling a method of the recordSet object, you’re just using PHP’s COM interface to do it.

Feel free to test though! 🙂