Is it possible to create a separate datatable for each csv file in folder

Help Studio
1

Context:

I have 31 csv files that are huge, ± 147823 rows of data per file.

  1. Would it make sense to merge files this big, or will I run into problems when processing them?

  2. That is why I thought of creating a separate datatable for each csv file, but have no idea how to generate the datatable with a unique counter or name

I got this far:

image
image491×655 43 KB

Please can you help with stating which option will be more viable?

2

@Nyx,

  1. Merging files into one would be sluggish better to process individually.
  2. If your all files/datatable going to have same columns, after generating DataTable from csv file, use Merge Data Table into a full DataTable.
    If files/datatable going to have different columns you can store all the datatables into a DataSet using below logic. DataSet can hold any number of DataTables and you can easily manipulate the data as you wish.

image

Thanks,
Ashok :slight_smile:

3

@Nyx

You can create a dictionary with key as string and value as datatable…

Ao that each file can be read and added as different tables…the name or key can be the filename

Cheers

4

All my CSV files have the same columns. Is my flow correct by doing a for each? Would that not overwrite the 1st datatable created?

Is this approach correct?

image
image587×762 52.5 KB

5

Your dt_CollectionDelta will be overwritten but we are already merging that data into dt_CollectionsFinal before being overwritten. So you are good. That’s correct approach. Test it.

Make sure dt_CollectionsFinal is declared outside of the Foreach csv file reading scope.

6

Create Dictionary (Of String, Datatable)

image
image1245×29 3.09 KB

For Each in DirectoryInfo (see here: Using DirectoryInfo.GetFiles instead of Directory.GetFiles, advanced filtering)
– Read CSV into yourDict(currentItem.Name)

image

Then you’ll have a dictionary of datatables. The key will be the filename. So to get to the datatable for a particular filename you reference yourDict(“filename”). You can use that expression anywhere you’d use a normal datatable variable.

7

Awesome I will try it

1 Like
8

What am I doing wrong?

image
image1028×535 59.6 KB

image

9

@Nyx, What’s datatype of the currentitem. You will get it in property of For Each. It seems the current item datatype is string

10

You have to put a filter into the () for GetFiles, if you just want all files put "*" or if you want CSV put "*.csv" etc

Get rid of the select, you don’t need it.