Filter data if value contain number 1 append 123

How To
1

image
image1648×400 44.5 KB

Hi Everyone,

I need help with the scenario described in the attached screenshot.

Here’s what I’m trying to do:

  1. Read CSV Attachments from Outlook and Upload to OneDrive to read
  • I’m reading the CSV file using Excel 365.
  • When applying a filter in Excel, I’m defining a range like A1:Z100 because the number of rows can vary between 10 and 100. To be safe, I’ve chosen a higher range and plan to filter out any empty rows later.
  1. Questions:
  • Is there a way in Excel to dynamically determine the exact number of rows with data?
  • I need a filter to append “123” to values in column H if they start with “2.”
  • During SQL insertion, I want to check if a value from column H exists in the database but not in Excel. If so, I need to delete that record from the database.

Any guidance on these points would be greatly appreciated!

Thank you,

bundle-output.txt (346.7 KB)

2

Hi Malik,

is this vor csv files only? I’d recommend not using excel but try it with the built in csv modules (parse csv). But anyway, concerning the first two questions:

  1. This is more of an excel question but yes, you can use a COUNTIF formula. Just google “excel count if not empty” to see examples.

  2. A filter does’t append anything. Do you want to do this in excel? Then again, you can use a formula, but you would have to use a new column. There you could extract the first letter using LEFT(), then check if its a two by surrounding it with an IF(). Somewhat like this: =IF(LEFT(H2;1)=2;H2&123;H2).

I assume you’d have to use “update column” modules to apply both formulas to your file.

  1. This is a bit cumbersome with excel I think, I never use excel for that kind of processing and I’m no expert on the modules.

Maybe someone else will jump in with a better idea…

3

John, Thank you for reply.

For point number 1, retrieve module ask for range not formula to fetch record…
For point number 2, I am aware filter does not append anything, what I can use In between excel and mysql module to append value? Iterator making sense but not sure what to do to make data append recurrsively with each row item H.
I have attached output of my Excel module you can review suggest will be great help.

point number 3, I do not want to do that in excel i just want to check in mysql record example compare two array like excel output [1,2,3] mysql output [1,4,6] delete 4,6 in mysql and add 2,3.