I have a CSV file. I need to insert a new column at the beginning with the file name in it

Help Studio
, ,
1

I am approaching the problem using the following method.
1.Creating a datatable (dt) with the filename
1.Reading data from csv into another datatable(dt_csv)
now I wanna join those two datatables in such a way that first column value in every row is the filename.
Finally, I am storing it in another csv file.

I am open for any other approaches as well.
Thanks in advance :slight_smile:

2

@Aravinda_Gangaram

Use Join DataTable activity to join both the DataTables and then use Write CSV activity and pass Join DataTable output to it.

3

image
image990Ɨ1588 40.8 KB

So I created the flow in this way. Everything ran successfully but I couldnt see a file in the destination.

4

you can use the add data column activity

That will be much easier

to reorganize that
just use a filter datatable
input dt as it is
Just add column sequence in Keep Columns section, as you want, output datatable will be the one that you need.

5

Hey @lakshman

How join datatable will add the column in the start?
is there any way to do that?

6

@rahulsharma How to filter or select the column which is required to join?
image
Assuming that the filename is available in dt_csv datatable.
I declared a variable column_name as ā€œfilenameā€

So what should I put up in the join?

7

I didnā€™t mention to use Join datatable

i dont think Join datatable is the solution.

You just need to add a new column in the datatable, which you got after reading csvā€¦ ads using ā€œAdd Data Columnā€ activity for datatable

then just use filter datatable
in input put the datatable you have after adding the column

and in the output, you put the order in which you want to arrange the columns (as you want the newly added column as the first column)

8

Got it thanks.
For now that works. I will build my solution based on your suggestion.

Thank you.

9

@rahulsharma

I thought both the DataTables has some common columns and based on that he wants to join the tables.

10

This is how it works.

Add Data Column to add.

Then, filter datatable can be used to rearrange the sequence of columns. Just pass the datatable and in output columns mention the sequence you want.

AddColumnAtFirst.zip (2.6 KB)

11

it looks like the file name is always the same fot the particular dt_csv
then we can

  • add the column filename
  • moving it on first position with setOrdinal method used within a Invoke Method activity
  • setting the file name by assign activity | dt_csv(ā€œFileNameā€).Expression = " ā€˜YourFileNameā€™ "

so it will set it to all cols from dt_csv without doing it by a for each X activity

12

Just in place of Filter. I am using Invoke method activity and using SetOrdinal Method inside it to set the required column at the first position. So, Even if there are few more rows in my raw_data file I wont have any issues in the future.

sadly, I am unable to add an attachment :frowning:

Thanks a lot @lakshman , @rahulsharma
for your insights and help. :slight_smile:

13

I didnā€™t know that we could assign it that way. Thank you @ppr . I will update that part of my design.

I was using For each loop :stuck_out_tongue:

Thank you :slight_smile:

14

grafik
grafik1046Ɨ562 78.1 KB

grafik
grafik486Ɨ613 53.2 KB

Find starter help here:
DCExpressions_AddColOnStart_SetFixedString.xaml (7.5 KB)

15

Hey @ppr it shows that theres an error stating:

Option Strict On disallows implicit conversions from ā€˜Stringā€™ to ā€˜Integerā€™.

16

filename column datatype is string?

17

Yeah

18

Please show some screenshots from the imp. as you can see from the immediate panel and helper.xaml was working we have to inspect some parts. Thanks

feel free to share your xaml with us

19

properties of that column

image
image719Ɨ331 14 KB

20

image
image751Ɨ369 10.9 KB

When I am using Expression