Ruby: How to Generate CSV Files That Has Excel-Friendly Encoding

How to generate .csv file for Excel without error messages?

Does your output file start with 'ID' ? If so then open the csv in a text editor and change it.
see http://support.microsoft.com/en-us/kb/215591 for more info

EDIT:

That link is now dead - however using the wayback machine it said:

MORE INFORMATION A SYLK file is a text file that begins with "ID" or
"ID_xxxx", where xxxx is a text string. The first record of a SYLK
file is the ID_Number record. When Excel identifies this text at the
beginning of a text file, it interprets the file as being a SYLK file.
Excel attempts to convert the file from the SYLK format, but is unable
to because there are no valid SYLK codes following the "ID"
characters. Because it cannot convert the file, Excel generates the
error.

https://support.microsoft.com/en-us/help/170245/prj-files-saved-as-csv-can-t-be-opened-in-microsoft-excel

This is another link which explains it - not sure how long this link will last either so here's a screen capture for future reference also:

write.csv() writes a different result from Mac OS than from Windows 10?

The problem isn’t R, the problem is Excel.

Excel has its own ideas about what a platform’s character encoding should be. Notably, it insists, even on modern macOSs, that the platform encoding is naturally Mac Roman. Rather than the actually prevailing UTF-8.

The file is correctly written as UTF-8 on macOS by default.

To get Excel to read it correctly, you need to choose “File” › “Import…”, and from thre follow the import wizard which lets you specify the file encoding.

Is it possible to force Excel recognize UTF-8 CSV files automatically?

Alex is correct, but as you have to export to csv, you can give the users this advice when opening the csv files:

1. Save the exported file as a csv
2. Open Excel
3. Import the data using Data-->Import External Data --> Import Data
4. Select the file type of "csv" and browse to your file
5. In the import wizard change the File_Origin to "65001 UTF" (or choose correct language character identifier)
6. Change the Delimiter to comma
7. Select where to import to and Finish

This way the special characters should show correctly.

How can Defining fields delimiter character, Encoding and Records seperator ({CR}{LF}) for CSV files in export (Save As) from Excel by VBA

Option Explicit

Const strDelimiter = """"
Const strDelimiterEscaped = strDelimiter & strDelimiter
Const strSeparator = ","
Const strRowEnd = vbCrLf
Const strCharset = "utf-8"

Function CsvFormatString(strRaw As String) As String

    Dim boolNeedsDelimiting As Boolean

    boolNeedsDelimiting = InStr(1, strRaw, strDelimiter) > 0 _
        Or InStr(1, strRaw, Chr(10)) > 0 _
        Or InStr(1, strRaw, strSeparator) > 0

    CsvFormatString = strRaw

    If boolNeedsDelimiting Then
        CsvFormatString = strDelimiter & _
            Replace(strRaw, strDelimiter, strDelimiterEscaped) & _
            strDelimiter
    End If

End Function

Function CsvFormatRow(rngRow As Range) As String

    Dim arrCsvRow() As String
    ReDim arrCsvRow(rngRow.Cells.Count - 1)
    Dim rngCell As Range
    Dim lngIndex As Long

    lngIndex = 0

    For Each rngCell In rngRow.Cells
        arrCsvRow(lngIndex) = CsvFormatString(rngCell.Text)
        lngIndex = lngIndex + 1
    Next rngCell

    CsvFormatRow = Join(arrCsvRow, ",") & strRowEnd

End Function

Sub CsvExportRange( _
        rngRange As Range, _
        Optional strFileName As Variant _
    )

    Dim rngRow As Range
    Dim objStream As Object

    If IsMissing(strFileName) Or IsEmpty(strFileName) Then
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:=ActiveWorkbook.Path & "\" & rngRange.Worksheet.Name & ".csv", _
            FileFilter:="CSV (*.csv), *.csv", _
            Title:="Export CSV")
    End If

    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = 2
    objStream.Charset = strCharset
    objStream.Open

    For Each rngRow In rngRange.Rows
        objStream.WriteText CsvFormatRow(rngRow)
    Next rngRow

    objStream.SaveToFile strFileName, 2
    objStream.Close

End Sub

Sub CsvExportSelection()
    CsvExportRange ActiveWindow.Selection
End Sub

Sub CsvExportSheet(varSheetIndex As Variant)

    Dim wksSheet As Worksheet
    Set wksSheet = Sheets(varSheetIndex)

    CsvExportRange wksSheet.UsedRange

    End Sub

Reference

---

Related Topics