Question

Excel as Database Text Trailing Spaces

  • 22 October 2023
  • 4 replies
  • 48 views
M
Badge +1

I created the below program that copies Column B to Column C using Excel as a database action.

 

There is a side effect for the insert/update/delete action at line 5; that does not keep the trailing spaces after "aa    " in line 2 after the copy.

 

 

So, when I check for equality after the copy only lines 3,4,5,6,7 are considered equal.

 

Is there a way to keep trailing spaces and prevent this automatic trimming behavior?

 

 

4 replies

R
Userlevel 3
Badge +8

Hi @mohamed.badran,

Can you please give a try by adding “IMEX=1” to the connection string in database connect action. 

More details here: https://www.connectionstrings.com/excel/#:~:text=Treating%20data%20as%20text

M
Badge +1

Hi @ravi.pothana

 

Thanks for your answer. I tried added the “IMEX=1” to the connection string but it caused the following error.

 

R
Userlevel 3
Badge +8

Hi @mohamed.badran,

Based on the information provided, this error usually occurs due to one of the following scenarios:

1. The SQL statement contains a special character, such as a single quotation mark (').

2. The SQL statement fails due to a variable with incorrect content used to build the SQL statement.

3. The values are not in the expected format. For example, you might be fetching numeric values, but those values are stored as text.

 

For the first scenario, you can use the "String > Replace" package before executing the SQL command. Search for the character ' and replace it with ''.

For the second scenario, I recommend tracing the variable over the bot and correcting the step where the variable gets the wrong value.

For the third scenario, ensure that the values are in the expected format. For instance, if the values are stored as text, ensure you're fetching them as text.

M
Badge +1

@ravi.pothana I have added a message to display the SQL statement after adding the variable $strTemp$ to the content of the query.

 

And this is what I feed to the updated statement - line 6 - in each iteration:

Update [Sheet1$] set [C]= "aa     " where [A] = "a";

Update [Sheet1$] set [C]= "bb" where [A] = "b";

Update [Sheet1$] set [C]= "cc" where [A] = "c";

Update [Sheet1$] set [C]= "dd" where [A] = "d";

Update [Sheet1$] set [C]= "ee" where [A] = "e";

Update [Sheet1$] set [C]= "ff" where [A] = "f";

 

The problem is that the action at line 6 “Database: Insert/Update/Delete” have a side effect that trims the trailing spaces automatically and sets column C with the wrong value “aa” without the trailing spaces. So, when we check for equality between Column B and C at line 2; we find that column B containing "aa     " is not equal “aa” in column C.

This means that the update statement at line 6 did not just update the value in column C with the value in Column B; But, it actually changed that value to an unexpected value.

You can try this simple program your self if you wish.

 

 

Note:

This is my connection string that worked for your reference:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Users\rpa\Desktop\testExcel\test.xlsx"; Persist Security Info=False; Extended Properties="Excel 8.0;";

 

 

Thank you.

Reply


Terms & ConditionsCookie settings