locked
Automate Import of CSV files RRS feed

  • Question

  • Good afternoon,

    I've been given a project to move a process from it's current Access based processing to SQL based. There will be a lot of questions around this as it's my first using Stored Procedures, Updates, Appends etc in SQL. I have no time limit as this is a development project only to enable me to learn more about SQL other than report writing. 

    However the first step is to transfer 1 or more csv files to an Import Table. There are issues here as the csv files are downloaded from an external source and by financial (fiscal quarters). The existing Access process says you have to manually open one csv and then add the contents from the other csv's (other quarters) to it and re-save as another filename ready to be imported.

    This appears to be a pointless step as within the data there is a dischage date which relates to the fiscal quarter so this can be done in another stage.

    So my first question to make this easier, is is it possible to transfer the contents of 1 or more csv's within a specified folder into a new SQL table in one action rather than multiple actions. All data types is the same in each csv.

    There are a lot more step to this moving forward but I'd like to take one step at a time a understand it before moving on.

    Tuesday, January 26, 2016 1:03 PM

Answers

All replies

  • You can do this very easily in SSIS assuming that the files all have the same structure.

    http://www.techbrothersit.com/2013/12/ssis-load-multiple-files-to-table-with.html


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Eric__Zhang Wednesday, January 27, 2016 6:46 AM
    • Marked as answer by ProfessorFudger Wednesday, January 27, 2016 10:42 AM
    Tuesday, January 26, 2016 1:18 PM
  • For this you can create a SSIS package

    http://blog.sqlauthority.com/2011/05/12/sql-server-import-csv-file-into-database-table-using-ssis/

    Also you can add foreach loop container to loop via .CSV files

    https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Eric__Zhang Wednesday, January 27, 2016 6:47 AM
    • Marked as answer by ProfessorFudger Wednesday, January 27, 2016 10:42 AM
    Tuesday, January 26, 2016 1:19 PM
    Answerer
  • You need to import all your CSV files into staging tables and then combine the data to form final tables. 

    This will make things easier for you.

    Thanks for this would this code work once I have the Tables set up:

    CREATE TEMPORARY TABLE your_temp_table LIKE your_table;
    LOAD DATA INFILE '/tmp/your_file.csv'
    INTO TABLE your_temp_table
    FIELDS TERMINATED BY ','
    (id, product, sku, department, quantity); 
    UPDATE your_table
    INNER JOIN your_temp_table on your_temp_table.id = your_table.id
    SET your_table.quantity = your_temp_table.quantity;
    DROP TEMPORARY TABLE your_temp_table;
    The reason I ask Ive got 8 csv's to import (2 csv's per quarter), but esentially they are same and I only need to bring in the data from one field only in 4 of them.
    Wednesday, January 27, 2016 11:08 AM
  • Sure, you can do this.  Load everything into a staging table and then write the results of that to a production table. 

    http://www.sqlservercentral.com/Forums/Topic1437794-391-1.aspx

    After you clean the data in the staging table, write the data from the staging table to the production table.

    http://stackoverflow.com/questions/6061075/how-can-i-load-a-large-flat-file-into-a-database-table-using-ssis


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 21, 2016 2:32 PM