none
upload data from excel file into sql server RRS feed

  • Question

  • Hi Guys,

    I appreciate it if you can help me with how to upload a dirty excel file into the SQL server table. 

    (I don't want to clean the file manually, I'm trying to automate it.)

    I have an EXCEl file (file name= file1) that includes the below data, and I need to upload data into the Test table on SQL Server. The test table structure is here.

    Excel file(File1):

    Columns:

    product_no,company,description,category,class,case,sales_date1(2019P09W2(02-DEC)),sales_date2(2019P09W3(09-DEC)),sales_date3(2019P09W4(16-DEC)),sales_date4(2019P09W5(23_DEC))

    Rows:

    1, Corporation1,GEN1,RS,A,C1,120,11.8,2.34,140

    --

    I need to upload above file into Test table.

    create Test table (

    id int identity(1,1)not null,

    product_no nvarchar(10),

    company nvarchar(10),

    category nvarchar(10),

    class nvarchar(10),

    case nvarchar(10),

    sales_date date,

    Sales float)

    insert into Test (product_no,company,category,class,case,sales_date,sales) values

    (1, Corporation1,GEN1,RS,A,C1,2019P09W2(02-DEC),120)

    ,(1, Corporation1,GEN1,RS,A,C1,2019P09W3(09-DEC),11.8)

    ,(1, Corporation1,GEN1,RS,A,C1,2019P09W4(16-DEC),2.34)

    ,(1, Corporation1,GEN1,RS,A,C1,2019P09W5(23_DEC),140)





    4.42 1.21



    zj


    • Edited by ZaraJ Friday, December 13, 2019 4:35 PM
    Friday, December 13, 2019 4:34 PM

All replies

  • We can achieve it using SQL Server Import and Export Wizard or SQL Server Integration Services(SSIS) and few other methods.
    Please go through below articles it may helps you.

    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15

    https://www.sqlshack.com/import-data-excel-file-sql-server-database/


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Friday, December 13, 2019 4:51 PM
  • Hi Suman Zakka,


    Thank you for your mention.

    I knew about how to upload data to SQL Server by using Import Task from SSMS or SSIS, but my question is about a dirty flat file, which means I need to clean flat-file at first. I'm looking for a solution on how to clean it automatically by any tools not manually






    • Edited by ZaraJ Friday, December 13, 2019 6:51 PM
    Friday, December 13, 2019 5:17 PM

  • I knew about how to upload data to SQL Server by using Import Task from SSMS or SSIS, but my question is about a dirty flat file, which means I need to clean flat-file at first. I'm looking for a solution on how to clean it automatically by any tools not manually

    Try using SSIS package - script task having .NET code to validate the metadata in excel. 

    Please refer: http://www.techbrothersit.com/2014/02/ssis-how-to-validate-excel-header.html


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Saturday, December 14, 2019 10:34 AM
  • Hi ZaraJ,

    Please check following script.

    ERROR HANDLING IN SSIS WITH AN EXAMPLE STEP BY STEP

    Derived Column Transformation

    Also , Sorry that I am not good at SSIS . If you would like to know more about this , please post your issue in SSIS Forum

    By the way, If you have post your issue in the corresponding forum ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 7:51 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 26, 2019 2:59 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 1, 2020 6:50 AM