Export column as string in CSV

anujeet

Hi,

I want to export large numbers in CSV with string format and not as numbers.
I know the solution works with .txt extension but I want to keep the extension as .csv.

I was able to implement this .xlsx but customize function but in case of CSV, customize just returns row data and nothing else.

Has anyone ever exported a column as string in CSV?

kthorngren

Looks like CSV export of large numbers works in this example:
http://live.datatables.net/cuwazidu/1/edit

Maybe you can update the example or provide a test case so we can see what you are doing.

Kevin

anujeet

No @kthorngren. In your mentioned example, the CSV shows the number with exponential power. What I am trying to achieve is to export the exact value as a string so that, the exponential formatting doesn't work.

Please look at this screenshot, that I exported from your example:

anujeet

Please check this JS BIn link to see how I am doing it for Excel

http://live.datatables.net/cuwazidu/3/edit

colin

Hi @anujeet ,

This thread should help, it's asking the same thing.

Cheers,

Colin

kthorngren

A CSV file is a flat text file without any formatting, etc. Its not really an Excel file, although your OS may default to opening it in Excel when clicked on. This is what it looks like when opening in a text editor:

"Name","Number"
"Ashton Cox","2"
"Cedric Kelly","900719925474099111423122331"
"Garrett Winters","900719925474099111"
"Tiger Nixon","9007199254740991"

Which is the expected output.

To properly open in Excel you will need to use Data > Get External Data > Import Text File option (may be different depending on Excel version). Walking through that process you can mark columns as text so the import as you want.

Kevin

anujeet

Hi. Thanks, I was able to resolve the issue in CSV by below code:

    exportOptions: {
                        columns: ':visible',
                        charSet: "utf-8",
                        format: {
                            body: function ( data, row, column, node ) {                       

                                var attrClass1 = node.className;
                                if (attrClass1.indexOf("fuelCardNumberColumn") >= 0) {
                                    data = data +"\t";
                                }

                                return data;
                            }
                        },
                    }

anujeet

But I am facing an issue with my Excel export. When I open the exported xlsx file in Microsoft Excel, I get the below error on opening:

Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

Does anyone else faced this issue?
I see some threads regarding this, but none of those are working. I have used below code to customize cell format in xlsx:

extend: 'excelHtml5',
                exportOptions: {
                    columns: ':visible',
                    format: {
                    body: function ( data, row, column, node ) {                       

                            var attrClass1 = node.className;
                            if (attrClass1.indexOf("fuelCardNumberColumn") >= 0) {  

                                fuelCardNumberColumn = colName(column);
                            }

                            return exportDataAsCustomizedValues(column, data, 'xls', node);
                        }
                    },
                },
                customize: function( xlsx ) {

                    if (fuelCardNumberColumn != '') {

                        fuelCardNumberColumn = fuelCardNumberColumn.toUpperCase();
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('row c[r^="'+fuelCardNumberColumn+'"]:gt(0)', sheet).attr( 't', 'inlineStr' );  //add :gt(0) to skip column title
                        $('row c[r^="'+fuelCardNumberColumn+'"]:gt(0)', sheet).attr( 's', '67' );  //apply for new style
                        $('row c[r^="'+fuelCardNumberColumn+'"]:gt(0)', sheet).append('<is><t xml:space="preserve"></t></is>');
                        $('row c[r^="'+fuelCardNumberColumn+'"]:gt(0) > is > t', sheet).each(function () {
                            $(this).text($(this).parent().siblings("v").text());  //paste text to new node
                            $(this).parent().siblings("v").remove();  //remove useless v node
                        });
                    }
                }