Bulk insert Using xlsx file

Question

User-664080073 posted

Hi,

I have an xlsx file with data like 

Car door electric contacts or car door interlocks

Hydraulic

Car

Monthly

And having around 65 rows 

I want to bulk insert this data to a table having following rows 

TaskCategoryID, TaskTypeID, Name, MinMinutes, MaxMinutes, CreatedDate, CreatedBy, IsDeleted, RecommendNotifyPeriod, EquipmentTypeId, FrequencyID, EquipmentContractName

But TaskCategoryID,TaskTypeID, FrequencyID are all foreign keys.

How could I use bulk insert to insert the data or is it better to insert normally through insert query.

Thank You in Advance.

Answer 1

User726159118 posted

Hi NaidyVinay,

This is incomplete information, Can you provide more details on this, So i can tell you what to do.
because you have provided 4 column of excel and two table column. based on this it is difficult to Answer.

How ever if you have idea about manual process. then create one console application in which read the excel file. and then loop all the rows for inserting data.

Mark As Answer If it is helpful.

Regards,

Answer 2

User347430248 posted

Hi NaiduVinay,

There are many ways to insert data in to SQL server from Excel.

If you can manage the values for your foreign key fields then you can try to refer examples below.

-> Using OPENROWSET:-

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Data$]);
GO

-> using OPENDATASOURCE:-

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\Desktop\Data.xlsx;Extended Properties=Excel 12.0')...[Data$];
GO

-> using BULK INSERT Command:

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'D:\Desktop\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

Reference:

Import data from Excel to SQL Server or Azure SQL Database

Regards

Deepak