Problem Exporting Spreadsheet from SAP to Excel

Topic

New Reply

Hello,

I searched the posts for similar problems and I cannot find anything that answers my problem.

Whenever I export from SAP to spreadsheet, the data I have sometimes has information written as 00E27, for example. The default format for my 2007 Excel seems to be a Scientific for the Number format in any given cell. How do I change this default to Text? Changing the cells to text after the import does not recreate the original values.

Thanks for taking the time to read and possibly answer my post.

Tom

Reply | Quote

Replies

The default number format in Excel is to show large numbers in scientific notation. To show it as text you can format the cell as text or add an apostrophe to the front.
If you can export as CSV you can format the cells when you import into Excel.

cheers, Paul

Reply | Quote

Paul,

Thanks for your answer. I am not able to change the data prior to export, which means no apostrophe can be added. Also, trying to reformat the cell after import does not yield or recover the original values. The cells only do this when there is an “E” in the data. For example, 00D40 exported from SAP and imported into Excel stays 00D40; however, 00E11 becomes 0.00E+00, and trying to change the cell to Text only yields a “0”.

Reply | Quote

If you open the file in a text reader like Notepad is the text correct? if it is not the problem is SAP export not Excel. If the text is correct, you may can try to change the extension and use the import text wizard to import the number as text.

What do the numbers that they are so large that Excel needs scientific notation to display them?

Steve

Reply | Quote

I exported to rich text and the text correct. Saved to HTML…it is correct. I tried use the import text wizard to import the number as text. On the preview in the wizard it looks right but it still mucks it up when the spreadsheet is populated.

The numbers are storage bins in the warehouse…I cannot get around the E/aisle nomenclature, which looks like a natural log to excel.

Tom

Reply | Quote

Can you post a sample export – just a couple of lines – and we can try some things for you.

cheers, Paul

Reply | Quote

If it won’t import properly as text, another option is to convert the number after the fact to text with a formula. It would be helpful to see some examples of “failures”: what the text is, what it is converted to, and what you want.

Steve

Reply | Quote

I cannot use a formula after import because the essence of the information is not carried within the error. Look at the Bin data…it is a physical location of material, something that cannot be calculated.

Reply | Quote

It looks to me like SAP is converting the bin numbers to a number in the export. Can you export as CSV so we can confirm this?

cheers, Paul

Reply | Quote

34103-screen-shotThese are my export choices:

Reply | Quote

What are the values in SAP before the conversion (ie what should they be) and what are they in the CSV file if opened in text editor rather than Excel? As far as I know, we don’t know if the problem is the SAP export or the Excel Import…

Steve
Did all the “bad values” in Excel start as the SAME SAP value (something like 00E00)?

Reply | Quote

34104-sap-screenshot I have the sap data to be exported attached and a sample of what it looks like when exported to richtext will not upload, the forum program thinks it is an invalid file. Below is a cut and paste of two columns from the text file, just to show a sampling that the 00E12 is read as it should==>this is what is seen as scientific notation in Excel.

Storage Type Bin
S01 00E12
S01 00E12
S01 00RESORT
S01 00RESORT
S01 00E12
S01 00E12
S01 00J12
S01 00J12
S01 00J12

Reply | Quote

Have you tried importing as something other than CSV? Excel is importing the column as “General” and this converts correctly the things it sees as text, but the things that are valid numbers (like 00E12) it converts to numbers during the import. 0 x 10^12 = 0 so that is what it enters and since it sees it as scientific notation it formats the value 0 as such. But since it reads all as zero (eg 00E01 = 00E12 = 00E27 = 0) you lose the value you want.

You need to import it NOT as CSV but as plain text (you may have to change the extension from CSV to TXT) and then use the import wizard to DEFINE the column as TEXT so it will not be converted. Opening it as a single column of text and using the Text to columns may work if it can be imported as fixed width. If not you may have to a find-and-replace in the text to find (no quotes) ” 00E” and replace it with (no quotes) ” ’00E”

Steve

Reply | Quote

Here is my work-around…

1. When exporting from SAP I save as .txt
2. In that file, Find all “E” and Replace with “Z” and save.
3. In Excell: Use Data from text and import to open sheet
4. Highlight the BIN column, then Find ’00” and replace with nothing
5. Unhighlight column, then Find “Z” and Replace with “E”

This is as good as I can get it; it is understood that all bins begin with “00”, which means it is understandable in the altered form–missing the two zeros in front.

Tom

Reply | Quote

How about converting 00E to ’00E (note the single quote at the beginning), and forget the Z? This should import the 00E strings as text. I have similar problems importing strings like this in the past.

Another approach may be to set this column to text in the Excel import wizard when importing it as a text file.

-Mike

Here is my work-around…

1. When exporting from SAP I save as .txt
2. In that file, Find all “E” and Replace with “Z” and save.
3. In Excell: Use Data from text and import to open sheet
4. Highlight the BIN column, then Find ’00” and replace with nothing
5. Unhighlight column, then Find “Z” and Replace with “E”

This is as good as I can get it; it is understood that all bins begin with “00”, which means it is understandable in the altered form–missing the two zeros in front.

Tom

Reply | Quote

The problem you may encounter with this approach is that any Zs you had in the file, will be Es at the end…

Steve

Reply | Quote

Okay. I did some more fiddling…

SAP export to HTML format saved to desktop==>open HTML file and put cursor on top to “select all.”==>Copy==>open Excel and put cursor in first field==>paste to match destination formatting.

No Find and Replaces necessary! Everything is the same as it ever was–sorry for the Talking Heads reference.

Reply | Quote

We’ve had this problem in our office. We use Office 2013 Pro and some people are able to export to Excel without errors, others aren’t. We finally found that the people experiencing the errors like you had the default Region settings in Windows Control Panel.

We changed the decimal point from a comma (,) to a period (.) and used a coma as a separator (,) and the same for our currency.

Default:
$123 456,00

Changed to:
$123,456.00

Now Excel is able to read the exported data correctly. Hope this helps…

Reply | Quote

How about exporting it as CSV, import it into Access, where you can define the BIN column as text, make any corrections/mods (like put a ” at the beginning of the line) and then exporting it as an XLS file?

Without a .csv to try, I can’t really tell if that would work

never mind – just seen you’ve found a workaround using HTML

Alan

Reply | Quote