Excel as ODBC database, how to account for header row?

Help Activities
,
1

I’m playing around with ODBC data sources. When using Excel as a database, and running a query against it, how to account for the first row being a header row? Unlike the read workbook Activity, there is no checkbox to account for that here. Because the first row has headers, all columns are imported as strings, which is not what I want. Is there a way around this, or must you never use header row when using Excel as a data source?

2

@Terry_Marr

In odbc drivers header parameter might be missing…try oledb connection you would have a hdr parameter which can be set to yes

All connections strings here

If you are strict on odbc then read the data then over write the column names with first row values using a for loop

Cheers

3

Can you give me a little more detail on how to use the OLEDB connection string to connect to Excel and run a query? I tried the following:

In the Run Query Activity from Database.Activities package
I used this connection string, which I copied from your link above:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';"

and here’s my query:
“SELECT * from [Sheet1$]”

the error I am getting is:
Run Query: Invalid argument

4

@Terry_Marr

Here’s the KB article for an ODBC connection (not OLEDB), if that’s ok for you:

5

I was looking for a way to use the first row as headers. I understood that there’s no way to do that with ODBC? And that there was a parameter for that in OLEDB? Is this correct? I don’t really care which one I use, but I’d like to have the first row be the headers. Is this possible?

6

@Terry_Marr , the first row of the SQL query result should be the header. The data types may not work as you’d want except for dates.

image
image450×527 13.2 KB

Result:

image
image422×163 3.16 KB

7

@Terry_Marr

Did you happen to install the oledb provider?

have connect and run separate to get proper error messages to understand the issue

Driver
https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_X64.exe

cheers

8

that link doesn’t work

9

I finally figured it out. For anyone that comes to this, there’s a package that really helps in seeing how the data is imported. It’s called BalaReva.EasyDataTable.Activities, and it allows you to see how the data is imported, and change a column if necessary! Learn how to use it here

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.