locked
How to load data from csv file to dynamic table RRS feed

  • Question

  • Hi, i have a reqiurement to load bulk of csv files to sql table. some times, some columns could not come in csv file(some times 100 columns and some times 80 cloumns).That time the package is getting failed. can any one suggest how to create a table dynamically based on csv file structure.
    Thursday, June 11, 2015 10:20 AM

Answers

  • Hello - While there are many options such as BULK INSERT or SSIS Packages but all of these requires a fixed table structure before you could push data into them.

    One method that you can try is like below (I am using ACE Drivers which you will have to download separately :

    -- Here file1.csv is having two columns EmpID, EmpName

    SELECT * INTO tblX FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;Database=D:\;', 'SELECT * from [file1.csv] ')

    -- Here file2.csv is having three columns EmpID, EmpName, DeptID SELECT * INTO tblY FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;Database=D:\;', 'SELECT * from [file2.csv] ')



    Alternatively

    • Write a simple VB Script based program to read the header row
    • Split the row value in an array
    • Loop through array, filter each column
    • Create the table in the database based on these columns and then finally
    • Insert record in to that table using BULK INSERT Method

    Hope this helps




    Good Luck! 

    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue


    • Edited by Manu Kapoor Thursday, June 11, 2015 12:37 PM Corrections
    • Marked as answer by Katherine Xiong Tuesday, June 16, 2015 8:50 AM
    Thursday, June 11, 2015 12:22 PM

All replies

  • You need to use method like OPENROWSET for that

    see

    http://blog.hubacek.uk/use-your-text-csv-files-in-your-queries-via-openrowset/

    so in your case it will be

    select * INTO TableName
    from openrowset('MSDASQL'
    ,'Driver={Microsoft Text Driver (*.txt; *.csv)}'
    ,'select * from <yourcsv file path>')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, June 11, 2015 11:00 AM
    Thursday, June 11, 2015 11:00 AM
  • Thank You Visakh... do we have any other way. I am getting this error.

    "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."

    Thursday, June 11, 2015 11:25 AM
  • Try below code

    BULK INSERT TableName
       FROM 'YourDrive\test.csv';

    Note: You must have required permission to read the file

    or as per Visakh answer, use Microsoft.Jet.OLEDB.12.0 instead.

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    Thursday, June 11, 2015 11:52 AM
  • Hello - While there are many options such as BULK INSERT or SSIS Packages but all of these requires a fixed table structure before you could push data into them.

    One method that you can try is like below (I am using ACE Drivers which you will have to download separately :

    -- Here file1.csv is having two columns EmpID, EmpName

    SELECT * INTO tblX FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;Database=D:\;', 'SELECT * from [file1.csv] ')

    -- Here file2.csv is having three columns EmpID, EmpName, DeptID SELECT * INTO tblY FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;Database=D:\;', 'SELECT * from [file2.csv] ')



    Alternatively

    • Write a simple VB Script based program to read the header row
    • Split the row value in an array
    • Loop through array, filter each column
    • Create the table in the database based on these columns and then finally
    • Insert record in to that table using BULK INSERT Method

    Hope this helps




    Good Luck! 

    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue


    • Edited by Manu Kapoor Thursday, June 11, 2015 12:37 PM Corrections
    • Marked as answer by Katherine Xiong Tuesday, June 16, 2015 8:50 AM
    Thursday, June 11, 2015 12:22 PM
  • Thank You Visakh... do we have any other way. I am getting this error.

    "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."

    Thats because Disallow Adhoc Access checkbox is enabled in provider properties. Deselect it and you will be able to use it

    see

    https://support.microsoft.com/en-us/kb/327489?wa=wsignin1.0

    Another way is to use Script task inside SSIS to generate data flow task programatically based on your file metadata

    see

    http://www.selectsifiso.net/?p=288


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, June 11, 2015 12:43 PM
    Thursday, June 11, 2015 12:42 PM
  • Thank You Manu...!
    Thursday, June 11, 2015 12:49 PM
  • Check this out. 

    Load Text/ CSV files To table when Number of Column Can Change in Source File/s

    Good luck!


    http://sqlage.blogspot.com/

    Thursday, June 11, 2015 3:36 PM
  • The standard Data Flow Task supports only static metadata defined during design time. I would recommend you check the commercial COZYROC Data Flow Task Plus. It is an extension of the standard Data Flow Task and supports dynamic metadata at runtime. You can process all your tables using single Data Flow Task Plus. No programming skills are required.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, June 12, 2015 9:09 PM