June 5, 2014 at 12:21 pm
We are on 2008R2.
I have a query I've been running once a week, copying the results to Excel for finance. The one thing that doesn't work well is the product code, which is nvarchar, but Excel insists on dropping leading zeroes and treating it as a number. For example Product 010 in SQL becomes 10 when copied into Excel.
I want to solve this before I take the next step and set up an SSIS to run this without my intervention. To save the users some heartburn, I hope there is a way to solve it from the SQL side. Any help will be appreciated-Thanks!
June 5, 2014 at 1:38 pm
Add a single quote to the value. This will left justify the value in the cell
'010
June 5, 2014 at 3:00 pm
Hi, Thank you for your interest.
I should have explained that I've all ready tried that. In the SQL query I created a field called ProdText by concatenating a single quote to the product code. For some reason the single quote is visible in Excel, unlike a number I type in with a single quote. I haven't asked the users to test their lookups using the data when it goes into the spreadsheet. I just assumed, possibly incorrectly, that if it didn't look right, it wouldn't work right either. I'm sure the users would view it with suspicion.
ProdText looks like this when pasted into Excel:
'010 (left aligned)
When I type a quote, zero, one, zero in Excel:
010 (left aligned)
Product Code pastes into Excel like this:
10 (Right aligned)
June 5, 2014 at 3:03 pm
This is a manual process to copy and paste into excel? The problem is that excel tries to determine what the datatype is for a column based on value in the first row. You can force it to text by simply formatting the column before you paste your data.
_______________________________________________________________
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/
June 5, 2014 at 3:09 pm
That's interesting, the column before that is the column header from the query and contains the field name. Tomorrow morning I will copy the data without the headers and follow your advice. Thank you and test first thing tomorrow morning.
June 9, 2014 at 9:36 am
Copying the data in without the headers seems to be the answer, since there was a text field immediately above the pasted data. The odd thing is that I can still perform math on those "numbers". The finance department was able to use them in their work as text. Thank you!
June 9, 2014 at 9:44 am
SQLKnitter (6/9/2014)
Copying the data in without the headers seems to be the answer, since there was a text field immediately above the pasted data. The odd thing is that I can still perform math on those "numbers". The finance department was able to use them in their work as text. Thank you!
No problem. Glad that worked for you. 😀
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply