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.
oh. I see what happening.
The exported CSV has one field with multiple entries separated by commas.
The fields are also separated by commas.
So in the import MS cannot see these as different obviously.
Does anyone have a solution, since I have no control over the format of the exported CSV?
Thanks!
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
Rezatwwareza 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.
Reza