Forum Discussion

twwareza's avatar
twwareza
Copper Contributor
Jan 14, 2021
Solved

Help with CSV into Excel for Mac

I use Excel for Mac and I tried to import a CSV file using the "Get External Data" option.  My problem is I cannot seem to get the original CSV into the correct format to be displayed.  Does an...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 15, 2021

    twwareza Perhaps you already figured it out, but just in case you haven't. Your csv-data contains files with line break and fields with "" (i.e. nothing). See pictures below. Excel doesn't handle these properly when you use the text/csv import wizard. Don't ask me why, I'm not an expert in that area.

     

     

    Open the csv in the TextEdit app on your Mac and do a Find off line feeds (type Alt-Enter in the Find field and type a space in the Replace field. Press Replace All.) Then, also replace all quotation marks with  nothing. Save the file (with a different name, to be safe) and now import the new file into Excel. Added a cleaned csv file and the resulting xlsx after import. Can you automate these steps? Probably yes, but I can't help you with that. There are resources on the web that explain how to do this with VBA, though.

     

    Then, you mention that you can't find how to connect to a folder. I'm afraid, that comes with "Get and Transform Data", a.k.a. Power Query which is NOT available for Mac users.

twwareza's avatar
twwareza
Copper Contributor
Jan 15, 2021

Riny_van_Eekelen - hey thanks!

 

Yah. That works - even via Numbers when double click from the folder. 

I am trying to work out a way to automate some reports and need to be able to read the CSV via Get Data in Excel. This way, all new data will be populated via CSVs. 

I also could not find a way to connect a folder of excel files as I did not see a get option for this. Is this possible?

Thanks

Reza


Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 15, 2021

twwareza Perhaps you already figured it out, but just in case you haven't. Your csv-data contains files with line break and fields with "" (i.e. nothing). See pictures below. Excel doesn't handle these properly when you use the text/csv import wizard. Don't ask me why, I'm not an expert in that area.

 

 

Open the csv in the TextEdit app on your Mac and do a Find off line feeds (type Alt-Enter in the Find field and type a space in the Replace field. Press Replace All.) Then, also replace all quotation marks with  nothing. Save the file (with a different name, to be safe) and now import the new file into Excel. Added a cleaned csv file and the resulting xlsx after import. Can you automate these steps? Probably yes, but I can't help you with that. There are resources on the web that explain how to do this with VBA, though.

 

Then, you mention that you can't find how to connect to a folder. I'm afraid, that comes with "Get and Transform Data", a.k.a. Power Query which is NOT available for Mac users.

  • twwareza's avatar
    twwareza
    Copper Contributor
    Jan 15, 2021

    Riny_van_Eekelen oh you are a star. Thank you. Thank you. I did not know how to do that.

    I now see the need to get Windows installed as this power query function seems to make reporting lighter work!

    Thanks again. 

    Reza

      • Darya_V_23's avatar
        Darya_V_23
        Copper Contributor
        Feb 21, 2023

        Hello Riny_van_Eekelen, 

        could you give me please some advice regarding export Microsoft List in CSV file on MAC device?
        When I do export  the fie loses the correct format and looks like on the picture bellow. Is the problem of this "" signes? How can I get a correct format of the file after export?

        Thank you in advance for response and help. 

        Best regards, 
        Darya

         

         

Resources