0

I am getting a CSV file as a Blob in response to one of the callouts. I need to parse this CSV file and convert the contents into a flat JSON structure. Is there an inbuilt way to parse CSV in apex ?

Sample CSV (please note that even though it looks like a simple comman separate value in the sample, it actually comes as a blob in response, so I need to account for that) -

ROW_INDEX,Year,Professional
1,2020,1

Output JSON required -

{
    "ROW_INDEX": "1",
    "Year": "2020",
    "Professional": "1"
}

JSON obtained after using the method suggested in the comments -

[{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"��\u0015�n\u0002B(�&��~_����}:�8�֘�\u0004_�8C��6~Y������3��5���[$>��M\u0017ۈĊ�S�ۜ㕔�Zt@\"D�\u0005iBr��3-e\u0004��\u0012�yU]H\u0015&#124;F�ǹ���"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"6����n���HBK�]�}V�!Fk\u0014�˵׿R��\u0004Q�\u0003�Z\u0013iT\b\\\u001EL葿\u0003�����d4�GH�\u001E\\aɍ��!u/!t��\u0003-C�\u0018�:��+\u0012A1!hj\u0011��b����\u0003��0&'.���\u0007��P?�T��N��\u001F�_=�pv�OPK\u0003\u0004\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0006\u0000\u0000\u0000_rels/PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��'��\u0000\u0000\u0000+\u0001\u0000\u0000\u000B\u0000\u0000\u0000_rels/.rels��;\u000E�0\f\u0006��SD�i"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u0003B�i\u0017��\u0015�\u0003��}�i\u001C%\u0001ڳ1p$�@F@\f������GVL�fWt�'#`����(�{�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"8U��\u0016�\u000F��R�A\u00013z(�EvD-C�����E�x\u0001]\bvǹW\u001D��'d��NCn�!���V�A���i���݀��de-��u���-�cS��"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"��.#��c��D��k1\b�4��\u001B�DC\u0012Q�y�?\u001E�_PK\u0003\u0004\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0003\u0000\u0000\u0000xl/PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MSɍ-��\u0000\u0000\u0000#\u0001\u0000\u0000\u000F\u0000\u0000\u0000xl/workbook.xml��Mn�0\u0010��=�5{p`�P��M��\u001E֕\u001BO�K<\u0013�\u0018�r�.z�^�n)���O���>>��[\u001A�\u0005E#S\u000B�y\u0005\u0006�g\u001F���a����hv��Ȅ-��¦{h^YN��'S��-����Z�\u0003&�s���e`I.�Q�V'A�5 �4�eU�lr��F��?\f\u001E���#�焔o\u0010����^C�\u0014�����UC.\u0015�^#>]�/\u0001K���Η�`�������v��K�=]�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"PK\u0003\u0004\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\t\u0000\u0000\u0000xl/_rels/PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MSK�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"D�\u0000\u0000\u0000�\u0001\u0000\u0000\u001A\u0000\u0000\u0000xl/_rels/workbook.xml.rels��KN�0\f���\"�e�\u0010�t6\bi�0\u001C�Jܦ�6�l賱?�H\\�\b�G�Y�`eٖ?��_�6ۗe6O�2��kZ0�&#124;\u000ES\u001A\u001D<�oϯ��b"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"8�D\u000E�$���6w4��\u001B�S\u0011S!I\u001CD�rm��H\u000BJ�\u000B��\u00192/����\u0016�\u0007\u001C�^����\f�WL�\u000B\u000Ex\u0017���X�/�<\f����\u001F\u0017Jz�}�&#124;�H�\u0015�<�:�\u001E��"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u0000\u0000\u0018\u0000\u0000\u0000xl/worksheets/sheet1.xml��On�@\u0018��}O��'���\b�&�\t�\u0003 <�Q"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"X@��l]�H�B][mc�V&U[��c�f�\u000F<#��%���n�}\u0018Ʀ�X�&q\u0014��_5�c\u0011���&��q��U��Pğ�\u0018ߗo��~�0nB��C�n"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"�Z\u001Doj�B%�\u0013m�t��ݰ�F�^7ux��6tө�\u0010��tX��ivc\\��\u0019\u001E��*�.�:�����}��b_�>\u000B%��BM���R�(�\u0014�\u0014f�x&�I\"J�J\"K�K\"L�L\"M�M�M��B�B�B�B�B�B�B�B�F�F��\u0011m\u001Am\u001Am\u001Am\u001Am\u001Am\u001Am\u0006m\u0006m\u0006m�ih3h3h3h3h3h�h�h�h�h����͢͢͢͢͡͡͡͡͡��\u001E�6�6�6�6�6�6�6�6�6�6�\u0007\u0000�<��/�8�m�\u0003��\u0014;흼{����b'�\u0017�\u0014�W�+�c�S�s�'-�9��g\u0014��+��{~5=����s�I;���"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"���<�8�չ/\u001E��Q����f�bo\u0017/�Ȉ����oPK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MSP�i��\u0004\u0000\u0000S\u000E\u0000\u0000\u0014\u0000\u0000\u0000xl/sharedStrings.xml�W�R�6\u0014��S��Mٙ�8a�M:!;�I \f\u0010��Rz�\bK!�ؒ+ɀ{�������\u0017�+��J`k�\u001B�\u001D�&#124;�G�&#124;��ϟ�??�\t<0��\u0014�A{/\f��XR.�\u000F�\u001F�&��\u0000�!��D"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"v\u0018\u0014L\u0007�\u0007���6��B\u001F\u0006+c��Z-\u001D�XJ��̘@�R��\u0018�Wݷt�\u0018�zŘI�V'\f\u000FZ)�\"�\\�_s\u0016�\\�à�\t v��n0�k>��&#124;v};�\u0018��̠߲��pÈ��-��UOωR�Q��\u0004�p*�0I�\u0005�`\u0019Qİ������]\u0015~�W\u0002��\u0013�k��\t�9��x��s��ea\u0003^s*\u001F�W��\u0015"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"�2�^C�)M\u0018p�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"'I�㌬������\u00074�s�M\u0001\"O����̖������\u0017���.�EM�g�23����Я��(�\u0004\f)UL�u��=\u0010r�z\u001Ea��`�x\u000FRA&�\u001C�\\��/��\u0000��?�\f\"I��T��\u000B�AU�vx�ɔ&#124;�\"f��f�-]�F0\u0016���kr�ȕA�@�+�{W��\"B���:jؽ�}�!\u0016\u0015�K���1�{3^x͐)�\u001D��\b�\u0012�?@�\u0000�@�2\\Zʄ��O�>)\u0014�cn��&#124;\u001D�\u001B��ߘ\u0006)\u0010��[\u0005��\\���"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"SK�*��\t]���B�7���Q[B/�\u0000"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"�E��+d V�\u0016\f��������=�5��9ŋ��dp��\u001A�G��ժ�\u0015Z�G�n�C��C�7\u001Di�Q��8(�i�3��{��}�.Fyۭ�h�\u0013z;�H�%=D�Q�\u0011ޭ\u0001"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"�λ�\\��cd3O\\�l����a=�k�7a�\u0004<ob\u0019�c���m9��Λ]�a��\u0016�\u0017\u00024l��\\�u�N\b�\u0010p�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"F�\u001B\f�����Q�F@�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u000F�(�0���n�;�i���\u00024�t�\u0017��A��9���\u0018\u0012�^ƃ��V�\u0014�\u000F��2�_\u001B���:cYf��n�`��cL�\u001Aa�b��)"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"�˝}����B��~}�˝�o'\u0015[�5V\"\\���]��<�b�\u0019ᴡ��#�L����H\u0012�ӯ]=��*W�h�H@�g\tK�\u0010�S���l6\u001B\u001D\u000F���v�P������r6��Z.N�Z��/�ã���0��Z-�I8�\u0017PK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000\u0000\u0000\u0000\u0000[Content_Types].xmlPK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0006\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00000\u0000\u0000\u0000G\u0001\u0000\u0000_rels/PK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MS��'��\u0000\u0000\u0000+\u0001\u0000\u0000\u000B\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000k\u0001\u0000\u0000_rels/.relsPK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0003\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00000\u0000\u0000\u0000L\u0002\u0000\u0000xl/PK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MSɍ-��\u0000\u0000\u0000#\u0001\u0000\u0000\u000F\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000m\u0002\u0000\u0000xl/workbook.xmlPK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\t\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00000\u0000\u0000\u0000d\u0003\u0000\u0000xl/_rels/PK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MSK�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"D�\u0000\u0000\u0000�\u0001\u0000\u0000\u001A\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000�\u0003\u0000\u0000xl/_rels/workbook.xml.relsPK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u000E\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u00000\u0000\u0000\u0000�\u0004\u0000\u0000xl/worksheets/PK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MS�\u0019J�\u001A\u0002\u0000\u0000�"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u0000\u0000\u0018\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000�\u0004\u0000\u0000xl/worksheets/sheet1.xmlPK\u0001\u0002\u0014\u0000\u0014\u0000\u0000\u0000\b\u0000\t�MSP�i��\u0004\u0000\u0000S\u000E\u0000\u0000\u0014\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 \u0000\u0000\u0000\u0012\u0007\u0000\u0000xl/sharedStrings.xmlPK\u0005\u0006\u0000\u0000\u0000\u0000"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u0000"},{"PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000\t�MS��&#124;�\u0016\u0001\u0000\u0000�\u0002\u0000\u0000\u0013\u0000\u0000\u0000[Content_Types].xml�R;N\u00031\u0010�9��6���@\be��O\tH�\u0003\f�l�Z��qBr6":"\u0000_\u0002\u0000\u0000�\u000B\u0000\u0000\u0000\u0000"}]
6
  • 3
    Is it possible? Yes. Is it recommended? No. Is there a builtin way to do so? No. CSV parsing is hard enough to get right without the threat of heap limits and CPU limits. What's the context for what you're trying to do? If it's coming from the UI, I strongly recommend using JavaScript and something like PapaParse.
    – sfdcfox
    Commented Oct 13, 2021 at 19:07
  • @sfdcfox The CSV that would be coming back will have its size in KBs (under 50 KB) so heap limits shouldn't be an issue. The context is that I am making a callout to a third party API through the apex backend which gives me a csv BLOB. The CSV will have one column header and one value for each header (as given in the example). I need to take this csv and convert it into a simple JSON. There is no UI involved in this so JavaScript is out of scope. Commented Oct 13, 2021 at 20:25
  • 1
    Fair enough. I'll add an answer when I get a chance.
    – sfdcfox
    Commented Oct 13, 2021 at 20:57
  • @sfdcfox did you get time to look into this by any chance ? Commented Oct 14, 2021 at 18:41
  • I haven't forgotten, just busy. Stuff came up last night. And it's not a matter of "looking" so much as, "it's going to take significant effort, so be patient".
    – sfdcfox
    Commented Oct 14, 2021 at 19:01

1 Answer 1

6

Inspired by this code, fixed up by Daniel Ballinger, I wrote this version, which returns a list of Maps. It turns out that in order to have decent performance, you have to write it in a really complicated way. I actually wrote four or five different iterations to try and get decent performance, and the best one I came up with was still at least twice as slow as the one that just returns a list of lists. For completeness, I'm including it here, but I will say this: if you need to use this code, do try to run it asynchronously (e.g. Queueable).

I'm going to leave them here in order of best performance to worst; you can review these at your leisure. Note that these are all basically iterations of "state machines," which is how you're meant to properly parse a CSV. Ironically, some of the optimizations used in the code linked above made the code slower, instead of faster. Or, maybe I've been staring at this too long. Either way, here goes.


Version 1 (~2x slower)

public with sharing class CsvParser {
    Integer csvIndex = 0;
    Integer csvLineCounter = 0;
    Integer csvSize;
    String csvString;
    static final Integer END_LINE = '\r'.codePointAt(0);
    static final Integer END_CELL = ','.codePointAt(0);
    static final Integer ESCAPE_CHAR = '"'.codePointAt(0);
    static final Integer SPACE = ' '.codePointAt(0);
    
    public List<Map<String, String>> parseCsv(String csv) {
        // Normalize newlines, trim last newline, if any
        csvString = csv.replace('\r\n','\r').replace('\n','\r').removeEnd('\r');

        // Variables
        List<Map<String, String>> results = new List<Map<String, String>>();
        Map<String, String> valueMap;
        String[] headers;
        String[] values;
        
        // Intialization
        csvSize = csvString.length();
        headers = readLine();

        // Duplicate headers?
        if(new Set<String>(headers).size() != headers.size()) {
            throw new InvalidStateException('Duplicate headers detected');
        }

        Integer headersSize = headers.size();

        // Parse loop
        while(csvIndex < csvSize) {
            values = readLine();
            results.add(valueMap = new Map<String, String>());
            // unexpected number of columns
            if(values.size() != headers.size()) {
                throw new InvalidStateException('csv cells do not match header cells, expected '+headers.size()+', got '+values.size());
            }
            // map columns to keys
            for(Integer index = 0; index < headersSize; index++) {
                valueMap.put(headers[index], values[index]);
            }
        }
        return results;
    }
    String[] readLine() {
        String[] buffer = new String[0];
        Integer currentCharCode;
        while(csvIndex < csvSize && (currentCharCode = csvString.codePointAt(csvIndex)) != END_LINE) {
            if(buffer.size() > 0) {
                // Not the first cell in the row, should have a END_CELL separator here. I advise against disabling this check.
                if(currentCharCode != END_CELL) {
                    throw new InvalidStateException('expected a "'+END_CELL+'" on row '+csvLineCounter+', position '+ csvIndex +' but received "'+String.fromCharArray(new Integer[] { currentCharCode })+'"');
                }
                csvIndex++;
                // Some CSVs include an extra space. We'll ignore these.
                if(csvString.codePointAt(csvIndex) == SPACE) {
                    csvIndex++;
                }
            }
            // We handle quoted and unquoted values separately to avoid cognitive complexity in reading the cells
            buffer.add(String.fromCharArray(csvString.codePointAt(csvIndex) == ESCAPE_CHAR? readQuotedCell(): readUnquotedCell()));
        }
        csvLineCounter++;
        csvIndex++;
        return buffer;
    }
    Integer[] readQuotedCell() {
        Integer[] buffer = new Integer[0];
        Boolean quoteEscape = false;
        // Skips the first escape_char, it is not included in the field's value
        csvIndex++;
        while(csvIndex < csvSize) {
            Integer currentCharCode = csvString.codePointAt(csvIndex);
            // Expect the next pass to be a quoted character
            if(!quoteEscape && currentCharCode == ESCAPE_CHAR) {
                quoteEscape = true;
                csvIndex++;
                continue;
            }
            // A doublequote should be followed by one of three characters. However, some CSVs might break this rule. Disable this if you want.
            if(quoteEscape && currentCharCode != ESCAPE_CHAR && currentCharCode != END_LINE && currentCharCode != END_CELL) {
                throw new InvalidStateException('ESCAPE_CHAR must be followed by ESCAPE_CHAR, END_CELL, or END_LINE at normalized index: '+csvIndex+' got: "'+String.fromCharArray(new Integer[] { currentCharCode })+'"');
            }
            // End of cell only when a END_LINE or END_CELL is found when in escaped mode
            if(quoteEscape && (currentCharCode == END_LINE || currentCharCode == END_CELL)) {
                break;
            }
            quoteEscape = false;
            csvIndex++;
            buffer.add(currentCharCode);
        }
        return buffer;
    }

    Integer[] readUnquotedCell() {
        Integer[] buffer = new Integer[0];
        while(csvIndex < csvSize) {
            Integer currentCharCode = csvString.codePointAt(csvIndex);
            // Some CSVs might do this. Disable this if there are unquoted cells that contain ESCAPE_CHAR. Note that the first character cannot be a ESCAPE_CHAR anyways.
            if(currentCharCode == ESCAPE_CHAR) {
                throw new InvalidStateException('doublequote not allowed in unquoted cell at normalized index '+csvIndex);
            }
            if(currentCharCode == END_LINE || currentCharCode == END_CELL) {
                break;
            }
            csvIndex++;
            buffer.add(currentCharCode);
        }
        return buffer;
    }

    public class InvalidStateException extends Exception { }
}

Version 3 (~4.5x slower)

public class CsvParser3 {
    public enum STATE { START_CELL, START_LINE, QUOTED, UNQUOTED, END_LINE }
    public static List<Map<String, String>> parseCsv(String csv) {
        csv = csv.replace('\r\n','\r').replace('\n','\r').removeEnd('\r')+'\r';
        Integer csvLength = csv.length();
        Integer headerSize;
        List<Map<String, String>> results = new List<Map<String, String>>();
        Integer index = 0;
        STATE currentState = STATE.START_LINE;
        List<String> headers;
        List<String> values;
        while(index < csvLength) {
            switch on currentState {
                when START_LINE {
                    values = new List<String>();
                    currentState = STATE.START_CELL;
                }
                when START_CELL {
                    String leadCharacter = csv.substring(index, index + 1);
                    switch on leadCharacter {
                        when ' ' {
                            index++;
                        }
                        when '"' {
                            currentState = STATE.QUOTED;
                            index++;
                        }
                        when else {
                            currentState = STATE.UNQUOTED;
                        }
                    }
                }
                when QUOTED {
                    Integer nextQuote = csv.indexOf('"', index);
                    while(true) {
                        String charAfterQuote = csv.substring(nextQuote+1, nextQuote+2);
                        switch on charAfterQuote {
                            when '"' {
                                nextQuote = csv.indexOf('"', nextQuote+2);
                            }
                            when '\r',',' {
                                values.add(csv.substring(index, nextQuote).replace('""','"'));
                                currentState = charAfterQuote=='\r'?STATE.END_LINE: STATE.START_CELL;
                                index = nextQuote + 2;
                                break;
                            }
                            when else {
                                throw new InvalidStateException('Invalid escaped character');
                            }
                        }
                    }
                }
                when UNQUOTED {
                    Integer nextComma = csv.indexOf(',',index);
                    Integer nextNewline = csv.indexOf('\r',index);
                    Boolean isCommaNext = nextComma != -1 && nextComma < nextNewLine;
                    Integer nextIndex = isCommaNext? nextComma: nextNewLine;
                    currentState = isCommaNext? STATE.START_CELL: STATE.END_LINE;
                    String subString = csv.substring(index, nextIndex);
                    if(subString.indexOf('"') > -1) {
                        throw new InvalidStateException('Invalid quote in unquoted cell');
                    }
                    values.add(subString);
                    index = nextIndex + 1;
                }
                when END_LINE {
                    currentState = STATE.START_LINE;
                    if(headers == null) {
                        headers = values;
                        headerSize = headers.size();
                        if(new Set<String>(headers).size() != headerSize) {
                            throw new InvalidStateException('Duplicate headers detected');
                        }
                    } else {
                        if(values.size() == 0) {
                            continue;
                        } 
                        if(values.size() != headers.size()) {
                            throw new InvalidStateException('Cell count mismatch');
                        }
                        Map<String, String> tempValues = new Map<String, String>();
                        for(Integer headerIndex = 0; headerIndex < headerSize; headerIndex++) {
                            tempValues.put(headers[headerIndex], values[headerIndex]);
                        }
                        results.add(tempValues);
                    }
                }
            }
        }
        return results;
    }
    public class InvalidStateException extends Exception { }
}

Version 2 (~7x slower)

public class CsvParser2 {
    enum STATE { START_LINE, START_CELL, QUOTED, QUOTED_ESCAPED, UNQUOTED }
    public static List<Map<String, String>> parseCsv(String csv) {
        // Normalize newlines
        csv = csv.replace('\r\n','\n').replace('\n','\r').removeEnd('\r')+'\r';

        Boolean headerRow = true;
        Boolean newLine = true;
        Integer size = csv.length();
        Integer headerRowSize;
        STATE currentState = STATE.START_LINE;
        List<Map<String, String>> results = new List<Map<String, String>>();
        List<String> headers;
        List<String> values;
        Integer[] buffer;
        Map<String, String> result = new Map<String, String>();

        for(Integer index = 0; index < size; ) {
            Integer currentCharacter = csv.codePointAt(index);
            switch on currentState {
                when START_LINE {
                    values = new List<String>();
                    currentState = STATE.START_CELL;
                }
                when START_CELL {
                    switch on currentCharacter {
                        when 32 { // ' ': Skip leading spaces
                            index++;
                        }
                        when 34 { // '"': Quoted string
                            buffer = new Integer[0];
                            currentState = STATE.QUOTED;
                            index++;
                        }
                        when else { // Unquoted string
                            buffer = new Integer[0];
                            currentState = STATE.UNQUOTED;
                        }
                    }
                }
                when UNQUOTED {
                    switch on currentCharacter {
                        when 34 { // '"' not allowed in unquoted cell
                            throw new InvalidStateException('Invalid character in unquoted cell');
                        }
                        when 44 { // ',' end of cell
                            values.add(String.fromCharArray(buffer));
                            buffer.clear();
                            currentState = STATE.START_CELL;
                        }
                        when 13 { // '\r' end of line
                            values.add(String.fromCharArray(buffer));
                            buffer.clear();
                            currentState = STATE.START_LINE;
                            if(headerRow) {
                                headers = values;
                                headerRowSize = values.size();
                                headerRow = false;
                            } else {
                                Map<String, String> cellMap = new Map<String, String>();
                                if(values.size() > 0 && values.size() != headerRowSize) {
                                    throw new InvalidStateException('Cell count does not match header cell count');
                                }
                                for(Integer cellIndex = 0; cellIndex < headerRowSize; cellIndex++) {
                                    cellMap.put(headers[cellIndex], values[cellIndex]);
                                }
                                results.add(cellMap);
                            }
                        }
                        when else {
                            buffer.add(currentCharacter);
                        }
                    }
                    index++;
                }
                when QUOTED {
                    switch on currentCharacter {
                        when 34 { // '"' escape sequence
                            currentState = STATE.QUOTED_ESCAPED;
                        }
                        when else {
                            buffer.add(currentCharacter);
                        }
                    }
                    index++;
                }
                when QUOTED_ESCAPED {
                    switch on currentCharacter {
                        when 34 { // '"' adds a literal doublequote
                            buffer.add(currentCharacter);
                            currentState = STATE.QUOTED;
                            index++;
                        }
                        when 13, 44 { // end of cell or end of line, either way, we're done with quoted field.
                            // Loop back to use unquoted logic
                            currentState = STATE.UNQUOTED;
                        }
                        when else { // We don't permit other escaped quoted characters, per RFC 4180
                            throw new InvalidStateException('Escaped character must be followed by quote, comma, or newline');
                        }
                    }
                }
            }
        }

        return results;
    }
    public class InvalidStateException extends Exception { }
}

Honestly, Version 3 surprised me the most. I though switch statements were supposed to make things faster, but it was significantly slower than baseline.

At any rate, this code gets you as far as:

[{"ROW_INDEX": "1", "Year": "2020", "Professional": "1"}]

Given you do something like:

httpResponse res = new http().send(req);
String csv = res.getBody();
String jsonOutput = JSON.serialize(new CsvParser().parse(csv));

You'd get similar performance using the link from above, plus the time it takes to map and serialize the data, as you would from CsvParser, I believe. It takes over half a second to parse 10,000 simple CSV rows with two columns. For 40k rows, as you suggest, it could easily take tens of seconds to parse. It'd literally be faster to call a Heroku web service to parse into JSON.


Edit: for anyone who's curious, I received the sample file by email, and it was actually an Excel Open Office XML file (basically ZIP + XML). The moral of this story is to make sure one is actually reading a CSV before trying to read the data.

7
  • thank you so much for the answer. There seems to be a problem with the json that is getting formed. I am adding the response json to the question itself as the json is too long to be contained in the comments. There are a lot of unicode characters and no readable text in it. However when I store the response csv as a content version and then view it in salesforce then it seems to be working perfectly fine. Commented Oct 20, 2021 at 6:19
  • @KnightoftheVale This code is working fine in my org. I would need to see a sample. If you have a way to get it to me, I can do some more offline research for you.
    – sfdcfox
    Commented Oct 20, 2021 at 10:32
  • sure, please let me know what all you need and I will send it across Commented Oct 20, 2021 at 14:51
  • @KnightoftheVale Send me a copy of the CSV file as it's saved in Salesforce, the code you use to retrieve this file, and the JSON you're seeing (saved as a separate file), if you can, and I'll take a look for you. My email's on my profile.
    – sfdcfox
    Commented Oct 20, 2021 at 15:29
  • sorry, unable to find your email on the profile page Commented Oct 20, 2021 at 19:56

You must log in to answer this question.

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