Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Problem Exporting Spreadsheet from SAP to Excel

    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

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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

  3. #3
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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".

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  5. #5
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Can you post a sample export - just a couple of lines - and we can try some things for you.

    cheers, Paul

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  8. #8
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    held sample.xls 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.

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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

  10. #10
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    screen shot.docThese are my export choices:

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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)?
    Last edited by sdckapr; 2013-06-03 at 05:41.

  12. #12
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sap screenshot.doc 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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  14. #14
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The problem you may encounter with this approach is that any Zs you had in the file, will be Es at the end...

    Steve

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •