Importing Data into a MySQL Database Using LOAD DATA - Simple Talk (2023)

Database and development teams often load data from plain text files into MySQL databases. Files can be used to add search data, support test and development environments, populate new MySQL instances, load data from regular streams, or otherwise support their functionality. To support the import process, MySQL providesBURDEN DATAStatement that reads lines from a text file and inserts them into the target array.

In this article, I'll show you how to use itBURDEN DATAInstruction for adding data from CSV (Comma-Separated Value) files and other plain text files. Although the examples are fairly simple, they show the key elements that go into oneBURDEN DATAExplanation and some of the problems you may encounter along the way. Each example retrieves and appends data from a file on the local systemManufacturerBord iTravelDatabase as you have seen in previous articles in this series.

Note: The examples in this article are based on a local instance of MySQL hosting a very simple database and table. The final section of the article - "Appendix: Preparing the MySQL Environment" - provides information on setting up my system and includes a SQL script to create the database and table on which the examples are based.

Connects to the MySQL server

In itself, importing data from a text file into a MySQL database is a fairly simple process. Often the hardest part about running is setting up your environment to make sure you can run oneBURDEN DATAstatement and insert the data into the target table. As with any SQL statement in MySQL, you must be granted the necessary permissions to perform your operations (a topic beyond the scope of this article). However, there are some other points you need to be aware of when importing dataLOCALCapability.

If you create oneBURDEN DATAStatement you can includeLOCALOption as part of statement definition. The option determines the security requirements of the statement and whether the source file resides on the client system or on the server hosting the MySQL instance:

• If you don't specifyLOCALoption, the source file must reside on the MySQL host. when you driveBURDEN DATAstatement, MySQL reads the file directly from the library and inserts the data into the target table. This approach generally works a little better than if you include itLOCALOption because the data loads faster. But getting the connection right is much more difficult (and the subject of much online discussion).
• If you specifyLOCALoption, the source file must reside on the client computer. The client reads the file and sends its contents to the server, where it is stored in a temporary file until loaded into the target table for processing. TheLOCALThe option also works when the client and MySQL are on the same machine, which is the approach I took for this article. In use, the connection is usually much easier to establishLOCALCapability.

For the examples in this article I usedLOCALCapability. Without them, MySQL connection requirements are not only more complex, but also not well documented, increasing frustration when you run into errors. If you look at the various forum posts that discuss connectivity issuesBURDEN DATAIn many cases, you'll find that people who reply to a post suggest using that statementLOCALchoice as a simple solution to the various challenges.

I also believe that for many database administrators and developers, locating the source files on the client side is preferable to uploading those files to the MySQL server, if they are allowed to do so at all. If you useLOCALChoice, you don't have toThe FILE privilegedrive oneBURDEN DATAstatement and you can save the source file in any local directory accessible to the client application, in this case MySQL Workbench.

Note: The MySQL documentation states: "ifLOCALis defined, the file must reside on the client host.” However, I was able to run oneBURDEN DATAstatement that containLOCALoption and pulled data from other systems on my network. The first was another Mac, and the second was the Windows 11 virtual machine. Furthermore, I did not test this function.

UseLOCALMake sure data loading is enabled on both client and server side. To enable it on the Workbench client side, change your login on the tool's home screen. Right-click the link in the main window and clickedit connection. In additionConnectionhis sideManage server connectionsdialog box for selectionProgressiveTab and add the following commandAndreBooks:

The command defineslocale-infilability toAN, making it possible to run aBURDEN DATAstatement that includesLOCALCapability. The image below shows the setup (in red) as it appears on the linkProgressivethe tab. This setting applies only to that user's workbench logins. Other connections must be configured separately.

In addition to activationlocale-infilYou must also activate this optionlocal_infilglobal variable on the server if not already activated. (The only difference between these two names is that the global variable uses an underscore instead of a hyphen.) To confirm the variable's setting, you can execute aPROJECTION global VARIABLESStatement against your MySQL instance:

The If statement returns a value ofAN, then you are ready. When the statement returnsVON, and then run the followingSERIEInstruction to activate the variable:

After you've enabled local data loading on both the client and server, you should be ready to run yoursBURDEN DATAOpinion. The following examples show various aspects of importing data from a text file. I'll show you the contents of each file as we work through the examples. You can then build them on your own system if you want to try the examples yourself.

Introduction ofLOAD THE DATENotice

Before we get into the first example, it's important to understand the basic elements of aBURDEN DATAStatement containing a number of clauses and sub-clauses. The following syntax simplifies the statement somewhat to give you an overview of the main elements of the sentence and their context:

