Help Needed: SQL Query Against Excel with Special Characters in Column Names

Help Activities
,
1

I need some guidance on an SQL query within UiPath that interacts with an Excel spreadsheet. The query was functioning as expected until I introduced columns with special characters: <90, 91-120, and >120. Since adding these to the spreadsheet, I’ve consistently encountered the error: “No value given for one or more required parameters,” which leads me to suspect a syntax error related to these column names.

Here is the code:

"SELECT [BookTitle], " &
"[Author], " &
"[Publisher], " &
"COUNT([BookID]) AS [TotalBooks], " &
"MAX([ReaderEmail]) AS [LastReaderEmail], " &
"MAX([CheckoutDate]) AS [LatestCheckoutDate], " &
“MAX([DueDate]) AS [DueDate], " &
“MAX([LibrarianEmail]) AS [LibrarianEmail], " &
“MAX([LibrarianName]) AS [LibrarianName], " &
“MAX([OverdueDays]) AS [MaxOverdueDays], " &
“MAX([””<90"”]) AS [UnderNinetyDays], " & // This line and similar lines are new additions
“MAX([”“91-120"”]) AS [NinetyOneToHundredTwentyDays], " &
“MAX([””>120"”]) AS [OverHundredTwentyDays] " &
"FROM [Books$] " &
"WHERE ([DueDate] < GETDATE()) " &
"AND ([Category] IN (‘Fiction’, ‘Non-Fiction’, ‘Biography’, ‘Children’)) " &
"AND ([ReadingLevel] = ‘Advanced’) " &
"GROUP BY [BookTitle], [Author], [Publisher] " &
“HAVING NOT (MAX([ReaderEmail]) IS NULL);”

Before adding the columns <90, 91-120, and >120 to the spreadsheet, the query executed without any issues. After adding them, the problem started. To address this, I’ve tried adjusting the syntax in various ways:

  • Using the original column names with special characters: MAX([“”<90""]) AS [<90]
  • Giving the column a plain English alias: MAX([“”<90""]) AS [UnderNinetyDays]
  • Removing the quotes and brackets: MAX([<90]) AS [UnderNinetyDays]

None of the above attempts have resolved the issue. The Excel spreadsheet has the exact column names as <90, 91-120, and >120. I am beginning to think there’s a special nuance to the syntax required when such characters are involved, specifically in the context of UiPath’s SQL query execution against Excel data. This is a screenshot of the excel columns. Anyone know what the issue might be?
2024-02-09 15_40_12-Stop work distribution list January 2024 READY.xlsx - Read-Only - Excel

2

Hi @TonyO

Try this query once:

"SELECT [BookTitle], " &
"[Author], " &
"[Publisher], " &
"COUNT([BookID]) AS [TotalBooks], " &
"MAX([ReaderEmail]) AS [LastReaderEmail], " &
"MAX([CheckoutDate]) AS [LatestCheckoutDate], " &
"MAX([DueDate]) AS [DueDate], " &
"MAX([LibrarianEmail]) AS [LibrarianEmail], " &
"MAX([LibrarianName]) AS [LibrarianName], " &
"MAX([OverdueDays]) AS [MaxOverdueDays], " &
"MAX([<90]) AS [UnderNinetyDays], " & 
"MAX([91-120]) AS [NinetyOneToHundredTwentyDays], " &
"MAX([>120]) AS [OverHundredTwentyDays] " &
"FROM [Books$] " &
"WHERE ([DueDate] < GETDATE()) " &
"AND ([Category] IN ('Fiction', 'Non-Fiction', 'Biography', 'Children')) " &
"AND ([ReadingLevel] = 'Advanced') " &
"GROUP BY [BookTitle], [Author], [Publisher] " &
"HAVING NOT (MAX([ReaderEmail]) IS NULL);"

Hope it helps!!

3

I tried this query but unfortunately got the same issue. I also tried the following for all three added columns:

  • Using the original column names with special characters: MAX([“<90”]) AS [<90>]

  • Giving the columns plain English aliases: MAX([“<90”]) AS [UnderNinetyDays]

4

Have you tried escaping the special characters with a backslash?

Example:

[\<90days]

[\>120days]