Solution to an exception in Excel import SQL due to format

I had a hard time importing SQL into Excel.

I had a hard time importing SQL into Excel.

Originally, the program runs normally. Sometimes some exceptions may occur because the user's excel document is not in the required format. I thought the same was true this time. After reading the data, I did not find any exceptions, and I did some data on my own. In fact, exceptions also occurred, I had to suspect that my program had a problem.

The most difficult to find is the last error, which is really good. I have used almost all the means I have mastered, and I can't find out what's wrong with the program. I have been tossing for a long time on a problem. It is a waste of time and easy to bring myself down. Later, I switched to suspect that there was a problem with the user's data. I accidentally adjusted the format and found that the exception was gone.

The cell format settings in Excel are rich. Once the data is formatted, you may not see it. For example, in the currency format, data is only a string of numbers and does not contain currency symbols.

In my program, a column of data is encoded, which may be a combination of letters and numbers, or a pure number. Normally, it should be set to text format, in this case, a pure number error is prompted, that is, a yellow exclamation point appears next to the cell. If the user thinks it is incorrect, it is set to a special format such as zip code, the data does not seem to change, but the program encountered an exception during identification. Another field is the month. The program requires that 0 be added before a single digit. If the format is set to a number, the previous 0 is gone, so it must be set to text. If the custom value is: 00, although the number displayed in the excel file is the same as the Expected One, the program processes the data as a number and the results do not meet the requirements.

To sum up, do not format the excel Data to be imported to the database. It is the best choice to retain the original character or number format.