locked
Bulk insert Using xlsx file RRS feed

  • 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.

    Wednesday, March 7, 2018 11:00 PM

All replies

  • 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,

    Thursday, March 8, 2018 5:26 AM
  • 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

    Friday, March 9, 2018 7:40 AM