Unanswered

Excel File in Sharepoint uploaded to SQL Table

(0) Share

Report

Posted on by Karip426

We have files uploaded to a Sharepoint site. The excel files are all the same format but each will have a different name. I won't know the name of the file coming in. Also, I need to take that file and upload it into a SQL server database and the table name will need to be the same name as the file. I understand I need to get create my flow using the "when a file is created or modified" and I am assuming the next step is "get tables" What I don't understand is how to get the variable and define it. Can someone show me how to do this?

For example there is a file named ABC_123.xls in my sharepoint. I need that file to be picked up and loaded into SQL database and the table name would then be ABC_123.

If another file comes in as DEF_567 then it will be loaded and the table would be DEF_567. Can someone show me the steps for this, specifically the defining the variables and how it is carried on down into the flow?

Thank you

Categories:

Building flows

Screenshot 2024-0...

I have the same question (2)

All responses (3)

Answers (0)

Michael E. Gernaey 53,315 Super User 2025 Season 2 on at

Like (1)

Report

Hi

Your description is not clear. You say you need to upload the Excel File into SQL, but then you talk about Get Tables. Uploading the file makes me think you mean store the file, versus take the data from an Excel table and putting it into a SQL Table, which it doesn't say.

So I am unsure of the ask, and where does SQL store the data (an image file or something???) and is the row using the File Name.

Please maybe do a Step by Step walk through with more details so we can help.

Was this reply helpful? Yes No
Suggested answer

NsL Coder 475 Super User 2025 Season 2 on at

Like (0)

Report
here is the "main steps" (i'm also going to assume that we are not dealing with updated and modified file needing to modify the sql table))

trigger on file created in a folder

issue a CREATE TABLE @{name of the file} with all the column definition

assuming the file is an excel file that already have the data formatted as table, you will use list rows in table action to get all the rows from a the table

Use OPENJSON() to "parse" the json data returned by the list rows in table action so you can do the insert into SQL table in 1 action rather than inserting one row at a time in an apply to each look

you may want to look into how OPENJSON works ( I am assuming you are using T-SQL)

INSERT INTO @{name of table} SELECT * FROM OPENJSON(@json) WITH ( colname DATATYPE '$.json_property_name', colname2 VARCHAR(100) '$.json_property_name' .... .... );

@json is a formalParameters with VARCHAR(MAX) or NVARCHAR(MAX) depending on your character set and assigned the value of the array returned from list rows in table.

for example

Was this reply helpful? Yes No
Karip426 15 on at

Like (0)

Report

I need to take the data from the Excel file and put it into a SQL Table. The files are never named the same thing and the sql table name needs to be the same as the excel file name.

Was this reply helpful? Yes No