4

I am connecting to an Excel file from PHP via PDO. When executing a query, Excel returns data, but it defines the first row of results as column names. How do I make Excel return all rows, including the first one, as data?

    $sheet = 'Sheet1';
    $fileName = '/123.xlsx';

    $dbh = new PDO("odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};HDR=NO;Dbq=$fileName;ReadOnly=False;", '', '');


            $query = "
            SELECT
                *
            FROM [{$sheet}$]
            ";

            $results = $dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);

PHP8.3 Excel2016 Win 10 x64

Improve this question
8
  • 2
    Why use OLEDB instead of using a library that can read XLSX files directly? xlsx is a ZIP package containing well-defined XML files. While you could read them directly, there are a lot of libraries that simplify this, run on all OSs and don't have bitness issues Commented Nov 10 at 14:31
  • These libraries are too slow. Commented Nov 10 at 14:36
  • 1
    You could try Header=False instead of HDR=NO. I have no idea if that will work, but I'm going by what I read here. I have my doubts about this, but it can't hurt to try. Did you find any documentation for the Data Source Name, or DSN, in this case? What does it say? Do you have a link? Commented Nov 10 at 14:53
  • I tried it. It doesn't help. Commented Nov 10 at 14:59
  • HDR=NO is a part of Extended Properties="...;HDR=NO;...", not a standalone key. Assuming your provider supports the Extended Properties. Commented Nov 10 at 15:29

3 Answers 3

Reset to default
1

If you do not manage to match your operating system with the driver bitness and the PHP bitness and figure out the appropriate driver ID and the configuration for it, (and if I get your question right), you should be able nevertheless to treat the first row differently than all the other rows.

As you're using ->fetchAll() (returns an array), this is rather straight forward with the different array functions:

$results = $dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);
$header = array_shift($results);

// or

$header = array_splice($results, 0, 1);

// or

$results = array_slice(
    $dbh->query($query)->fetchAll(PDO::FETCH_ASSOC),
    1
);

And probably a couple more ways, compare Deleting an element from an array in PHP.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yes. This issue can be resolved by subsequent processing of the query results. But this is not a completely clean solution. Especially considering that Excel is able to return data without dividing it into headers and data.
You already do that technically with fetchAll. If you wrap everything into a generator or decorate it with an iterator, you can have this more transparent and if you manage to establish the configuration you're looking for the rest of the code does not need any changes. Nevertheless, add more details to your question like operating system, PHP version and if 32/64 bit, same for the drivers etc. so that someone who is more into odbc and excel finds some more food for thought.
Added information.
1

In ODBC the first row is treated as column headers or data depends on the HDR setting. HDR=NO, which is correct.

in newer versions of the Excel ODBC driver may still infer headers based on the data type unless you also specify IMEX=1

Try

$sheet = 'Sheet1';
$fileName = '/path/to/filr-123.xlsx'; // absolute path

$dsn = "odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
     . "DBQ=$fileName;"
     . "HDR=NO;"
     . "IMEX=1;"; // forces all rows to be treated as data

$dbh = new PDO($dsn, '', '');

$query = "SELECT * FROM [{$sheet}$]";
$results = $dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);

print_r($results);
Improve this answer

3 Comments

Unfortunately, it didn't work. Anyway, the first row of the sheet is perceived as column headings.
"In ODBC the first row is treated as column headers or data depends on the HDR setting" Where did you see that?
I saw this by running the code.
0

A solution has been found. Not quite straight, but clean. Instead of connecting via ODBC, you need to connect via ADODB and the settings for the column header work there.

$conn = new \COM("ADODB.Connection");

$file = 'C:\123.xlsx';
$sheet = 'Sheet1';


$conn->Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$file;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"");

$query = "
SELECT
    *
FROM [{$sheet}$]
";

$rs = $conn->Execute($query);
$num_columns = $rs->Fields->Count();

for ($i=0; $i < $num_columns; $i++) {
    $fld[$i] = $rs->Fields($i)->Value();
}

var_dump($fld);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.