Some Currency symbols not showing in Ms excel

Question

I'm generating a CSV like this

::CSV.generate(headers: true) do |csv|
  csv << ['Date', 'Transaction', 'Order Total', 'Wallet Amount', 'Wallet Balance']
    fetch_data.each do |data_response|
    data_response.wallet_amount = "₹40"
    csv_data = { date: data_response.display_date} 
        .merge(data_response.to_h.except(*skip_attributes))
    csv << csv_data.values
  end
 end

Now after generating it when I open it in Linux it works fine but not for Ms-Excel. Currency symbols like ₹, € not showing properly.

Here is the sample picture from Ms-Excel

and when i tried to convert it in iso format like this

::CSV.generate(headers: true, encoding: 'ISO-8859-9')

the error i got is

Encoding::UndefinedConversionError (U+20B9 from UTF-8 to ISO-8859-9)

is there any way that my euro and INR symbols can show on Ms-excel

Blackfoot

You face a mojibake case (example in Python for its universal intelligibility): '₹ €'.encode( 'utf-8').decode('cp1254') returns '₹ €'. – Organic

@Organic so how can i tackle this ? – Blackfoot

I think that Excel has File -> Import function where you can define encoding of input .csv file. Determine UTF-8. – Organic

@Organic but we are sending this csv to non-technical persons – Blackfoot

Please check this: superuser.com/search?q=excel+csv+bom – Organic

Does this answer your question? Microsoft Excel mangles Diacritics in .csv files? – Organic

yeah it is helpful I'm going to upvote it but now I'm sharing the exact solution @Organic thanks a lot for your help – Blackfoot

Answer 1

So finally I solve this by attaching a UTF-8 BOM as a prefix to my file Here is the code

with_utf8_bom( ::CSV.generate(headers: true, encoding: Encoding::UTF_8) do |csv|
      csv << ['Date', 'Transaction', 'Order Total', 'Wallet Amount', 'Wallet Balance']
      fetch_data.each do |data_response|
        csv_data = { date: data_response.display_date }.merge(data_response.to_h.except(*skip_attributes))
        csv_data[:wallet_amount] = '€70'
        # debugger
        csv << csv_data.values
      end
    end )

private

  def with_utf8_bom(content)
    "\uFEFF" + content
   end

I hope this will help someone to tackle this issue.

Blackfoot