Error on OLEDB Execute stage

[FrankieTEWV]

Hello,

I have an excel file in the xlsx format. I need to do a find and replace function so it finds all the comma's and replace it with a blank space.

I am using the OLEDB actions - the OLEDB 'Set Connection' and 'Open' action work - however i am getting an error on my 'Execute' stage.

The excel report has the below data in it and the commas are just in the 'Position Title' column - please note you wont see commas in my screen shot below as its just a snippet but there are commas in the data.

My Execute SQL expression is 

but the error i am getting is .....

I then did a test and updated the OLEDB Execute SQL query to find all @ in the email column and replace with # 

i got a different error this time 

but when i checked the excel report it had changed all the @ in the emails to # .. so it did work but cant progress to the Close OLEDB action due to the pop up.

Can someone please help me find a solution to the execute SQL stage to find  the commas and replace with blanks in the Position Title column.

Thanks

Frankie

[Mohamad_747]

Hi @FrankieTEWV 

Can you try to put [Position Title] like this ?

I mean put square brackets

Let me know 🙂

[Mohamad_747]

your flow should look like this : 

 

in set connection calculation stage put this : 

Replace("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=***PATH***;Extended Properties=""Excel 12.0 Xml;HDR=YES;""","***PATH***",[Path])

and store it in con like this : 

in execute : 

"UPDATE [Sheet1$]
SET [Postion Title] = REPLACE([Postion Title], ',', '')"

[FrankieTEWV]

Thanks so much for responding, my connection calculation is this:

the reason why i don't have "Excel 12.0 Xml" is because my excel doc is not xlsm, its a xlsx so ive used "Excel 8.0"  and the connection does work with that 

i got that from the below instructions 

I did try adding the square brackets and copied your SQL you gave  ....

but i get this error

[Mohamad_747 · Accepted solution]

Hi @FrankieTEWV 

With pleasure 🙂

for the SQL i forget to put the 'i' in Position so set it like this : 

"UPDATE [Sheet1$]
SET [Position Title] = REPLACE([Position Title], ',', '')"

For the connection, try to set it up like I did (just to test and see if it works)

Replace("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=***PATH***;Extended Properties=""Excel 12.0 Xml;HDR=YES;""","***PATH***",[ExcelFile]) 

please note that Excel 12.0 Xml is used for xlsx files (Excel 2007 and later) that are stored in an XML-based format.

[FrankieTEWV]

Hello,

Thank you Ive added the calculation stage and stored it in 'con' 

I put the 'con'  in the connection string ... see below

 and both the open and set connection actions worked ..

i updated the execute action as you advised

but unfortunately still getting the error ..

i checked the position title name on the excel report and could see the name as Position_Title see below ..

so i did update the code to say Position_Title ...

then got a different error ..

Apologies I'm not sure if i needed the underscore in the Position Title or not but either way with it or without it I'm still getting errors.

[Mohamad_747]

Hi @FrankieTEWV 

Can you tell me which version of  excel are you  using? I did the same steps again on my pc and it works fine. I have already had a similar error in the past because of a special character hidden in the column. what I can suggest you try to test what you did on another excel file (create it and put only 3 columns with 3 rows) and run the script on it. if it works it means that there is a special character in your column that prevents execution

[FrankieTEWV]

Hi Mohamad,

See below what version of excel i am using

when using the OLEDB actions i had to install AccessDatabaseEngine 64 bit, however that didnt work with any OLEDB actions so i had to remove that and then install the AccessDatabaseEngine 32 bit and it worked .. im just wondering as the excel version i am using is 64 bit could that be why im getting issues?

I will try your suggestion of creating my own excel file and run the script - il let you know how i get on - thank you so much for all your input and help its been so useful.

[FrankieTEWV]

Morning,

I did my test excel ran the execute stage and it worked .. so you are right there must be some kind of special character in that collum on the excel report that is preventing execution.

Which would mean there would need to be a manual intervention on that report once downloaded to remove special character before the execute action could run.

I just want to say thank you so much for your help ive learnt a lot from this.

Thanks

Frankie

[michaeloneil]

Hi @FrankieTEWV 

Whenever i get the error 'Datatype mismatch' it usually means the values Im trying to enter through oledb are different from the format in the cells I'm writing to e.g. writing text to cells that are formatted as numbers. Have you looked at the format on the cells you're updating to check they match what you want to do?