38

I am running excel and would like to export my file as a .scsv (semi-colon separated values) sheet instead of a .csv. Is there any way to do this?

ps. I can't do a .csv and search and replace commas with semi-colons because the fields contain commas already.

3
  • If you're open to doing a little programming, a converter could be written fairly easily in the Python programming language using its built-in csv module which would make it easy to read Excel .csv files and then write new versions of them that had just about any delimiter your desired.
    – martineau
    Sep 17, 2012 at 21:38
  • It depends if there are commas in the entries or not.
    – AER
    Dec 3, 2014 at 0:54
  • Instead of trying to modify manually, another option is: 1. Go to Control Panel > Clock and Region > Region > Additional Settings 2. Change List Separator from comma to: ";" That's it. When you save your file as a csv, MS will use semi-colon. No need to modify manually Note: Do change it back to comma for saving future csv files with comma separation Dec 15, 2022 at 20:48

12 Answers 12

26

How about doing Tab Delimited and replacing the tabs with semi-colons?

In excel: File -> Save As -> in Format select "Tab Delimited Text (.txt)" then his save.

Open the file in notepad and open the replace window. Since you can't tab directly into this window, copy a tab from your document and then paste it into the Find box and put your ; into the replace box. Then replace all.

I assume this would work because it is very rare to have tabs within an excel document.

5
  • Although basic for most users, could you provide a set of instructions as well? Sep 17, 2012 at 21:06
  • Can that be done entirely within Excel and if so, how?
    – martineau
    Sep 17, 2012 at 21:22
  • I don't think it can be done entirely in Excel. Excel hides the formating (tabs) so you wouldn't be able to replace them. After replacing them with semi-colons you wouldn't be able to open with excel either (it'd just be one giant cell)
    – Robert
    Sep 17, 2012 at 23:01
  • I assume using regex to replace and placing \t would work Feb 22, 2017 at 14:02
  • When you have special characters of a different language in your file (like Greek) this doesn't seem to work as the characters symbols are replaced with '?'
    – Joos
    Feb 3, 2019 at 11:49
12

I don't think you can set output separator directly in Excel, but (assuming Windows) you could change OS list separator to ; - that's done in Regional Settings->Customize. Excel will use that setting outputing your csv files.

3
  • 7
    Important note: if you do this, all Windows programs that use the Internationalized Windows libraries will assume that ; is the separator character for all CSV files. If this is a one time solution, it probably makes more sense to save a regular CSV file and use an editor to change the commas to semicolons Sep 18, 2012 at 3:04
  • This change will also affect Opening of CSV files in Excel. And as mentioned in comment above, possibly more programs. If you want to make more compatible CSV file, put sep=; on top of the CSV file, then once you save the file, you can change List Separator back to whatever it was.
    – papo
    Dec 30, 2015 at 3:04
  • I want to point out that is a Windows Control Panel setting in case you're looking all over the place in Excel (as I was). As mentioned in other comments, this affects all programs using the setting. For my case, I'll be changing the setting temporarily to export the sheet and then revert back.
    – Whip
    Jul 26, 2020 at 5:31
10

