none
Import Tab Delimited file

    Question

  • This is for SQL Server 2005

    I have a tab delimited file that once a day will be imported to a table.  It will also need to be imported whenever someone pushes a button on the program thats viewing this data.

    the DTS package or procedure also needs to set a last modified field on the table so that the user knows when the last time the data was imported.

    Is a stored procedure the best way to import the tab file and have the ability to be called from a sql command?  Does anyone have a link or any information that can get me started?
    Thursday, July 09, 2009 1:45 PM

Answers

  • if you are looking for a T-SQL method, you can use BULK INSERT with format file or BCP (via xp_cmdshell)
    KH Tan
    • Marked as answer by GreenWaterBoy Thursday, July 09, 2009 2:33 PM
    Thursday, July 09, 2009 2:03 PM

All replies

  • if you are looking for a T-SQL method, you can use BULK INSERT with format file or BCP (via xp_cmdshell)
    KH Tan
    • Marked as answer by GreenWaterBoy Thursday, July 09, 2009 2:33 PM
    Thursday, July 09, 2009 2:03 PM
  • Hi,

    If you want to insert through Bulk Insert then check the below link for code

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/


    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, July 09, 2009 2:08 PM
  • Here is example with BULK INSERT (you can call this script from a client application):

    Given tab delimited text file with values (data.txt):

    abc	10	z
    def	9	k

    You can create a staging table for the imported data. Here the Data table is your production table, and Staging is only for importing.

    CREATE TABLE Data (
     keycol INT IDENTITY PRIMARY KEY,
     col1 VARCHAR(10),
     col2 INT,
     col3 VARCHAR(10),
     datecol DATETIME DEFAULT CURRENT_TIMESTAMP);
     
    CREATE TABLE Staging (
     col1 VARCHAR(10),
     col2 INT,
     col3 VARCHAR(10));

    Then you can run the following script to import data:

    TRUNCATE TABLE Staging;
     
    BULK INSERT Staging
    FROM 'c:\data.txt'
    WITH (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n');
     
    INSERT INTO Data (col1, col2, col3)
    SELECT col1, col2, col3 
    FROM Staging;
     
    SELECT keycol, col1, col2, col3, datecol
    FROM Data;
     
    /*
    
    keycol  col1  col2  col3  datecol
    ------- ----- ----- ----- -----------------------
    1       abc   10    z     2009-07-09 10:24:08.213
    2       def   9     k     2009-07-09 10:24:08.213
    
    */



    Plamen Ratchev
    Thursday, July 09, 2009 2:29 PM
  • Thanks  works 100x faster than individually inserting each row with VB.

    Thursday, July 09, 2009 2:33 PM