WEX5 actual combat heidisql Import Excel data

A preface

I've never done anything big before, and suddenly the client says, I'm giving you a data, you're deploying to the cloud. I think, very simple, just say yes.

Tragedy happened, the guest sent me, incredibly is an Excel table!!!

Originally want a piece of data copy, a look, more than 2000!!

Online search Excel Generate SQL, say what macro editing, write Java statement, have a half-day, always not. According to the text on the Internet step by step, are not. Perhaps I understand the mistake, perhaps the person who writes the tutorial to write the discrepancy. Anyway,

It's gotta be done.

Finally find methods, check data, test, finally found the method.

Two-effect Demo:

More than 2000 data, successfully imported successful.

Three implementation methods:

1 Modifying an Excel file

This is the data and cannot be imported directly.

You must add a column ID column and drag to generate the sequence number. Avoid self-destructive data from the ID column in the database.

2 Generating a CSV file in Excel

(note) The name cannot be a Chinese character. Otherwise, the import will prompt that the file does not exist. Save the prompt, and click Yes directly.

3 Creating a new table in Heidisql

(note) field names and order, to correspond to data one by one in Excel, several fields to build several fields.

4 Importing CSV files in Heidisql

5 Modifying Import parameters

A total of 4 places to note:

1 ignore the number of rows, according to the data in Excel, which do not need, to select

2 Comma separated. When you generate a CSV file, the hints are comma-delimited, separated by columns and columns, and fields in the database. If the heidisql default is a semicolon, you want to modify it manually. Otherwise, only the first column can be generated.

3 data conflicts, whether updated, see your database requirements

4 You can choose to insert data fields. Fields can be many, but not less, and fewer will prompt for an unsuccessful insertion.

Finally, click to import, the lovely thousands of data finally came into the accurate.

In the future can be bold to write data in Excel, will be or Excel operation easy.

WEX5 actual combat heidisql Import Excel data