Encoding issue when previewing CSV file on Android Google Sheets application

Question

I am currently developing a new feature in my Android application that allows users to export their data as a CSV file.

The export process is managed on the backend. On app side, we download the binary, save it temporarily, and use the ACTION_VIEW intent for the preview. This allows the user to choose their preferred app for viewing and sharing the file.

However, I am encountering an issue with the encoding, but only when using the Sheets application. Opening the file on PC, macOS, or even Google Sheets web works as expected.

Share component	Google Sheets app preview

On Android

We fetch the document as below:

...
override suspend fun getDocument(file: File): File {
    val body = http.execute(
        call = { service.getDocument(...) },
        onError = { ... },
    )
    withContext(dispatchers.io) {
        file.writeBytes(body.source().readByteArray())
    }
    return file
}
...

then we open it by using the intent:

...
fun createFileIntent(context: Context, file: File, mediaType: MimeType? = null): Intent {
    val contentUri = file.getContentUri(context)
    val type = mediaType?.value ?: contentUri.getMimeType(context)
    val intent = Intent(ACTION_VIEW)

    intent.setDataAndType(contentUri, type)
    intent.flags = FLAG_GRANT_READ_URI_PERMISSION
    return intent
}
...
private fun showCsv(file: File) {
    val intent = createFileIntent(requireContext(), file)
    startActivity(intent)
}

On backend

Response.ok(outputStream(activitiesCSV))
    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"activities.csv\"")
    .header(HttpHeaders.CONTENT_TYPE, "text/csv; charset=utf-8")
    .build()

private StreamingOutput outputStream(byte[] bytes) {
    final byte[] bom = new byte[] { (byte) 239, (byte) 187, (byte) 191 };  //UTF-8 BOM
    return outputStream -> {
        outputStream.write(bom);
        outputStream.write(bytes);
        outputStream.flush();
    };
}

activitiesCSV is a byte[] that is retrieved from the DB. If I do not add the UTF-8-BOM, the special characters are not recognized when opening the csv in MS Excel but here we have the issue on Google Sheets app.

Answer 1

We resolved the issue by removing the BOM header on the backend and adding the following code on the Android side, which ensures the conversion from UTF-8 to ISO_8859_1 (Latin 1).

override suspend fun getDocument(file: File): File {
    val body = http.execute(
        call = { service.getDocument(...) },
        onError = { ... },
    )
    withContext(dispatchers.io) {
        val sourceByteArray = body.source().readByteArray()
        val byteArrayToWrite = when (fileType) {
            CSV ->
                sourceByteArray
                    .toString(body.contentType()?.charset() ?: UTF_8)
                    .toByteArray(ISO_8859_1)
            else -> sourceByteArray
        }
        file.writeBytes(byteArrayToWrite)
    }
    return file
}