Help with CSV into Excel for Mac

[twwareza]

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. 

[#2]

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!


 

[#3]

@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.

[#4]

@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

[#5 · Accepted solution]

@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.

[#6]

@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

[#7]

@twwareza You're welcome!

[#8]

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

[#9]

@Darya_V_23 Not sure I follow, Sorry! Better to remove your post here and start a new thread as the original post was about importing CSV into Excel. Your question deals with exporting. As a general note, though, when you export an Excel file to CSV (or save as CSV), you loose all formatting and formulas. And if you have more than one tab, only the active tab will be saved as CSV, so you'll loose everything from the other tabs.