Need to fetch all the files from a folder in sharepoint and load those files in different table in database. And also the file have data in its name and that also need to be added as a column
It is forum policy that when you use AI to generate an answer, you say so.
Appreciated the answer.
But can you please be specific .
This i tried. i am able to fetch the list then for in for each how can i call all the files and specify the specific tables for each files which is to be loaded
Hello @Gautham_Pattabiraman,
Can you please help in this
Welcome to the community
- Use for each file from office 365 activities
- To read data depending on file type you can use read range,read file read csv etc
Cheers
Thank you so much for the update.
I need to fetch all the files from the folder and those files are having different set of columns with some like aggregate value at the first row and from second row ownwards some columns, and some have directly few columns.
I need to extract all the columns from each file and load into different tables in database.
Then first read the cell and try to check which type of file it is and accordingly reqd the dta for eqch type…you cannot generaalizw this scenario
Cheers
Hi @josesusan044 ,
If possible can you share few sample files. Or snaps of sample data from the files.
Need to check the data which we need to deal with.
If sharing exact files or data is not possible, please try to share a sample set with which we can form the solution and apply it on the real data.
Are all the files in sharepoint folder of same type for example excel files.?
If yes then here is a general algo which you can use,
Step 1 : Use the For each File /Folder +Download File activity combo to download all the files from the sharepoint folder to local folder.
Step 2 : Use variables to store the table name and file names ( Downloaded)
Step 3 : Read the data and use insert statement to push the data into db (one file at a time)
Need more details to give more elaborated steps.
However hope this helps you form a solution.
Happy automating.!
Thanks,
Gautham.
Hello @Gautham_Pattabiraman,
Thank you so much for the update.
The folder contains more than 10 files of .xlsx type or pdf and i need to fetch only the xlsx file and each file data to be loaded in the different database tables since all the files have different column and values
@josesusan044 For achieving this take a assign activity, create a array of string variable,
arr_Input_Files = Directory.GetFiles("FolderPath","*.xlsx")
Using Above assign you will get all the file paths of Input Excel in a array of String.
Then take a foreach activity put arr_Input_Files
Then in Body, Read range and further logic accordingly.
Regards,
Ajay Mishra
Hi @josesusan044 ,
Attaching a flow (Sequence), That has the base structure you need to iterate in local folder and take the required action after picking and assigning the data table name you want the data to be inserted to.
Hope this helps you out.!
TestProcess.zip (19.7 KB)
Check the sample flow and let me know if you have any further queries.
Thanks,
Gautham.
Hello @Gautham_Pattabiraman ,
Thank you so much for the quick update.
Tablename mentioned in the sample is the tablename of the database table or datatable.
And where is the datatable name assigned and
one more scenario need to add …if file name will be like “abc 4.1.24.xlsx”, so need to extract the 4.1.24 and add as column in the table
Hi @josesusan044 ,
table name variable should have the Data Table name inside db.
DB name will be present in the connection string you are to connect with db while configuring Execute Non Query Activity to execute the insert statement.
File name you can change how you want it by , Simply click on file1.xlsx(it is a editable field) only.
Hope this helps to shed some light on your doubts.
Thanks,
Gautham.
Hello @Gautham_Pattabiraman,
Thank you for the quick response.
Example my file name is file1 6.2.24.xlsx, file2 6.5.24.xlsx and
I need to load whole file that is file 1 and file 2 data to different table in database and also need to add a column with that filename extension that is 6.2.24 as well
Hey @josesusan044
Inside the matching case of Switch(File Name based), Assign the appropriate SQLConnectionstring,SQL Provider Name, Data Table Name / Query in a string type variable.
and Read Range Workbook (OP will be Data table), manipulate and used to further to execute query
Hello @Gautham_Pattabiraman ,
Need a small help the sample that you shared will be using swith case “file1.xlsx”
So if my filename is file1 5/1/24.xlsx i am giving a table name.
So every month and date in the file name change and i need to manually change case.
So is there any way like switch case file name only take file1 not full filename like “file1 5/1/24”
Hi @josesusan044 ,
There are two options here,
Option 1 :
Use an if condition instead of switch case,
CurrentFile.Name contains (“FileNametocheck”)
This would allow you to check for filename which you know is matching with the file name from the current iteration of the loop.
Option 2 :
If you can determine the date that would be present at the end of file name.
Use an assign activity, make the date dynamic part concatenate with the filename.
Pass this as Case values inside switch case.
You can try either of the above whichever sounds easy to you.
Hope this helps you out.
Thanks,
Gautham.
Hello @Gautham_Pattabiraman,
So if we have 10 files in a folder, we need to give 8 elseif minimum i guess or any way to modify the switch case only.
Like currentfile.name will take file1 4.3.24.xlsx as per switch case
or any way to take only file1 in the case using currentfile.
Nope it needs to either one of the options.
Have to go for nested if or switch case where you create multiple cases with dynamic name that would match against currentfile.name
Hello @Gautham_Pattabiraman ,
Can you please help me with one example with dynamic filename in switch case