February 21, 2008 at 10:25 pm
Here is the problem that i am facing
create table file1(Loan_Number varchar(10))
insert into file1 values('0000012345')
insert into file1 values('0000045678')
insert into file1 values('0000009876')
Now here is the query
Exec Master..xp_cmdshell 'bcp "SELECT loan_number from test.dbo.file1" queryout C:\test.xls -c -T -RRaw'
I want to see the 0000012345 in test.xls sheet but instead of this it shows as 12345
Now you can not see 00000 values in Excel sheet,
Is there anyone knows about this problem?
Please reply
February 22, 2008 at 6:13 am
If you try to manually type that value into an open Excel spreadsheet, you'll see the same behavior. Excel interprets it as a number, so it truncates the leading zeroes. In Excel, there are a few ways to fix this: make the type of the column text, or you could prepend the value with a single quote mark. Not sure how this will translate to your bulk copy, but hopefully will put you on the right direction.
February 22, 2008 at 6:47 am
Besides, maybe it would be better to use linked server to excel?
Piotr
...and your only reply is slàinte mhath
February 22, 2008 at 9:46 am
ya that is right if we can put Text format, that is possible but if suppose every time i want to see New Excel file then how can i change the Text format?
February 22, 2008 at 11:22 am
Part of the problem is that Excel insists on deleting leading zeros for you.
I found a workaround here:
The solution is to create a file with a .csv extension, and format each number like so: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm
="00001"
="00002"
="00003"
="00004"
I typed a csv file manually and opened with excel to confirm that it does indeed work. So I think this would make your SQL statement look like
Select '="' + loan_number + '"' From test.dbo.file1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply