Importing csv file

Occasional Visitor

1 - I need to import csv files into Excel. When I do so, I always need to navigate through the wizard to change the options from Tab delimited to comma. How can I change my settings so that excel will know to always use 'comma separated' and automatically open the file with the data in distinct columns without having to use the wizard?

 

2 - Similarly, when I export data from Excel to a csv file, every piece of data becomes concatenated into one cell and the third party application I am using cannot recognized in the csv file I created a 'parsed' record for data mapping.  How can I resolve this issue?

 

Thanks you very much,

 

Lucie H

1 Reply

Hi @LucieH135 

 

according to your description a assume you have to do these imports on a frequent basis.

Therefore I strongly suggest to use Power Query instead of the old import wizard.

 

It's not really complicated, you just need to get used to it. You find this functionality in the Data menu:

Martin_Weiss_0-1651577741585.png

 

Once you have imported it in Excel, you just need to replace the recent CSV-file with the new one (keep the same name) and do a refresh in Excel (right-click in the table, Refresh). And it will get the new data.

 

Regarding the CSV export:

If you save a CSV-file from Excel, the information from all the different cells are actually separated by a separator character. Which is a usually a comma or semikolon, depending on your regional local settings (CSV = comma separated values). And in case you use the comma (or semikolon) within your cells, Excel puts these values into quotes in order to be able to keep the proper cells.

 

From a distance it's hard to say why your third party application cannot deal with it. Maybe there is something special with your file. Could you provide an example?