Forum Discussion
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 anyone know what options to select in the process to make sure MS knows what I need done to the CSV text.
I have enclosed the original CSV and the result.
There are too many permutations for me to go through !
Help.
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
Reza
