Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Created csv, but how can I specify data as INT instead of VARCHAR

    Hi,

    I have a piece of code that is stored in a SQL JOB and creates a .csv file. It works by calling back data from another db, creating a temp databse, exporting the data from that db and then deleting the db. The code to pull back the info is stored within a view. The problem I have is the id column contains whole numbers, when the number is greater than 6 digits, it's returned in the following format within the csv file 1.01477e+006

    It's only the job that creates the .csv file that I have a problem with.

    I've tried specifying the colum as INT, but I then get an error message.

    Can anyone point me in the right direction?

    Here is the error message

    Conversion failed when converting the varchar value 'id' to data type int. [SQLSTATE 22018] (Error 245). The step failed.
    Thanks

    Zig

    Code:
    --create a global temp table to put the data in
    create table ##bcpVCBD (theorder char(1),
    		id varchar(200),
    		Parent_Man varchar(200),
            Client varchar(200),
            Sort_Code varchar(200),
            Bank_Account varchar(200))
    
    
    
    
    --insert the row names
    insert into ##bcpVCBD  (theorder,
    		id,
    		Parent_Man,
            Client,
            Sort_Code,
            Bank_Account)
    
    values                              ('1',
                                        'id',
    		'Parent_Man',
            'Client',
            'Sort_Code',
            'Bank_Account')
    
    
    --insert the actual data from the view
    insert into ##bcpVCBD  (theorder,
                            id,
    		Parent_Man,
            Client,
            Sort_Code,
            Bank_Account)
    select '2',
             id,
    		Parent_Man,
            Client,
            Sort_Code,
            Bank_Account 
    from EQREPDB.dbo.vCBD
    
    declare @cmd nvarchar(4000)
    
    
    set @cmd = 'bcp "select   id, Parent_Man, Client, Sort_Code, Bank_Account '
                      + ' from ##bcpVCBD order by theorder" queryout "g:\ReportingExtracts\Reconciliations\CBD\CBD' 
                      + convert(varchar(20),getdate(),112) 
                      + '.CSV" -m1 -c -t"," -r"\n" -S"REASLKJDSQZZ123" -T' 
    
    exec master.dbo.xp_cmdshell @cmd,No_output
    
    drop table ##bcpVCBD
    Last edited by Ziggadebo; 10-25-10 at 10:33. Reason: added failure reason

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Prepare to kick yourself....

    Are you opening this csv file in Excel? Have you expanded the column?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •