1

Need to make Excel use comma as a default delimiter if I drag a .csv file to Excel window or select a .csv file from Windows Explorer. None of the suggestions on this site or combinations therof have worked. In Control Panel - Regional Settings - System Separators I have set comma as the default delimiter and set space character as thousands separator. In Excel I tried using System Separators and NOT using System separators with Decimal set to dot . and Thousands set to a single space, so that comma would only be used as delimiter, but nothing seems to work.

We have hundreds or workers each opening dozens of .csv files each day and it's much easier to do this from Windows Explorer than from inside Excel and having to set comma delimiter each time.

dclark
  • 29

2 Answers2

1

Thanks for the prompt responses. If I edit the first two words of my post, perhaps the question becomes more clear: by changing "Need to . ... " to "How can I . . . . ". OK, anyway I found the actual reason the files don't open as expected. The files I want to open are actually in csv format but their extensions are .rpt (this extension has no meaning to anyone outside our company). If I rename them with .csv extension then they open delimited correctly using the default Excel 2007 and Windows 7 delimiter settings.

I still want to open .rpt files without any name changing, so I came up with a tiny macro I saved in Excel's XLSTART folder so it will always be available, and with a hotkey assignment to the macro this is really all I need. The macro uses the text-to-columns function after selecting column A. I just recorded the macro from Developer menu.

dclark
  • 29
1

Another solution, which may or not be possible for you, but maybe useful for others searching this thread in future!
If you have the line sep=; as the first line of the file, Excel will honour that separator. This works with csv files and I also tried making an rpt file.
This solution was found from How to open semicolon delimited csv files in us version of excel --> credit goes to @atx and I wish I had searched this myself in the past!

Community
  • 1
Captain
  • 166