Forum Discussion

Fazel Khota's avatar
Fazel Khota
Copper Contributor
May 05, 2017

csv files

i use to download my bank statements in csv and it use to open automatically with excel in sepearated columns- that was using 2013 . Now im using the latest excel and  it doesn't. When I use the Get External Data  and use the delimited format its arrange correctly only that none of the number text are readable and can convert to numbers for calculation purposes. file is also attached 

excel

4 Replies

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 12, 2017

      Frances,

       

      You latest file is empty, but the file from first post is opened correctly in any version of Excel if only you have proper regional settings - semicolon for List separator and the dot for Decimal symbol.

       

       

      1) When double click on CSV file Excel splits it on 3 columns and correctly recognizes the date in first column and number in third one.

       

      2) Import from csv file with 3-step (old style connector) requires only the dot as decimal settings, you may select semicolon as separator within the wizard and YMD for first column, General for two others.

       

      3) Modern connector in latest version of Excel with correct regional settings recognizes the file in one step and returns the table with correct date and number formats in first and third columns (with default settings).

       

      Thus first please check the regional settings on your computer. If they are as above and import still doesn't work when more details will be required, but let start from this.

       

  • vijaykumar shetye's avatar
    vijaykumar shetye
    Brass Contributor
    May 07, 2017

    Dear Fazel Khota,

    When you claim to use the latest excel, which version are you refering to, 'Excel 2016'?

    Since 'it doesn't' work the way you want it to, can you provide details of

    (1) What is happening,

    (2) What is expected to happen.

    If possible attach the file with the unexpected result.

     

    Vijaykumar Shetye,

    Spreadsheet Excellence,

    Panaji, Goa, India

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 05, 2017

    Hi Fazel,

     

    It looks like you updated not only Excel - all versions parse csv files in the same way, no changes is here.

     

    You have semicolon as the separator in you csv file. For your Windows/Mac (not Excel) check Regional format, here "Change date, time and numbers format" under it Additional setting. You will see List separator here, change it on semicolon and save.

     

    Now your csv file shall be opened and parsed correctly by Excel if you click on the file name in File explorer.

     

    However, please take into account that could be negative side effect in other functions or applications which also used list separator to parse the text. For example, if you have (or someone forward to you) the Excel file with INDIRECT function where comma separates part of the within INDIRECT text, yiu'll receive an error. On the other hand, you could never meet such.

Resources