The reason is that today, my colleague took a 1G CSV file and asked me to help import it into the database. In line with the principle of being helpful, I agreed. I thought it was very simple, is not graphical operation oh, in a minute to do it, however, sloppy…

Problems encountered

  • Start importing. So let’s open up the structure, the first step is a problem, one G is too big to open notepad, right? Online search let me find a good editor EmEditor (with the decompression version download link and registration code at the end)
  • First import. All error, output a series of garbled code, coding problem this simple, change the code to continue
  • Second import. Half report error and half report no error. What the hell is this, looking through the logs and looking through the Internet, uh, none of them. Through my exhaustive trial-and-error method, do not report an error, the original or wrong coding, so in the import of the code must be chosen!!
  • Third import. Didn’t expect a third time, ha ha, NEITHER did I. It takes an hour to navigate through the data, but everything else is fine, just a few date fields, all 1920-01-01, if it’s empty it’s 0001-01-01. What is this stuff?

Solution Steps and precautions

After several hours of exploration, all the above problems have been solved one by one. Since there is no relevant document on the Internet, I hereby make a record, so that I can refer back to it in the future when I encounter problems. I also hope to provide some help to my friends who encounter the same problem.

The first step

First select the table you need to import, which can be automatically created without it, right click -> Import Wizard, and select CSV file

The second step

Select the CSV file you need to import, and determine the encoding of your file, which is necessary for you to import successfully. If you have to use the exhaustive method, the commonly used methods are UTF-8, GBK, GB2312 and so on

The third step

On Windows, you don’t need to do this. Default is fine. On Linux and MAC, you need to select the corresponding separator

The fourth step

This is very important, especially if you have a date field in your file.

  • The default for date sorting is DMY but generally we should choose YMD, which stands for year, month and day
  • The date separator is whatever the format of the date in your file is, usually a / – and so on, okay
  • A time delimiter is a time delimiter that contains the hour and second of a date.

Step 5

Select the target table, I this is an existing table, you can also select new table, and then in the target table you create the name of the table

Step 6

The next step is to set the mapping between the fields in the source table and the target table

Step 7

Next, choose a pattern based on your needsHere is a suggested setting, click Advanced, check use NULL instead of blank string, this is to solve my date file is empty string, but import has become 0001-01-01

The last step

All set up, start your import, if the end is like this, congratulations on your successful import!

conclusion

The following is a summary: first pay attention to the encoding of the file, and then pay attention to the date format when there is a date field. Finally, it is recommended to select null instead of blank string

In fact, solving problems is not complicated, just need to start to do, to think, not afraid of trial and error, learn to summarize. May we eventually become what we want to be.

Attached CSV download path editor: link: pan.baidu.com/s/1w9HN1D8E… Extraction code: B9B9