10-25-10, 09:05 #1Registered User
- Join Date
- Oct 2010
Unanswered: Created csv, but how can I specify data as INT instead of VARCHAR
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.
--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 09:33. Reason: added failure reason
10-25-10, 10:17 #2King of Understatement
- Join Date
- Feb 2004
- One Flump in One Place
Prepare to kick yourself....
Are you opening this csv file in Excel? Have you expanded the column?Testimonial:
ur codings are working excelent.