How to extract files names and write them into excel?

Hi everyone!

I would like to extract only the name of several files and write them in a column in excel.
How can I do that?

Thank you in advance for your help on this :pray:

1 Like

Hello @Lilli1 what kind of files are we talking about?
You can extract values using String manipulation and Regex, then put them in Excel tables using Write Range Activity.

You can get more information about this on Ui Path Academy

1 Like

Take a look at this post :slight_smile:

Try searching on forum more, you will probably find most of your answers.

1 Like

Hi both,

Thank you for your quick reply :slight_smile:

Iā€™ve tried this:
1/ Assign files to a variable
2/ Open the folder
3/ For each with Get OCR text
4/ Generate Data Table
5/ Excel application scope with write range (using the data table)

Main.xaml (10.2 KB)

@srdjan.suc: Itā€™s PDF, you can see what Iā€™ve used above, but I have this error message ā€œFor Each: Object reference not set to an instance of an object.ā€
@TimK: Thank you, yeah actually Iā€™ve already saw this one but after trying it, it didnā€™t work :frowning:
I got this error message ā€œFor Each: Object reference not set to an instance of an object.ā€.

1 Like

For Each: Object reference not set to an instance of an object

This exception happens when you try to work with Null objects (basically it is a null pointer exception).

Could you upload the PDF that you are working with?
Maybe we can use Read PDF activity and then extract the desired results

1 Like

@Lili1 i just gone through your flow you haven,t provided the for each input and also there is and open application activity i donā€™t know why you provided it and inside the for each you directly used get ocr text we should not give like that.in that you can give read pdf text and from there you will get output as string and by string manipulation store the value in a variable.
and you can write it in a excel.

1 Like

@Lili1 Run it in Debug mode and it will identify what variable / argument is not set correctly.

It may be that you havenā€™t extract the files correctly etc into a list?

You should be able to simply get all files within a folder, if you only want certain filetypes etc (PDF)? then you can use an if statement within a for each fileInFolder to determine if filetype is CSV then add to collection.

You can convert you list to a datatable or add individual items from the list to a datatable using a for each then add data row.

1 Like

Hello @Lili1,

Try to use Path.getFileNameWithoutExtension(ā€œINPUT PATH STRINGā€) ā†’ extract file name without extension
OR Path.getFileName(ā€œINPUT PATH STRINGā€) ā†’ extract file name with extension

1 Like

Hi everyone and thank you very much for all these reply!
I have few questions:

On the recommandation of @venkatmalla6 Iā€™ve added input in the for each! It is now working but only for the document Iā€™ve selected via OCR. Itā€™s not executed on all the files present in the folder. How can I do it on several files? Not only one. Iā€™m open to any solution that would work :slight_smile:

@srdjan.suc: How can I use PDF activity? I mean how can I specify that this is the file name I want to get?
Do you have an example?

@venkatmalla6: If I remove the ā€œopen applicationā€ the OCR cannot work, because UI element is not found.

@TimK: How can I convert a list into a Data Table? Because Iā€™ve tried but it did not succeded. Do you have an example?

@sangram: Where can I use this ā€œPath.getFileNameWithoutExtension(ā€œINPUT PATH STRINGā€)ā€ ?
In an ā€˜assignā€™ activity I guess, but how would look like the sequence then? Do you have an example?

Sorry for all these questions, Iā€™m new on UiPath and itā€™s quite challenging, I apprecite your help :slight_smile:

1 Like

@Lili1 does the pdf format of all pdfā€™s same

Yes:

1 Like

@Lili1 i mean inside the pdf does all the files look alike or not

I assume @Lili1 only wants the file names, not content of PDF from the question.

So Take Contrat_S / Contrat_X etc and put them into Excel file?

If you want all the items on the same row - To convert list to array and add that to a datarow (using add datarow activity)

Convert list to array ā†’ list.ToArray in input.

Or if you want to have them in a single column, you can do a for each item in the list, then using add datarow add the item.

1 Like

Yes indeed, the idea is to get only the filesā€™names: ā€œContrat_Sā€ ā€¦ and put them into excel.

@TimK: So you suggest to add ā€œlist.ToArrayā€ in a second ā€œFor eachā€ loop?
1/ Assign files to a variable
2/ Open the folder
3/ For each with Get OCR text
4/ For each with as input ā€œlist.ToArrayā€?
5/ Excel application scope with write cell

1 Like

So you dont need to open the folder and use get ocr text.

If you build a new datatable and give a column of String called FileName

you should just be able to use Directory.GetFiles(ā€œFilePathā€)
This will give you a list of all the files within that folder.

So For Each item in Directory.GetFiles(ā€œFilePathā€)
Assign myFileInfoVar = new FileInfo(item)
You can use Add Data row and myFileInfoVar.Name will give you the name.

Once you have your whole datatable - you will then use write range activity to write to Excel

2 Likes

Iā€™m gonna try this ASAP and let you know how it turns out!
Thank you so much for your time on this :pray:

1 Like

@TimK: Ok so I have an error message saying that:

Do you know why?

1 Like

Hi @Lili1

Could you try adding a .ToString to the right side of your assign activity?

Hi @nlee1131: Unfortunatly it says ā€œ[ā€¦]cannot be converted to ā€˜Stringā€™.ā€

Any particular reason for making FileInfo a type string?