on 02-20-2008 11:44 AM
Does anyone have any tips or tricks for running a query then exporting to Excel without dropping leading zeros from the data?
Examples: numeric Item codes with leading zeros, telephone numbers that have been entered with no spaces.
Regards,
Douglas McDove
Hi Douglas,
After exporting the query results to excel, do the following:
1) Open Excel
2) Click on open file
3) Select the .txt file created by SAP
4) In the Step 3 of text import wizard, for column data format, select "Text" format for the column for item code {the one that has leading zeroes}
5) You will see the values as they appear in SAP. Then save this file in regular .xls format.
I know this is a cumbersome thing but due to limitations of excel you will have to follow this procedure.
Regards,
Gyanesh Rupani
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
if you export it from query, give before each column in select prefix '
for example
select '''' + convert(varchar, cardcode) as cardcode from ocrd
hope it helps
Petr
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried to prefix the export, but the prefixing character then becomes part of the data in the spreadsheet. For example, if you export
'0003
(There is a single quote at the front of that.)
Excel treats the first character as a single quote. If you remove the single quote, then Excel treats the remainder as a number and removes the leading zeros (even if you set the column to a Text format before you remove the quote).
I suppose a quote at the start of a number still leaves the list readable, but I wondered if there is a better way. I certainly have not found one yet.
Regards,
Douglas McDove
It's OK - I understand that the issue is with Excel. I just had problems with it again and I wondered if anyone on this forum had any clever techniques that at least gave some kind of a workaround.
I think that the only advice that I have found from SAP themselves is to run queries in Query Analyzer and copy to Excel - not really a solution for the average user.
The only way that I have found is to export the data with some extra text at the front then manipulate it in Excel. Pretty much what was suggested above. It looks like no one has anything that is much better.
As I pointed out above, exporting with a quote in front does not give the same effect as manually entering data with a quote in front.
It looks like there just is not a nice way of doing this, but I accept that it is Excel not B1 that is the problem.
< Font Color="RED" Size = 3 Face=Verdana>
I liked the challenge and therefore.......I got this for you
</Font>
SELECT CHAR(28) + T0.CardCode [BP Code\] FROM [dbo\].[OCRD\] T0
SELECT CHAR(29) + T0.CardCode [BP Code\] FROM [dbo\].[OCRD\] T0
SELECT CHAR(30) + T0.CardCode [BP Code\] FROM [dbo\].[OCRD\] T0
SELECT CHAR(31) + T0.CardCode [BP Code\] FROM [dbo\].[OCRD\] T0
Any of the above 4 should work. T0.CardCode is the database field I tested, you can substitute this with any database field and it should work.
Regards
Suda
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.