1

I can make Microsoft Excel open up a semicolon separated value text file by placing "sep=;" at the beginning of the file. Then upon double clicking, it properly separates every column and row into the correct cells. However, the issue with this is that it makes the file unusable to other programs that aren't expecting the "sep=;" line at the very beginning. But if I remove this, then Excel opens it and doesn't properly map the values to the correct cells (a single cell will have several entries, including the semicolon separator).

I can always use the "Data -> From Text/CSV" feature to import the file, but this is slow and takes a while, especially if I just want to quickly view the contents.

Is it possible to make Excel correctly display the data in a semicolon separated value file without changing the content of the file when double clicking on it? Or am I out of luck?

I am also OK with changing the file extension to something unused if necessary, such as ".scsv" instead of ".csv" if this will require some registry tweaks. The other programs that require the semicolon separated value files don't care about the extension, only the file content.

Improve this question
1
  • I don't know of any way to do that. But perhaps you could program a button in some Excel file that would run a Power Query that could open and properly parse the file. That would save having to specify the delimiters (and other information) each time.
    – Ron Rosenfeld
    Commented Mar 21, 2021 at 18:14

1 Answer 1

Reset to default
1

Here are a few ways to skin the csv cat:

  • Rename to .txt seems to always pop-up with the text import wizard (slow, but not as slow as using the Data > Text to columns feature)
  • You can make a batch file to auto-add the sep=; line to a temp csv file and open it with excel. Right-click your csv file and select "open with" > "Choose another app" > "More apps" > "Look for another app on this pc", and select your batch file (also check "Always use..."). You can also drag the csv file onto the batch file. Here's the code, put in notepad and save as "SCSVOpen.bat" (change the path to excel as required):

.

@echo off
echo sep=; > c:\temp\temp.csv
type %1 >> c:\temp\temp.csv
"c:\program files (x86)\microsoft office\office16\excel.exe" temp.csv
  • Last but not least, you can make an "SCSV Opener.xlsx" file. This method will prompt with an "Import text file" open file dialog box as soon as you open it, so you can open the csv using predefined import options. Create a blank workbook, then use the Data > From text option and set the delimiter once using the wizard. Before finalising the import, where you designate where the data in the spreadsheet should go, click properties. Set it like this:

enter image description here

Improve this answer
2
  • If you get the semicolon csv files from a specific source, it might be easier to rename them .ssv or .scsv when you get them. Then associate the batch file as the default program to open all .ssv files. This way normal csv's will still open in Excel as it should.
    – ExcelEverything
    Commented Mar 23, 2021 at 10:53
  • Oh and you might have to modify the code if you want to open more than one scsv file at a time...
    – ExcelEverything
    Commented Mar 23, 2021 at 11:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .