使用phpMyAdmin将使用长数字的电子表格导入MySQL的最佳方法

Question

I have recently been collecting data for my company to create a MySQL database. In doing this using PHP it can be used to view, add, edit and delete using a webpage interface.

I have currently created a prototype that allows me to import this data by converting a .XLS or XLSX sheet into a .CSV format that is then imported through PHPMyAdmin. Only recently I have found a snag with using this method. In my spreadsheet I have a few columns that represent barcodes, these barcodes vary in size, some going over 15 digits long. Excel has a limit to what it will represent and unfortunately displays the following:

This can obviously be changed by formatting the excel column to number, text etc.. however when saved as a CSV format it becomes impossible to change as every time you close the document it reverts back to it's original state.

When viewing the CSV file in Notepad++, the barcode will show fine although when I import the file into PHPMyAdmin, the barcode will show in the same way as the CSV.

So my question splits into two parts:

1. What is the best format to import my spreadsheet on?
2. How do I resolve this annoying format.

Answer 1

I'd say your data in the spreadsheet is stored incorrectly; it should be treated as text rather than a number. Likewise in the database. That will handle your question #2 and prevent this from becoming a problem in your database. It's not really a numeric representation (for instance a count of anything); it's a string of identifying characters that all happen to be digits. That seems to me like it should be represented as a string column type such as CHAR or VARCHAR.

I believe you said that the CSV looks fine (as in 5028252188661), so if you set the MySQL table to expect a string rather than a numeric type you should see the correct value represented after you import as well.