TheBURDEN DATAIn the clause indicate if you want to includeLOCALCapability. As I mentioned earlier, this is the approach I took in this article. The next clauseINFIL, specifies the path and filename (in quotes) of the source file. You can specify an absolute or a relative path. If relative, the path is relative to the calling library.

You can then specify bothERSATZDieTO IGNORE, both are optional. TheERSATZThe option tells MySQL to replace existing rows that have the same unique key value. TheTO IGNOREThe option tells MySQL to ignore rows with the same key value. TheTO IGNOREThe option has the same effect asLOCALoption, so if you useLOCAL, you never have to use itTO IGNORE. However, you can use itERSATZchoice withLOCAL.

TheINDONESIAN BORDThe term specifies the name of the target table. The most important thing is to make sure you have the necessary permissions to add data to this table.

TheFELDERIt follows the clause and supports one or more of the following three clauses:

• TheYOU ARE DETERMINED WITHThe subclause specifies the character string used to terminate each field in the text file. The string can consist of one or more characters. The default is\ Tfor tabs, meaning that tabs are used to separate field values.
• TheCLOSED WITHThe sub-clause specifies the character used in the text file to enclose values, e.g. B. Quotation marks around string values. TheOPTIONALKeyword, which is itself optional, is used according to the MySQL documentation "when input values ​​are not necessarily quoted". (More on that in a moment.) The default value forCLOSED WITHThe subclause is an empty string indicating that the fields are not quoted.
• TheFLEE WITHThe subclause specifies the character used as an escape character in the text file, which might affect how MySQL interprets the data. The default is a backslash (\), which is also used in MySQL to escape characters, including the backslash itself. Many programming languages ​​also use backslashes to escape characters.

TheFELDERThe clause itself is optional, but if you include it, you must specify at least one of the clauses.

Notice thatOPTIONALOption iCLOSED WITHThe subjunctive is one of its most confusing elementsBURDEN DATANotice. Usage made no difference in the various tests I ran. For example, in a test I closed all valuesManufacturerEnclose fields except for one in double quotes. MySQL imported the data correctly whether I included it or notOPTIONALCapability. I also tried the use optionNULLvalues ​​and empty strings and got the same results. There may be instances where choice makes a difference, but I haven't discovered it yet. HoweverFELDERAndlinesclauses iBURDEN DATAstatement is the same asCHOOSE...ON EXTRACTstatement and much of the discussion in the MySQL documentation about itOPTIONALthe choice is related to itCHOOSE...ON EXTRACT, so maybe it's more relevant there.

Just likeFELDERclause, hlinesThe clause is also optional. ThelinesThe term supports the following two clauses:

• TheSTARTER WITHThe term indicates the common prefix used at the beginning of each line in the text file. The default is an empty string indicating that no specific prefix is ​​used. If a prefix is ​​specified and a row does not contain that prefix, MySQL skips the row when inserting the data.
• TheYOU ARE DETERMINED WITHThe condition specifies the string used to end each line in the text file. The string can consist of one or more characters. The default is\N, which refers to a newline character. I created my text file in Apple's TextEdit application, so the default setting worked on my system, but not all systems work the same. For example, if the text files are created in Windows, you may need to specify'\r\n'ifYOU ARE DETERMINED WITHWert.

If you include boatsFELDERclause andlinesclause, hFELDERThe clause must come first. TheTO IGNORE N linesThe clause comes after these two clauses. TheTO IGNORE N linesThe condition specifies the number of lines to skip when importing data from the beginning of the file. The clause is usually used when the file contains a header, so the clause is written as followsTO IGNORE 1 lines.

The last sentence is the list of columns enclosed in parentheses and separated by commas. Although this clause is optional, you will probably want to include it in most of your statements unless your source data contains a field for each column and the fields are in the same order as the columns.

TheBURDEN DATAThere are a few more clauses in the statement, but the ones I've shown you here are enough to get you started. However, I recommend you to read the MySQL topicLOAD DATA statementto learn more about the different elements of the statement.

Imports a CSV file

Now that you've introduced yourselfBURDEN DATALet's look at some examples that show them in action. You can refer back to the previous section as needed as you work through the following sections.

In preparation for the first example, I created a file namedManufacturer1.csvand added the following elements:

I saved the file in the folder/Users/mac3/Documents/TravelData/on my local computer. If you want to try the samples yourself, you can save the files anywhere on your system that Workbench can access. However, be sure to update the file path in the examples before executing your instructions.

