View original (French)

Loss of "0s" when converting a file

Solved
sebi68 Posted messages 87 Registration date   Status Member Last intervention   -  
 tatichak -
Hello,

I have a problem when converting an xls file to csv; I lose the leading 0s in front of the numbers
e.g.: 00789 becomes 789

before the transfer I set the cell format to text, but it didn't change anything...

does anyone have any ideas? please

thank youuuuuuuuuuuuu
Configuration: Windows 2000 Internet Explorer 6.0

8 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    It's normal for you to lose the 0s, it's a display format but the number is indeed 789.
    You need to convert them to text by inserting a column and using, for example, =RIGHT("00000"&A1,5)
    Then do a 'copy/paste special values' before deleting the original column.
    eric
    4
    1. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
       
      Hello, thank you for participating ...

      I did this too, but when I convert the xls file to csv and then save it, close it, and reopen it, the 0s no longer appear ...

      See you later
      0
      1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > sebi68 Posted messages 87 Registration date   Status Member Last intervention  
         
        Oh well, if you open it in Excel, it will try to convert everything that can be numeric, you'll need to reset the formats. But in your CSV file, you do have your zeros.
        Personally, I don't really see the point of saving in CSV if it's to reopen it in Excel... Why don't you explain what you're trying to achieve instead?
        Eric
        0
  2. nes
     
    Hello,
    I found the solution in another forum (http://www.excelabo.net/trucs/texte-csv).

    You need to enclose all the cells (in the constructed .csv file) like this:
    =("00001")
    3
    1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      ... hoping there aren't thousands of them, as is probably the case here!
      0
    2. tatichak
       
      Great, thank you!
      0
  3. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
     
    Hello, and thank you for participating

    I made sure the 0s are there in xls, I save it as csv on the screen before closing the file, it's fine then.....

    same thing!!! I reopen my csv file and no more 0!!!

    I think that simply a csv file cannot display 0s before anything else....

    sniff, see you later!
    1
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      I repeat: the 0s are in the csv file!!!
      Open it with Notepad to see for yourself.
      2
    2. sylvlau
       
      Good evening,

      Sorry Sebi68. That works. You persist in seeing or entering 0s on the screen. Which is "futile."
      Open the generated csv file with Notepad or Wordpad. You'll see that it's fine.

      Talk to you later
      1
  4. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
     
    Hello,

    yes I see ... indeed I really wanted to see the 0s when opening the file in Excel. With Wordpad, the famous 0s are definitely there!!!

    Thank you! Have a good Monday ...
    1
  6. Anonymous user
     
    Hello,

    Try with Format / Cell / Number / Custom,
    in Type: 00###
    adapting the number of hashes to the number of digits.

    --

    Argitxu
    0
  7. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
     
    I tried, but it's the same, I lose the zeros when I reopen the CSV file...

    Thanks for helping with my issue!!!

    I think I've tried everything... it must not be possible...

    See you!
    0
  8. Anonymous user
     
    I'm sorry... I have no other idea
    But wait, someone will definitely have another suggestion.
    --

    Argitxu
    0
    1. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
       
      yes

      the only solution I just found is to keep the csv file open for further use...
      that is to say, saving it, but keeping the file open (with the 0 on the screen)

      enjoy your meal!
      0
  9. sebi68 Posted messages 87 Registration date   Status Member Last intervention   3
     
    Okay, I'll try to be a bit clearer...

    I'm using a CSV file for data import into a CMMS software

    for items, for example, I have the item code with 7 digits 0012345

    initially, I have an XLS file, the import into the CMMS software goes through an application that requires a CSV file. I'm therefore forced to convert my original file into CSV and then I lose the leading zeros...

    hoping I've been a bit clearer, thank you for your participation!!!!
    0
    1. sylvlau
       
      Hello,

      You're so close to the answer and ...

      You've understood it, Excel strives to treat numbers faithfully. Your insignificant zeros are just fluff.
      So... as Alphonse Allais said: logic leads to everything as long as you know how to... get out of it!!!

      So turn your numbers into strings!
      Imagine you have 125 in cell A1. You put this formula in a new cell:
      =RIGHT("0000000"&TEXT(A1,0),7)

      The result will be a text string "0000125" that should be suitable for your CMMS software.
      You do a copy/paste special (values) on your original cell and you're good to go (just for testing).
      I actually recommend preparing a new sheet, which will only contain your formulas referencing your original sheet (like in my example) with your 000???? values in text format. It’s this new sheet that you will export.

      Keep me updated. See you soon!
      0