How Do You Stop Auto Formatting to a Cell I'm Using as a Barcode Number

Question

I have just started using the SearchBuilder. Before that, I was using Moment.js (https://cdn.jsdelivr.net/momentjs/latest/moment.min.js) and DateTime Moment (https://cdn.datatables.net/plug-ins/1.10.24/sorting/datetime-moment.js).

When using the Searchbuilder for dates, I am getting the following error: Uncaught TypeError: t is not a function.

Is any solution available?

Link to test case: https://jsfiddle.net/jL7pt01w/1/

My code:

$.fn.dataTable.moment('DD.MM.YYYY, HH:mm');
            var table = $('#' + panel.id).find('#operatorLogs').DataTable({
                responsive: true,
                fixedHeader: false,
                lengthChange: false,
                stateSave: false,
                searchHighlight: true,
                searchBuilder: true,
                autoWidth: true,
                buttons: [{
                        text: '<i data-tooltip="true" title="განახლება" class="fas fa-sync-alt"></i>',
                        className: 'btn-success',
                        action: function(e, dt, node, config) {
                            table.state.clear();
                            update_operator_logs_personal(panel.id);
                        }
                    },
                    {
                        text: '<i data-tooltip="true" title="ბეჭვდა" class="fas fa-print"></i>',
                        className: 'btn-success',
                        extend: 'print',
                        title: 'printed_best_shop',
                        exportOptions: {
                            columns: ':visible:not(.not-export-col)'
                        }
                    },
                    {
                        text: '<i data-tooltip="true" title="excel ფორმატი" class="fas fa-file-excel"></i>',
                        className: 'btn-success',
                        title: 'excel_best_shop',
                        extend: 'csv',
                        charset: 'UTF-16LE',
                        bom: true,
                        exportOptions: {
                            columns: ':visible:not(.not-export-col)'
                        }
                    },
                    {
                        text: '<i data-tooltip="true" title="დაკოპირება" class="fas fa-copy"></i>',
                        className: 'btn-success',
                        title: 'copied_best_shop',
                        extend: 'copy',
                        exportOptions: {
                            columns: ':visible:not(.not-export-col)'
                        }
                    },
                    {
                        text: '<i data-tooltip="true" title="სვეტების გაფილტვრა" class="fas fa-eye"></i>',
                        className: 'btn-success',
                        title: 'filtered_best_shop',
                        extend: 'colvis',
                        columns: ':not(.noVis)'
                    }
                ],
                order: [
                    [2, 'desc'],
                    [2, 'asc']
                ],
                aoColumnDefs: [{
                    orderable: false,
                    aTargets: [0, 1]
                }],

                

            var info = table.page.info();
            var count = table.rows().count();

            $('#' + panel.id).find('.count-rows').html(count);

            table.buttons().container().appendTo($('#' + panel.id).find('#operatorLogs_wrapper .col-sm-6:eq(0)'));
            
            table.searchBuilder.container().prependTo(table.table().container());
            
            
            table.columns.adjust();

The sequence of js files is as follows:

<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script> <script src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap.min.js"></script> <script src="https://cdn.datatables.net/autofill/2.3.7/js/dataTables.autoFill.min.js"></script> <script src="https://cdn.datatables.net/autofill/2.3.7/js/autoFill.bootstrap.min.js"></script> <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script> <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.bootstrap.min.js"></script> <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.colVis.min.js"></script> <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script> <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script> <script src="https://cdn.datatables.net/colreorder/1.5.4/js/dataTables.colReorder.min.js"></script> <script src="https://cdn.datatables.net/fixedcolumns/3.3.3/js/dataTables.fixedColumns.min.js"></script> <script src="https://cdn.datatables.net/fixedheader/3.1.9/js/dataTables.fixedHeader.min.js"></script> <script src="https://cdn.datatables.net/keytable/2.6.2/js/dataTables.keyTable.min.js"></script> <script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script> <script src="https://cdn.datatables.net/responsive/2.2.9/js/responsive.bootstrap.min.js"></script> <script src="https://cdn.datatables.net/rowgroup/1.1.3/js/dataTables.rowGroup.min.js"></script> <script src="https://cdn.datatables.net/rowreorder/1.2.8/js/dataTables.rowReorder.min.js"></script> <script src="https://cdn.datatables.net/scroller/2.0.4/js/dataTables.scroller.min.js"></script> <script src="https://cdn.datatables.net/searchbuilder/1.1.0/js/dataTables.searchBuilder.min.js"></script> <script src="https://cdn.datatables.net/searchbuilder/1.1.0/js/searchBuilder.bootstrap.min.js"></script> <script src="https://cdn.datatables.net/searchpanes/1.3.0/js/dataTables.searchPanes.min.js"></script> <script src="https://cdn.datatables.net/select/1.3.3/js/dataTables.select.min.js"></script> <script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script> <script src="//cdn.datatables.net/plug-ins/1.10.24/sorting/datetime-moment.js" type="text/javascript"></script> <script src="https://cdn.datatables.net/searchbuilder/1.1.0/js/dataTables.searchBuilder.min.js" type="text/javascript"></script> <script src="https://cdn.datatables.net/searchbuilder/1.1.0/js/searchBuilder.bootstrap.min.js" type="text/javascript"></script> <script src="https://cdn.datatables.net/datetime/1.1.0/js/dataTables.dateTime.min.js" type="text/javascript"></script> <script src="https://cdn.datatables.net/plug-ins/1.10.25/features/searchHighlight/dataTables.searchHighlight.min.js"></script>

Solution 1

The answer is no! This is the behaviour by design I'm afraid to say! Source

There may be some work arounds using templates. See the comments as I've marked it as a duplicate.

I will also point out, that it may not actually do what you think it's doing (or what it's showing). When Excel loads the CSV, it formats it to the scientific notation, however, that's just the formatting, the actual value is untouched. To test it, load the CSV in notepad and see.

Solution 2

Select the entire column in your excel sheet that contains the barcode number. Right-click the column-header and choos 'Format cells'. In the dialog that apperars, choose Text. Excel should now never reformat the contents.

You do need to perform this action everytime you open your CSV in Excel.

Comments

[Floyd]

The file is originally saved as a .xls format. I then save it as a CSV for my work or to export to MySQL through PHPMyAdmin.

[Dave]

And during which part of the process is the format changing? Your comments says XLS -> CSV -> MySQL. I'm not sure when you're reloading it in Excel. Or are you saying that the formatted number (5.02825E+12) is what is being generated when saving to CSV? Can you please just open the CSV with notepad to ensure it's not just the way Excel is displaying it?

[Jonathan Scion]

have you tried changing the format cell to text before entering barcode data

[Floyd]

Part 1, create the xls with number. Part 2, convert the sheet to CSV. Part 3, Upload to MySQL.

[Dave]

Andrew, after converting to a CSV file, just test it in notepad and see if what value is exported.

[Floyd]

5028252188661 is shown in Notepad++

[Floyd]

I'm going to assume at the moment MySQL however I am just testing a new method changing the data type to text for barcode.

[Floyd]

Dave Rook - Regardless of the motives once I have the file as a CSV, this question is still relevant. I have taken fully into account the advice given and have only mentioned my intentions for after the file is converted to CSV and answered what people have asked.

[Dave]

Then, the answer is, the value you're seeing is only how Excel formats it!The value itself is actually the full number, not the notation (as per my post).

[omatai]

Upvoting since this question is more descriptive and more readily found than "how to turn off scientific notation" as far as I'm concerned. I would have called it "exponent format"

[Floyd]

This is correct, however when exporting to a database, the column is reverted back and is displayed on the database as 5.02825E+12.

[R-D]

What kind of database, what are the properties for the barcode column in the database? It sounds more like a problem in the database or the client application than with Excel.

[Floyd]

varchar 255, will change to text.

[R-D]

Varchar 255 should suffice, maybe PHP is treating it as a number instead of text?

[Floyd]

Roland Van Dorn - There is a solution to this that I have just found, save the sheet as a OpenDocument Spreadsheet (.ods). Then export through PHPMyAdmin. This will create a new sheet and you can name your MySQL columns through your spreadsheet.