After I createManufacturer1.csvfile I ran the followingBURDEN DATAStatement that saves the dataManufacturerBord iTravelDatabase:

As you can see, that's how it isBURDEN DATAthe clause includesLOCALpossibility, andINFILThe clause specifies the source file. These followINDONESIAN BORDClause relating toManufacturerBord.

The next clauseFELDER, containsYOU ARE DETERMINED WITHclause specifying that a comma be used as the field separator instead of the standard tab. The statement then specifies the names of the two target columns:Vendor_IDAndManufacturer- what is in brackets.

When you execute the statement, MySQL extracts and fills in the data from the fileManufacturerTable. You can verify that the data has been added to the table by running the followingCHOOSENotice:

TheCHOOSEThe statement returns the results shown in the following figure, indicating that the data has been inserted into the table. Have this statement handy, because you can use it to validate your results for the remaining examples.

To simplify this article, you can also do the followingSHORTStatement from which data is to be removedManufacturerTable to prepare for the next example:

You should also have this declaration to hand. You will want to do this after most of the following examples, except in a few cases where I introduce specific concepts, so I advise you not to do so.

Ignore the first few lines of an input file

Some of the source files you are working with may contain a header that displays field names or contains other types of information, such as B. Comments about when and where the file was created. You can skip these lines when entering data by including themTO IGNORE N linesclause in yourBURDEN DATANotice.

To see how this works, create a text file namedManufacturer2.csvFile, add the following data to the file and save it in the same location asManufacturer1.csvFile:

Now run the followingBURDEN DATAStatement containing:TO IGNORE 1 linesClause telling MySQL to skip the first row:

After you performBURDEN DATAinstruction, you can run your againCHOOSEStatement to confirm that the correct data has been added. The results should indicate that the header was received. Then you can run your ownSHORTstatement again in preparation for the next example.

TheTO IGNORE N linesThe clause is not limited to one line. For example the followingTO IGNORE N linesThe term denotes five lines instead of one:

when you driveCHOOSEThis time you should get the results shown in the image below. (Don't crop the table for this or the next example, as I'll point out some other issues.)

As you can see, the array only contains the last three lines from the source file. Suppose you needed to run the statement again, but this time specifying only one lineTO IGNORE N linesClause:

When you run the statement, MySQL tries to insert all seven rows of data into the target table, but only the first four rows succeed. After executing the statement, MySQL returns the following message:

4 lines affected, 3 warnings: 1062 Duplicate entry "105" for important "manufacturers". MAIN" 1062 Duplicate entry "106" for main manufacturer. MAIN' 1062 Duplicate entry '107' for key 'Manufacturer'. PRIMARY Entries: 7 Deleted: 0 Skipped: 3 Warnings: 3

