How might I select a value in a CSV file to look-up to a corresponding value in an Excel file, and then copy an associated value back to the CSV file?

Help
1

Hello,

I would really appreciate any help in building the following workflow.

I have two files of data:

  1. A CSV file that contains about 250 rows of transactions. Each row details 10 columns of common data about each of the transactions. Included is the account number which is populated and the customer code which is an empty field.
  2. I also have a separate Excel workbook that contains two columns of data that act as a ‘mapping table’ between an account number and the corresponding customer code.

I would like to achieve the following:
I would like to select the account number for each of the 250 transactions on the CSV file and look up whether it exists in the excel mapping table.
• If it does exist, I would like to copy the corresponding customer code from the mapping table and update it into the corresponding empty column cell of the transaction in the CSV file. Also, if the customer code does get updated, then I would like to update another field in the corresponding column cell with a static data value of “closed”.
• If the account number does not exist in the mapping table then no action should be taken for that transaction, and the next transaction should be interrogated in the same fashion.
This should be repeated for each row up to and including the last transaction in the CSV file.

Many thanks in anticipation of your kind assistance.

2

@TerryD, Hi Below is the procedure and it should work. This is not optimal but sufficient to solve your problem. I was able to do it so you will be able to code with below clues.

1.Read CSV (datatable - data265)
2.Read excel(datatable - Book1table)
3.For each row in data265
4…For each row in Book1table(this is not optimal… but works!)
4.1…IF Account(data265) = Account(Book1table)
4.2…update data265 with accountCode from Book1table
4.3…write Cell( Book1). status column as “Closed”
4.4Loop through all Book1Table rows(like I said it is not optimal but works)
5.Loop through all rows in data265
6.WriteCSV to finalCSV from data265

3

Thank you @viswanarahari that very helpful.
When you say that you tried it and it worked - do you have a copy of the process flow that you would be able to share as an xaml file?
Many thanks,
T

4

Attached is the solution for your requirement and you can expand on this.

-Viswa

csvAndExcelProcessing.zip (20.3 KB)

5

Hi @viswanarahari I imported your process flow but could not get it to work with your csv.265.txt?
image

6

Hi @viswanarahari I fixed the previous error message. However, the final CSV does not update with the relevant account code:

image
image714×382 11.4 KB

7

image

Hi @TerryD, Not sure how you got ‘Untitled.csv’ ? It is not in my files I sent to you.

  1. Book1 - can be your starting excel. Look in that and you can see 'Status’ is ‘closed’ for two accounts. Before you run my xaml, please replace ‘closed’ with space and save the worksheet and close the excel program.
  2. csv265.txt - is your starting comma separated values txt.
  3. finalCsv.txt - is your final text. If you want later rename it to finalCsv.csv.

Hoping above works!.

Thanks and regards,
Viswa.

8

Hi @viswanarahari, thank you for your reply.

It was called ‘Untitled.csv’ as I renamed it that from ‘finalCsv’.

What I need to do is:

  1. Update the ‘Code’ from Book1 to the ‘finalCsv’ file and also the ‘Closed’ status to finalCsv rather than the Book1
  2. I do not need to update Book1 at all as this is simply the mapping reference table.
  3. All updates should be made to the ‘finalCsv’ (where a mapping of ‘Account’ exists between ‘csv265’ and ‘Book1’)

Many thanks

T

9

@Terry, You are most welcome.

By the way, I thought you have read my 6 steps I gave so assumed my assumptions were correct. Based on those six steps I wrote the code.

Can you please let me know the headers( first line of) finalCsv ?

regards,
-V

10

Hi @viswanarahari,

Ah ok, understood. My apologies, I should’ve checked.

The headers for the finalCsv are attached:

image
image1003×45 1.89 KB

  • So where there is a match for ‘Account’ (in the mapping table ‘Book1’) we should then update the Code with that of the code from the mapping table 'Book1". In this instance also update ‘status’ with ‘Closed’
  • If there is no match to ‘Account’ (from Book1), then no action for that row and move onto the next row.

Additionally, the format of the initial ‘csv265’ file is excel workbook.csv rather than .txt - what is the easiest way to encompass this into the workflow?

Many thanks again,

T

11

@TerryD, No worries at all.

I have no preferences. (It depends upon process dependency)

Can you attach sample workbook.csv and finalcsv.csv ? Both having two rows ?

regards,
Viswa.

12

Hi @viswanarahari,

The headers and format of ‘finalCsv.csv’ are the same as the initial ‘csv265.csv’ input file

Here are the headers for the ‘Book1.xls’ (mapping table) - only two columns.
image

Many thanks,
T

13

Hi @TerryD, Please run the attached. I have not added all the headers. But even if you add the headers and change the order it should work.

Thanks and regards,
Viswa.
csvAndExcelProcessing.zip (19.8 KB)

14

Hi @TerryD, You only have to add headers to startCsv.csv. No need to add header to finalCsv.csv.

Let me know if you have any further questions.

image

Thanks and regards,
Viswa.

15

Hi @viswanarahari,
Thank you very much for your help with this. It was really useful and the process now works as planned. Great job.
Best regards,
Terry.

16

Thank you so much @TerryD for the feedback.

Can you please mark this as solved ?

Happy coding!
with regards,
Viswa.

17

Hi All,

I have an excel file with Columns Name and ID.I got ID from a web page and stored into a variable.

Now the requirement is to open the excel and lookup for the ID got from the Web page and need to pull the corresponding name in the next column.Can someone guide me how to write the code.
@ClaytonM @arivu96

Thanks,
Siva