Unanswered

execute sql from excel, return empty cell

(0) Share

Report

Posted on by lion

100

execute sql from excel(发票清单.xlsx), return some empty cell for supplier column, the column cell format is 'general', some cell value is string, some is number, the cell with string return empty.

sql:

select * from [%SheetName%]
where Status is null

column format in excel

some cells are empty within supplier column but the data in excel has value

Categories:

Power Automate Desktop

发票清单.xlsx

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (2)

Report

This is the normal behavior of running SQL queries to Excel. The data type that the SQL query determines for each column has nothing to do with the formatting of the column in Excel. It uses some of the first values to determine the data type. And if those values are numeric, it will assume the column is numeric and then ignore non-numeruc values.
As far as I know, the only way to work around this is to modify the connection string to force it to read the entire contents of the table as text. It can be done by adding the IMEX=1 parameter to the extended properties of the connection string. See here for reference: https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
lion 100 on at

Like (0)

Report

know it's a normal behavior, ask business format the column to 'Text' then issue fixed, thanks!

Was this reply helpful? Yes No