The announcement states that the existing ones are sufficientVendor_IDhis values105,106, And107was omitted. This means that no new rows with these values ​​were inserted into the table. Only the first four lines have been added. when you driveCHOOSEAgain, you should get results similar to the image below. (Again, don't truncate the table, leave it for the next example.)

The table now contains all seven rows of data. However, if you look closely at the timestamps in the figure, you'll see that the last three lines are almost 30 seconds ahead of the first five lines. (I ran the last twoBURDEN DATAStatements that are close enough to each other.)

Now let's assume you drive the same thingBURDEN DATAinstruction again, only this time insert itERSATZCapability:

When you run the statement, MySQL now returns the following message:

14 rows affected Entries: 7 Deleted: 7 Ignored: 0 Warnings: 0

The message indicates that 14 rows were processed. But only seven records were affected and seven were deleted. This means that the database engine deleted the seven existing records and added them back to the table. You can verify that this is runningCHOOSEstatement again. Your results should have different timestamps than the previous results, with all values ​​very close if not identical.

Now you can drive your own carSHORT BORDto create an opinionManufacturertable for the next example.

Work with quoted fields in the input file

When you import data, your text files may contain some or all of the fields that are in quotes. I created for exampleVendor3.csvFile with the following data, containing single quotes around the string values:

To process the listed fields, you can add aCLOSED WITHsubparagraph to yourFELDERclause, as shown in the following example:

TheCLOSED WITHSpecifies that a single quote is used to enclose fields. The quotation mark is preceded by a backslash to escape the character when passed to the database engine. When you don't use itCLOSED WITHclause, the database engine treats the quotes as literal values ​​and stores them with the other values.

After you performBURDEN DATAInstruction you can run your ownCHOOSEstatement to confirm the results, and then run your ownSHORTto create an opinionManufacturertable for the next example.

If you put a single quote in thatCLOSED WITHclause, you can enclose them in double quotes instead of escaping them with a backslash:

In some cases, the text file uses double quotes to enclose field values ​​instead of single quotes. To demonstrate how to handle this, I createdManufacturer4.csvFile with the following data:

To deal with this file, you mustCLOSED WITHThe clause needs to be modified to show a double quote by enclosing it in single quotes:

After you run thisBURDEN DATAinstruction, you can run your againCHOOSEInstruction to check the results. Once you've checked them out, you can run your ownSHORTInstruction to prepare for the next example. (You should do this for all remaining examples.)

Work with different formats in your text files

The text files you are working with may be delimited by tables rather than commas, and may contain other items that require special handling. ThinkHersteller5.txtFile I created with the following data:

In this case, a tab is used as the field separator and each line precedes it*,*to mark. Therefore no information is requiredYOU ARE DETERMINED WITHsubparagraph iFELDERclause since tab is the default, but you must take action to handle the row prefix. To do this, you need to add onelinesclause with aSTARTER WITHClause specifying the prefix characters:

When you run this statement, MySQL uses the prefix characters to determine which rows to add, removing the characters as it does so.

As already mentioned, the previous example does not contain oneYOU ARE DETERMINED WITHsubparagraph iFELDERClause. It also doesn't contain anyYOU ARE DETERMINED WITHsubparagraph ilinesTerm because the text file uses the default newline value. However, you can also include both clauses if necessary:

UseSTARTER WITHNote that your text file must use these prefixes consistently, otherwise unexpected results may occur. For example the following text file,Hersteller6.txt, contains a line with two entries but no prefix before the first entry:

Once you have created the file on your system, you can run the followingBURDEN DATAInstruction to see what happens:

When you execute this statement, MySQL skips the record containing aVendor_IDits value104but it still adds the record with its value105. You can confirm this by running it againCHOOSEStatement that returns the results shown in the following figure.

In some cases, you may encounter text files whose lines end with unconventional characters (as opposed to normal linefeeds or newlines). I created for exampleHersteller7.txtFile with the following data, separating the lines with triple hash marks (###):

In order for this file to fit, you must include oneYOU ARE DETERMINED WITHsubparagraph to yourlinesClause specifying hashes:

When you execute this statement, the database engine knows how to interpret the hash marks and imports the data accordingly, removing the hash marks in the process.

In some cases, you may also encounter a text file that uses a character other than the backslash to escape characters in fields. E.gHersteller8.txtThe file contains seven lines of comma-separated fields, one of which contains a comma in the constructor name:

In this case, the comma in the name is removed by a hyphen (^). Since this character is not a backslash (simple escape character), you must add oneFLEE WITHpoint-defining clause, as shown in the following example:

If you don't includeFLEE WITHclause, the database engine keeps the design but abbreviates the vendor name, as inbald airplanes^. However, when you include the clause, MySQL strips the hyphen and treats the comma as a literal value, resulting in a column valuebald Fly, Inc., instead of the shortened version.

Start importing data into MySQL

As already mentioned, it is soBURDEN DATAThe statement contains different information than what I have shown you here. There are also other ways to enter data, such asmysqlimportCommand line tool that creates and sendsBURDEN DATAInstructions on the MySQL server. Most of the features of the tool are directly related toBURDEN DATANotice. Another possibility isBord Data introductionthe wizard in MySQL Workbench. The wizard guides you through the process of importing data from a CSV or JSON file.

If you work regularly with MySQL databases, chances are you'll want to import data from text files, even if it's just for setting up test or development environments. In most cases, what I've shown you here will be enough to get you startedBURDEN DATANotice. However, note that you may encounter situations that I have not covered. So it's always a good idea to refer to other MySQL documentation to fill in the gaps.

Appendix: Preparing the MySQL Environment

When creating the examples for this article, I used a Mac computer configured with a local instance of MySQL 8.0.29 (Community Server Edition). I also used MySQL Workbench to interface with MySQL. The examples import data from a set of sample text files that I created in Apple's text editor, TextEdit.

I give you the contents of the files throughout the article along with the exampleBURDEN DATAOpinion. If you want to try these samples, you can create the files on your own system while working with these samples. However, before you begin, you should run the following script against your MySQL instance:

The script createdTraveldatabase and addsManufacturerTable. Otherwise, that's all you need to try out the examples (other than building the source files). For most of the examples, I simply truncated the data to prepare the table for the next example. If you've already created the database and table for previous articles, I recommend recreating them now, or at least trimming themManufacturertable before you begin.