asked on

Bulk Insert Row Terminator

Hi,

i got a flat file which i would like to Bulk insert into a table

Flatfile Format

1,2,3,4,5/n
1,2,3,4/n
1/n

Open in new window

So the problem that i have is, that Bulk insert is only looking for the row terminator in the last column.

So the table would look like this:
col1 col2 col3 col4 col5
1 2 3 4 5
1 2 3 4 1

Instead of
col1 col2 col3 col4 col5
1 2 3 4 5
1 2 3 4
1

Is there a way to give the row terminator a higher priority or something?
I guess i would have the same issue with xml format files.

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

T-Virus

ASKER

Hmmm not the answer i was hoping for but hey we have to life with it.

I do know tools that where written in java that would work trough the exact same flatfile in less then 5sec. (2mil rows)

So in your opionion which way is the fastest?

Some Script that would do the job before Bulk inserting the data or
this Function? Even if the Function would take 30min to transform the date into a new table not faltfile funnily, there it would take just 5sec. But is it really the way to go with import export import?

CREATE FUNCTION dbo.fnSplit    (
          @Expression NVARCHAR(max)
        , @Delimiter  NVARCHAR(max)
        , @INDEX      INT
    )
    RETURNS NVARCHAR(max)
    AS
    BEGIN
        DECLARE @RETURN  NVARCHAR(max)
        DECLARE @Pos     INT
        DECLARE @PrevPos INT
        DECLARE @I       INT
       
        -- SELECT dbo.fnSplit('4.55.108.2','.', 2)
       
        IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @INDEX < 1
            SET @RETURN = NULL
        ELSE IF @INDEX = 1 BEGIN
            SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)
            IF @Pos > 0 SET @RETURN = LEFT(@Expression, @Pos - 1)
        END ELSE BEGIN
            SET @Pos = 0
            SET @I = 0
           
            WHILE (@Pos > 0 AND @I < @INDEX) OR @I = 0 BEGIN
                SET @PrevPos = @Pos
                SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))
               
                SET @I = @I + 1
            END
           
            IF @Pos = 0 AND @I = @INDEX
                SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))
            ELSE IF @Pos = 0 AND @I <> @INDEX
                SET @RETURN = NULL
            ELSE
                SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))
        END
       
        RETURN @RETURN
    END

Open in new window