CSV is a comma separator file, and each column of data uses a comma as the separator. It has strong versatility, and can be processed by Notepad, EXCEL, various text processing tools, or databases. I once encountered such a problem. I imported the CSV file data into the database for processing. The CSV file separator is not a comma, but two characters | |. In order to solve this problem, I made some attempts and successfully imported the database. These methods are now recorded for future use.
1、 Characteristics of the CSV raw data
The CSV data contacted is exported from other databases. Each column uses a double character | | as the separator. The first line is the title. However, there should be a problem with the export settings. For example, the empty column does not have any marks, that is, the | | | separator is connected twice, which is not standardized.
Therefore, the first attempt was to open the data with EXCEL. When looking at the overall situation first, if feasible, the EXCEL data was directly imported into the database. This method failed. When separating EXCEL data into columns, there is the option "continuous delimiters are treated as a single". Since there are no marks in empty columns of data, it is obviously not feasible to treat continuous delimiters as a single. Instead, 4, 6, 8 consecutive delimiters will be treated as one.
2、 Replace double delimiters with single characters
Later, we tried a new method to replace double characters with single characters, so that there would be no problem in importing directly into the database. It is planned to replace the delimiter with a comma. First, query the entire CSV file for no comma. After replacement, the data will completely become a comma delimiter file. This method succeeds.
3、 Create table structure before importing
The database uses MariaDb, and the client is HeidiSql that comes with the system. When importing CSV data, it can automatically identify the title and data. In fact, when I tried the above method, I thought about importing the database directly, and entering | | as the separator. But somehow, there were always errors. If you use the above method, you can change the data into a comma separator file, and then directly import it into the database. There is no problem.
However, if the data volume is too large to be replaced with common single characters, how to solve it? So we still need to find an ultimate solution. After an attempt, first create a table structure in the database, then import the CSV data, and enter the separator | |. The import is successful. Note that the previous attempt to import CSV directly to automatically identify the title failed. The reason for this success is that the table structure was established in advance.