Forum Discussion
Help with CSV into Excel for Mac
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 First, I opened the csv from within Excel (like you, also on a Mac) and applied Text-to-columns, resulting in a mess. Threw it all away and opened the csv file by double clicking it in its folder. Excel opened it without problems. See attached.
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
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.
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.
Rezatwwareza You're welcome!