Use LibreOffice Calc (it's a free download, and works on all major operating systems).

To save to semi-colon separated values:

  1. Open your xlsx/csv file
  2. File -> Save As...
  3. Choose "Filter CSV" as the filter (should be default if your file name ends with .csv)
  4. Tick "Edit filter settings" and press Save
  5. When it asks for confirmation of the file format, press "Use Text CSV Format"
  6. Change the "Field delimiter" to a semi-colon (you can type anything in here).
  7. Press OK.

LO Calc will correctly handle commas and semi-colons in cell values.

1
  • 1
    Thank you, works well. This should be the accepted answer. Dec 4, 2017 at 11:07
5

If you would like a script, paste the following in a module.

Option Explicit
Sub export2scsv()
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim strString As String
    Dim i As Integer, j As Integer

    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

    Open "output.scsv" For Output As #1

    For i = 1 To lastRow
        Cells(i, 1).Select
        strString = ""
        For j = 1 To lastColumn
            If j <> lastColumn Then
                strString = strString & Cells(i, j).Value & ";" ' Use semicolon instead of pipe.
            Else
                strString = strString & Cells(i, j).Value
            End If
        Next j
        Print #1, strString
    Next i

    Close #1
End Sub
4

1.> Change File format to .CSV (semicolon delimited)

To achieve the desired result we need to temporary change the delimiter setting in the Excel Options.

Move to File -> Options -> Advanced -> Editing Section

Uncheck the “Use system separators” setting and put a comma in the “Decimal Separator” field.

Now save the file in the .CSV format and it will be saved in the semicolon delimited format !!!

4
  • 5
    Taken from ashwaniashwin.wordpress.com/2013/04/19/… Please note that attribution is required.
    – Arjan
    Apr 28, 2014 at 7:11
  • worked perfectly May 11, 2020 at 8:17
  • It's a shame that this question failed to provide proper attribution. After 7 years, a low quality answer was submitted, and now I have to downvote what very likely was a better quality answer than was submitted today.
    – Ramhound
    Jul 7, 2021 at 21:14
  • Actually, this could be the simplest and yet correct approach. Saved my day. Uncheck "Use system separators" and save as "comma separated" actually saves as "semicolon separated". Feb 18, 2022 at 13:34
4

To change comma to semicolon as the default Excel separator for CSV - go to Region -> Additional Settings -> Numbers tab -> List separator and type ; instead of the default ,

4

You can change the separator globally through Customize Format.

Browse to Region & language, open Region, on tabsheet Formats click the button Additional Settings, and on tabsheet Numbers change the value for List separator:

1
  • Don't just post a link to the answer, include the salient details in the answer. If the targetted page goes away, then the answer is useless. Also, although I'm no Windows expert, this looks the same as wmz's answer from three years ago.
    – blm
    Jan 16, 2016 at 17:50
1
  1. In an empty column after the end of your data, write the formula =TEXTJOIN(";", FALSE, A1..F1) in the top cell, (assuming data in column A through F), then copy the formula down the column.
  2. Now this column will have the semi-colon separated text you want, just copy and paste it to Notepad and save.

This is similar to using the CONCATENATE function mentioned here, but it's a little easier to write the function.

1

Depending on your language ( * ), using Save As and selecting CSV (MS-DOS) (*.CSV) file format might fit your needs, since it creates a semi-colon separated text file.

( * ) Your text data must match ASCII printable characters (character code 32-127). Check blue-marked characters on provided table

Since you are looking on replacing decimal separator, check @user318853's suggestion.

0

If your list of values is in column A, try this -- Cell B2 =A1&";"&A2 Cell B3 =B2&";"&A3 Copy cell B3 to the bottom of your list. If you copy and paste the last cell in your list as values, you will then have your semi-colon separated list. You can then copy that into Notepad, Word, or wherever you so desire.

0

You can do this easily in 4 steps with Excel's concatenate function.

  1. In your file, add a column of semicolon characters where you would expect them to land in a .csv export.

  2. Start a new column at the end of your data and in the first row, use the =concatenate() function. Be sure to select all the cells you want to appear in the final file. Your formula will look something like =concatenate(A1,A2,A3...)

  3. Copy and paste the resulting formula down through all the rows of data.

  4. Select the whole column of formulas and Paste Special -> Values into the neighboring column. Now you have all your data delimited by semicolons.

Finally, export to your file format of choice. If you export this as .txt in Windows you can simply change the file extension to .csv because both are plain text formats.

-4

I use in simple way:-

  1. Open csv(semicolon) file with Notepad or Notepad++.
  2. Find and Replace(Ctrl+H) from semicolon(;) to comma(,).
  3. Save and close file.
  4. Now, open modify file with Ms-Excel.
1
  • 1
    This is going in the wrong direction.  The question asks how to create a semi-colon separated values file; this answer assumes that a semi-colon separated values file already exists and describes how to create a CSV file — incorrectly, as it doesn't take into consideration the key constraint of the question: namely, that the data are known to contain the delimiter. Apr 13, 2015 at 19:54

You must log in to answer this question.

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