insert data from csv/excel files to SQL Server

(0) Share

Report

Posted on by parry2k

Hi there,

I'm planning to do following:

- read files (csv/excel) from one drive folder

- insert rows from files in sql server table

- archive the file

Trigger - it can run on schedule

File Format - will be fixed standard format for all the files

Is it possibe?

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

parry2k 14 on at

Like (0)

Report

Hi there,

I'm planning to do following:

- read files (csv/excel) from one drive folder
- insert rows from files in sql server table
- archive the file

Trigger - it can run on schedule
File Format - will be fixed standard format for all the files

Is it possibe?

Was this reply helpful? Yes No
v-micsh-msft on at

Like (0)

Report

Hi @parry2k,

Well, based on what I know, I think this is not achieveable.

How would you like to read the file from OneDrive folder?

In order to have the Insert Row into SQL Server table work, we should take use of Excel->Get Rows Action, after the Schedule trigger.

Then add the SQL server Insert Row action:

For archive file, could you please explain a bit here?

If you mean to delete (or move it to another place) the corresponding Excel file in OneDrive folder, then we need take use of OneDrive Action->Delete file (or copy and then delete), but using this action would reqiure the file identifier in OneDrive, which currently I have no idea to get the corresponding file identifier.

Further, for files, we would need to take use of OneDrive List files action, then take use of Excel Action for each file to parse the table content, after that, we need to add another apply to each for each row, which(nested Apply to each) currently is not supported.

Regards,

Michael

Was this reply helpful? Yes No
parry2k 14 on at

Like (0)

Report

Hi see my comments:

For archive file, could you please explain a bit here?
If you mean to delete (or move it to another place) the corresponding Excel file in OneDrive folder, then we need take use of OneDrive Action->Delete file (or copy and then delete), but using this action would reqiure the file identifier in OneDrive, which currently I have no idea to get the corresponding file identifier.

Yes, basically want to copy to another folder, delete from source folder, copy/move to another folder on one drive. seems like it is not possible at this point?

Further, for files, we would need to take use of OneDrive List files action, then take use of Excel Action for each file to parse the table content, after that, we need to add another apply to each for each row, which(nested Apply to each) currently is not supported.

Could you please let me know how it is possible, should I add "One Drive List files action" and then "Apply to each file" container and move all you suggested in that containter correct?

Is there a solution for CSV files similar to excel file?

Was this reply helpful? Yes No
Courtenay 52 on at

Like (0)

Report

Hi there,

Courtenay from Parserr here. Just wanted to let you know. This is exactly what Parserr does! We were added to Flow last week and very excited about it.

Parserr allows you to turn incoming emails into useful data to use in various other 3rd party systems.You can use to extract anything trapped in email including email body contents and attachments.

We have a handy "query" function, where you send the CSV/Excel as an attachment (or autoforward it to us) , and then setup the query to extract the rows you need from your CSV/Excel. These rows are then available in Flow to send to SQL as you mentioned.

https://parserr.com

Let me know if you need any help. Id gladly set this up for you.

Was this reply helpful? Yes No
anton-khrit 197 on at

Like (0)

Report

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

Once you parsed CSV you can iterate through result array and use this data to insert into SQL table.

Was this reply helpful? Yes No