Do Not Call list

  • Hi,

    So, even though my company has nothing to do with telemarketing, we've been asked to bump phone numbers for one project up against the national DNC list. It's a whopper. I'm running into some issues, and I was hoping someone here has either worked with it, or maybe has some pointers in working with a file this large (2.5gb text file :w00t:)

    I guess first off, here's how I'm trying to load it, though the create table syntax isn't set in stone:

    CREATE TABLE blah.dbo.nationaldnc

    (area nvarchar (3) NOT NULL,

    phonenumber varchar(8000) NOT NULL)

    BULK INSERT blah.dbo.nationaldnc FROM '\\myserver\Sample\2013-4-3_Global_18B81869-3F4C-4C1F-BF46-FF33984D9900.txt'

    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '', BATCHSIZE = 100000)

    This is how the file is set up, per donotcall.gov

    The Full List in a Flat Text File has one three-digit area code, a comma, and a seven-digit telephone number per line, with a linefeed at the end of each line:

    123,4567890

    123,4567890

    123,4567890

    My problems are:

    When I use anything other than nvarchar(max) for the second column, I get truncation errors. That makes me think that I'm using the wrong row terminator. However I'm not sure how to use char(10) for it, since just writing in char(10) doesn't seem to work.

    The other will likely be when bumping the phone list up against it, how to write the least harsh query. I have stored procedures that do it with our internal do not contact list, but it's so much smaller than this that I'm not sure they'll scale. I'll post those second, as it's somewhat long.

    Any ideas or suggestions would be welcomed.

    Thanks

  • USE [blah]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[DeDupe]

    @projectID int,

    @dupVar1 varchar(20) ='',

    @dupVar2 varchar(20) ='',

    @dupVar3 varchar(20) =''

    as

    DECLARE @ProcessTable varchar(64)

    select @ProcessTable=ds_ProcessTable from blah.dbo.ds_dataset where ds_ProjectID=@ProjectID

    declare @SQLString varchar(1000)

    set @sqlString='with d1 as (

    select id, [var1]

    from

    ),

    d2 as (

    select id, [var1]

    from

    )

    update

    set statusflag = 10 where id in (

    select d1.id

    from d1, d2

    where d1.id > d2.id

    and d1.[var1] = d2.[var1]

    ) '

    if @dupvar2 <> ''

    begin

    SET @SQLString='with d1 as (

    select id, [var1], [var2]

    from

    ),

    d2 as (

    select id, [var1], [var2]

    from

    )

    update

    set statusflag = 10 where id in (

    select d1.id

    from d1, d2

    where d1.id > d2.id

    and d1.[var1] = d2.[var1]

    and d1.[var2] = d2.[var2]

    ) '

    SET @SQLString=REPLACE(@SQLString,'[var2]',@dupVar2)

    end

    if @dupvar3 <> ''

    begin

    SET @SQLString='with d1 as (

    select id, [var1], [var2], [var3]

    from

    ),

    d2 as (

    select id, [var1], [var2], [var3]

    from

    )

    update

    set statusflag = 10 where id in (

    select d1.id

    from d1, d2

    where d1.id > d2.id

    and d1.[var1] = d2.[var1]

    and d1.[var2] = d2.[var2]

    and d1.[var3] = d2.[var3]

    ) '

    SET @SQLString=REPLACE(@SQLString,'[var2]',@dupVar2)

    SET @SQLString=REPLACE(@SQLString,'[var3]',@dupVar3)

    end

    SET @SQLString=REPLACE(@SQLString,'

    ',@ProcessTable)

    SET @SQLString=REPLACE(@SQLString,'[var1]',@dupVar1)

    exec (@SQLString)

  • erikd (4/3/2013)


    Hi,

    So, even though my company has nothing to do with telemarketing, we've been asked to bump phone numbers for one project up against the national DNC list. It's a whopper. I'm running into some issues, and I was hoping someone here has either worked with it, or maybe has some pointers in working with a file this large (2.5gb text file :w00t:)

    I guess first off, here's how I'm trying to load it, though the create table syntax isn't set in stone:

    CREATE TABLE blah.dbo.nationaldnc

    (area nvarchar (3) NOT NULL,

    phonenumber varchar(8000) NOT NULL)

    BULK INSERT blah.dbo.nationaldnc FROM '\\myserver\Sample\2013-4-3_Global_18B81869-3F4C-4C1F-BF46-FF33984D9900.txt'

    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '', BATCHSIZE = 100000)

    This is how the file is set up, per donotcall.gov

    The Full List in a Flat Text File has one three-digit area code, a comma, and a seven-digit telephone number per line, with a linefeed at the end of each line:

    123,4567890

    123,4567890

    123,4567890

    My problems are:

    When I use anything other than nvarchar(max) for the second column, I get truncation errors. That makes me think that I'm using the wrong row terminator. However I'm not sure how to use char(10) for it, since just writing in char(10) doesn't seem to work.

    The other will likely be when bumping the phone list up against it, how to write the least harsh query. I have stored procedures that do it with our internal do not contact list, but it's so much smaller than this that I'm not sure they'll scale. I'll post those second, as it's somewhat long.

    Any ideas or suggestions would be welcomed.

    Thanks

    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

  • Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

  • erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

    So, does this mean you are using the \ n (no space between the \ and the n)?

  • Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

    So, does this mean you are using the \ n (no space between the \ and the n)?

    Yes ma'am. No spaces at all.

  • erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

    So, does this mean you are using the \ n (no space between the \ and the n)?

    Yes ma'am. No spaces at all.

    First, sir. Second that's my dad.

    Have you tried \ r \ n (again with no spaces between the \ and the r and n).

  • erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

    So, does this mean you are using the \ n (no space between the \ and the n)?

    Yes ma'am. No spaces at all.

    You might modify your response slightly. Lynn is not a ma'am. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.

    There is no space.

    So, does this mean you are using the \ n (no space between the \ and the n)?

    Yes ma'am. No spaces at all.

    First, sir. Second that's my dad.

    Have you tried \ r \ n (again with no spaces between the \ and the r and n).

    Hm. I didn't know it could do that. :blush:

    Now that my face has been properly de-egged:

    Yes, with this error message:

    Msg 4866, Level 16, State 1, Line 1

    The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly.

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    This is with the phonenumber field set to varchar(7), as it should be in the file.

  • Can you upload a 10 line snippet of the file as a .txt file, please?

  • And what happens if you make the phone number field varchar(8)?

  • Lynn Pettis (4/3/2013)


    And what happens if you make the phone number field varchar(8)?

    Heh. Curiously, I can use Python to split the file up (opening the whole file with any of the programs I have available is impossible; the computer comes to a screeching halt), and the split files seem to agree with the \ n row terminator. Maybe I'll try using it to re-save the whole thing to one file?

    When I use any length short of nvarchar(max), I get the truncation error I posted before, up to and including varchar(8000).

  • erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    And what happens if you make the phone number field varchar(8)?

    Heh. Curiously, I can use Python to split the file up (opening the whole file with any of the programs I have available is impossible; the computer comes to a screeching halt), and the split files seem to agree with the \ n row terminator. Maybe I'll try using it to re-save the whole thing to one file?

    When I use any length short of nvarchar(max), I get the truncation error I posted before, up to and including varchar(8000).

    Worth a shot.

  • Lynn Pettis (4/3/2013)


    erikd (4/3/2013)


    Lynn Pettis (4/3/2013)


    And what happens if you make the phone number field varchar(8)?

    Heh. Curiously, I can use Python to split the file up (opening the whole file with any of the programs I have available is impossible; the computer comes to a screeching halt), and the split files seem to agree with the \ n row terminator. Maybe I'll try using it to re-save the whole thing to one file?

    When I use any length short of nvarchar(max), I get the truncation error I posted before, up to and including varchar(8000).

    Worth a shot.

    Yep, working. Thanks for your gender neutral time.

    Some days SQL makes me want to go put together Ikea furniture to relax. Sheesh.

Viewing 14 posts - 1 through 13 (of 13 total)

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