cancel
Showing results for 
Search instead for 
Did you mean: 

Export to Excel - data with leading zeros

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

I knew you guys could do it...

That's two useful workarounds for the leading zeros problem.

Regards,

Douglas

Answers (1)

Answers (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

former_member583013
Active Contributor
0 Kudos

Douglas,

The single quote is the only known way of making the Numeric value display as text preserving the preceeding Zeros.

This is actually an issue with Excel and not B1

Suda

Former Member
0 Kudos

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.

former_member583013
Active Contributor
0 Kudos

< 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

Former Member
0 Kudos

That's quite a neat solution. A character that does not show in the Query screen or Excel. It's probably the easiest way if you want a printed report that shows, say, Item codes without dropping leading zeros.

Regards,

Douglas

former_member583013
Active Contributor
0 Kudos

Douglas,

I am glad it was helpful and thanks for your generous 2 points.

Good luck

Suda