Bulk Insert from Excel to SQl

Like (0) Share

Report

Posted on 10 Jun 2022 07:32:29 by Riyaz_riz11

3,603 Super User 2025 Season 2

Is there is anyway to bulk insert into database in Power Automate desktop, instead of looping, i will get data in table format, can i push bulk data into DB

@VJR

All responses (9)

Answers (1)

Thanura09 186 on 19 Jul 2023 at 13:57:18

Like (0)

Report

Re: Bulk Insert from Excel to SQl

you can get the file id by using graph explorer, take the file id and and use it as the file
https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0
Jojie 6 on 12 Jul 2023 at 04:24:56

Like (0)

Report

Re: Bulk Insert from Excel to SQl

I know that this is an old thread, but I came up to this problem right now.

Can anyone guide me how to use the filepath from SharePoint excel file rather than the Local file path?

Thanks!
VJR 7,635 on 25 Nov 2022 at 07:55:17

Like (1)

Report

Re: Bulk Insert from Excel to SQl

Hi @Abhilash_Swain

Just did a high level research and they all say using the OPENROWSET(BULK) function.

Import JSON documents - SQL Server | Microsoft Learn

How to Import JSON Data into SQL Server? - GeeksforGeeks

Could you explore some more on that. If its works for you then yours will be the first answer on the forum for importing a json into database :).
Abhilash_Swain 901 on 25 Nov 2022 at 07:08:43

Like (0)

Report

Re: Bulk Insert from Excel to SQl

Hi @VJR ,
I am using an Azure SQL server.
My csv and Json file is in onedrive .

One drive link:- (Ex:- https://my.xxx.com/personal/abhilash_swain_xxx_com/_layouts/15/download.aspx?UniqueId=d079e37524c640f3bac299f7ccd88b40&e=4h8518)

Local Link:- "C:\Users\Abhilash.Swain\Downloads\test.json"
VJR 7,635 on 24 Nov 2022 at 12:27:28

Like (0)

Report

Re: Bulk Insert from Excel to SQl

Hi @Abhilash_Swain

Haven't tried that, but

i. Can you share what path of OneDrive are you using

i.i. Hopefully the OneDrive is fully synced when the file is used

ii. What is the error you are seeing when you mention "query is not working"

iii. And what exactly you meant by "pushing json data" when it is used for csv data
Abhilash_Swain 901 on 24 Nov 2022 at 12:17:44

Like (0)

Report

Re: Bulk Insert from Excel to SQl

Hi @VJR ,

I am downloading the excels/CSV file from a drop box and storing it in one drive. The query is not working for server files . Have you tried that?

I am trying to push a JSON data. Any idea how to achive?
Verified answer

VJR 7,635 on 10 Jun 2022 at 09:24:39

Like (3)

Report
Re: Bulk Insert from Excel to SQl

No need to read excel as Db.

If you already have the data in Excel then set up Sql connection and use the Execute sql statement with the BULK Insert command. The command itself uses the Excel file parameter.

Below are some examples for csv.

You may not need all the parameters. So search for the actual syntax and see which parameters you will need.

BULK INSERT Employees
FROM 'D:\data\employees.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
);

BULK INSERT Sales FROM 'C:\temp\1500000 Sales Records.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR='\n', BATCHSIZE=250000, MAXERRORS=2);
Riyaz_riz11 3,603 Super User 2025 Season 2 on 10 Jun 2022 at 09:12:53

Like (0)

Report

Re: Bulk Insert from Excel to SQl

Can you tell me in brief, so i hav a excel contain 1 lakh of data, so i will read eexcel as a db so the data was stored on exceldata table variable,

how can i pass this table data directly database, how to insert.
VJR 7,635 on 10 Jun 2022 at 09:09:36

Like (0)

Report

Re: Bulk Insert from Excel to SQl

There is a BULK INSERT sql statement itself which you can make use of after loading the data into Excel.