Post reply

Import .txt file into SQL Table - Not working!

  • March 25, 2013 at 8:37 am

    #394465

    Hi there, I would really appreciate it if someone could help me. At the moment part of our customer information is held in Lotus Approach and part in SQL.

    I have exported the Approach information to Excel and am trying to import this into SQL. I have also saved the Excel sheet as a .txt file.

    My issue is that three of the Approach fields are free text boxes, with lots of carriage returns. I have tried the following query to enter the information into SQL:

    BULK INSERT livedev.dbo.CarriageReturns

    FROM 'C:\Users\janitor\Desktop\TestData\Test.txt'

    WITH ( FIELDTERMINATOR = '\t',

    ROWTERMINATOR = '\r',

    FIRSTROW=2 )

    This works fine on the simple fields but the large text fields are carrying over to the next row and creating lots of errors.

    I have read lots of forums but just can't seem to find anything that works. I should have 437 records imported into SQL, but looking at a Hex editor - my data is showing as having 1124 lines because of the carriage returns.

    It must be something to do with the ROWTERMINATOR and it doesn't know where the row ends - but I can't find a solution for this!

    Thanks

    Caroline

  • March 25, 2013 at 10:34 am

    #1600410

    I'm no expert but I use this code and it works, if you're just dumping into one field to get it into the db

    USE Databasename

    DECLARE

    @FilePath VARCHAR(1000) = 'c:\Transfer\' ,

    @ArchivePathVARCHAR(1000) = 'c:\Transfer\Archive\' ,

    @FileNameMaskVARCHAR(1000) = '*.txt'

    DECLARE @ImportDate DATETIME

    SELECT @ImportDate = GETDATE()

    DECLARE @FileName VARCHAR(1000) ,

    @File VARCHAR(1000)

    DECLARE @cmd VARCHAR(2000)

    CREATE TABLE ##Import (s VARCHAR(MAX))

    CREATE TABLE #Dir (s VARCHAR(8000))

    -- IMPORT FILE

    SELECT@cmd = 'dir /B ' + @FilePath + @FileNameMask

    DELETE #Dir

    INSERT #Dir EXEC MASTER..xp_cmdshell @cmd

    DELETE #Dir WHERE s IS NULL OR s LIKE '%not found%'

    WHILE exists (SELECT * FROM #Dir)

    BEGIN

    SELECT @FileName = MIN(s) FROM #Dir

    SELECT@File = @FilePath + @FileName

    SELECT @file

    SELECT @cmd = 'bulk insert'

    SELECT @cmd = @cmd + ' ##Import'

    SELECT @cmd = @cmd + ' from'

    SELECT @cmd = @cmd +' ''' + REPLACE(@File,'"','') + ''''

    TRUNCATE TABLE ##Import

    -- IMPORT DATA

    EXEC (@cmd)

    -- REMOVE FILENAME JUST IMPORTED

    DELETE#Dir WHERE s = @FileName

    INSERT INTO tablename(fieldname)

    SELECT s FROM ##Import

    -- ARCHIVE FILE

    SELECT @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName

    EXEC MASTER..xp_cmdshell @cmd

    END

    DROP TABLE ##Import

    DROP TABLE #Dir

    GO

    If you just create the Transfer & Archive folders named and put your text files into the Transfer folder it should work.

  • March 25, 2013 at 10:36 am

    #1600413

    Thanks very much, will give this a go 🙂

  • March 25, 2013 at 10:38 am

    #1600416

    You're welcome. Let me know how you get on.

  • March 25, 2013 at 10:00 pm

    #1600598

    It is worth noting that the solution provided requires you enable xp_cmdshell, which is disabled by Microsoft by default when you install SQL Server and for good reason. Enabling it opens up a can of worms when it comes to maintaining a high-level of security and auditability in your database instance. Do some research into the feature before enabling it and as always make sure you understand code before running it in your environment.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply