Import the contents of a text file (TXT) into Excel and SQL

These two days on the network circulated 14w 12306 account information txt document, downloaded and found in the TXT document and query content is not very convenient.

Here I am documenting the process of importing TXT documents into Excel and SQL.

Import to Excel

1. Create a new Excel and select from text in the data option of the Ribbon:

2. Text Conversion Wizard for Excel:

Step 1: Use the default options

Step 2: Select the delimiter, the delimiter in TXT is---, but only one "-" symbol can be entered here

Step 3:

This is the end of importing from TXT to Excel, but because the delimiter we enter is a single "-", there are a lot of extra columns in Excel, and the last to delete:

Import into SQL import into SQL preprocessing

1. If we look closely at the ID of an Excel column, we find that Excel defaults to setting this column as a number format, and because the numbers are too large, they are also shown as scientific notation.

But some IDs have the last character "X", so this column should be converted to a text type, or the "X" in the process of importing SQL will become a null value.

The conversion process is as follows:

In the last item of the Conversion Wizard, select the following column format:

2. In the first column in Excel, add the header, which is later imported into SQL to act as the corresponding SQL table field role.

Formally Import SQL

1. Select Import Data:

2. Select the data source for Excel:

3. Select the worksheet to import into Excel and set the table name to import the database:

4. Import the Mapping settings for SQL fields and Excel columns to remove some empty Excel columns

5. View the results of the run

6. Finally, you can write the SQL statement query directly:

Summarize

Import into SQL and Excel can also be implemented in code, but the existing tool implementation is quite handy, the last benefit map:

Import the contents of a text file (TXT) into Excel and SQL