How do I save an excel spreadsheet as a semi-colon separated values file?

by Kamilski81   Last Updated December 07, 2017 10:01 AM

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.



Answers 10


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.

Robert
Robert
September 17, 2012 20:50 PM

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.

wmz
wmz
September 17, 2012 22:00 PM

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
user157938
user157938
September 18, 2012 11:08 AM

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 !!!

user318853
user318853
April 28, 2014 06:48 AM

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.
vineet
vineet
March 27, 2015 07:46 AM

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:

Santa Fee
Santa Fee
January 15, 2016 17:35 PM

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 ,

Michael
Michael
February 15, 2016 07:53 AM

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.

warthog
warthog
October 28, 2016 15:39 PM

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.

naught101
naught101
February 15, 2017 02:53 AM

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.

Greg Cavanaugh
Greg Cavanaugh
December 07, 2017 10:00 AM

Related Questions



Listbox, mouse wheel scroll issue

Updated October 22, 2015 08:00 AM

Formula issue Microsoft Excel

Updated July 02, 2015 16:00 PM

If Difference Higher or Lower than Display

Updated August 03, 2015 15:00 PM