Concatenate SQL data using EXCEL

Recently I have encountered such a situation that I want to import some online data (I am too lazy to COPY data one by one, and there is no ready-to-use import method). I just want to use SQL statements to import data, powerful EXCEL Functions

1. The data we want to import is often relatively simple, but it is annoying to read more data.

2. If you want to import data, there is no ready-made method. If you want to import data, it will be easy to program the SQL statement. But it is troublesome (you can't change it every time. Of course you can make complicated configurations, but it is still easy to use in EXCEL)

3. EXCEL is simple and practical.

 

My example is as follows:

1. For example, we can obtain a certain method of data to be imported (separated by commas (,), such as classification (Category 1, Category 2, category 3)

2. We need to insert it into the corresponding table, but no ready-made tools are used.

3. We may need to set default values for other fields.

If you need this, you can obtain any SQL statement (Update, insert, or delete) by following these steps)

1. Copy data to a cell in EXCEL

2. Use the EXCEL Partition Function to select a proper separator and separate it (very practical)

3. Select an empty column and paste it selectively. Select transpose to convert the data into multiple rows.

4. You can convert multiple data using the preceding method. Note that the same SQL statement requires one-to-one data conversion.

5. Select the first row of the data as the standard and start to flatten the data,

6. On other cells of the row, you can enter multiple SQL parts to meet the splicing and changing needs. For example, you can use the EXCEL numbering function to automatically add numbers.

7. on other empty cells in the row, call the function to splice the data in the above Cells in order. The function is = CONCATENATE (A1, B1, C1), A1, B1, c1 indicates cells, and the quantity can be changed.

8. Use EXCEL's drag-and-drop copy function (select the cell, + down in the lower right corner, and the formula will automatically change according to the cell). This will not be self-taught.

9. set identical or changed data for other custom EXCEL cells in the same way

10. in this case, you can see the desired SQL string in the calculated cell, but it cannot be copied and used. Copy this column to another column and paste it with the optional values, the SQL statement that can be used is obtained.

 

In fact, you can also get text in other formats. Because the search replacement cannot achieve the conversion of this statement, it is quite simple to use EXCEL. If you like to be lazy, you can try it.

Attachment: DEMO