Import data into a MySQL database using LOAD DATA - Simple Talk (2023)

Database and development teams often load data from plain text files into their MySQL databases. The files can be used to aggregate search data, support test and development environments, populate new MySQL instances, load data from regular sources, or otherwise support your operations. To help with the import process, MySQL providesCHARGE DATAstatement that reads rows from a text file and inserts them into the destination table.

In this article I show you how to useCHARGE DATAstatement to add data from comma-separated value (CSV) files and other plain text files. Although the examples are fairly basic, they demonstrate the basic components that go into aCHARGE DATAstatement and some of the problems you may encounter along the way. Each example retrieves data from a file on the local system and adds the data tomanufacturersshould ijourneydatabase 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 Your MySQL Environment," provides information on how I set up my system and includes an SQL script to create the database and table on which the examples are based.

Connects to the MySQL server

Importing data from a text file into a MySQL database is itself a fairly straightforward process. Often the hardest part of the operation is setting up your environment to ensure that it allows you to run oneCHARGE DATAstatement and import the data into the destination table. As with any SQL statement in MySQL, it must have been granted the necessary privileges to perform its operations (a topic outside the scope of this article). However, there are a few other issues you need to be aware of when importing data, starting withLOCALpossibility.

When you create aCHARGE DATAstatement, may includeLOCALoption as part of the statement definition. The option determines the security requirements of the statement, as well as whether the source text file is located on the client system or on the server hosting the MySQL instance:

• If you don't specifyLOCALoption, the source text file must be located on the MySQL server. when you driveCHARGE DATAstatement, MySQL reads the file directly from the library and inserts the data into the destination table. This approach usually works a bit better than when you includeLOCALbecause the data is loaded more directly. But getting the connection right is much more difficult (and the subject of many online discussions).
• if you specifyLOCALoption, the source text file must be located on the client machine. The client reads the file and sends its content to the server, where it is stored in a temporary file until it is loaded into the destination table for processing. ThatLOCALThe option also works if the client and MySQL are on the same machine, which is the approach I've taken for this article. The connection is usually much easier to establish when usingLOCALpossibility.

For the examples in this article I have usedLOCALpossibility. MySQL's connection requirements are not only more complicated without it, but they are also not well documented, which adds to the frustration in case of errors. If you check out the various forum posts that discuss connection issues aroundCHARGE DATAstatement, you will find that in many cases people responding to a post suggest the use ofLOCALoption as a simple solution to various challenges.

I also believe that for many database administrators and developers, placing source files on the client side is preferable to uploading those files to the MySQL server, if they have permission to do so at all. if you are usingLOCALoption, you don't have toFILE privilegedrive oneCHARGE DATAstatement and you can save the source text file to any local directory accessible by the client application, which in this case is MySQL Workbench.

Note: The MySQL documentation says that "ifLOCALis specified, the file must be located on the client's host." However, I was able to run aCHARGE DATAstatement that includedLOCALoption and pulled data from other systems on my network. The first was another Mac computer and the second was a Windows 11 virtual machine. I haven't tested this ability beyond that.

UsingLOCALoption, make sure that data loading is enabled on both client side and server side. To enable it on the Workbench client side, change your connection on the home screen of the tool. Right-click on the connection in the main window and click onedit connection. In thatConnectionside ofManage server connectionsdialog box to selectAdvancedtab and add the following command in itandrébooks:

The command setslocal-infilpossibility ofIN, which allows you to run aCHARGE DATAstatement that includesLOCALpossibility. The following figure shows the configuration (marked in red) as it appears in the connectionAdvancedthe board. This setting only applies to this user's connections in the Workbench. Other connections must be configured individually.

In addition to enablinglocal-infiloption, you must also activatelocal_infilglobal variable on the server if it is not already enabled. (The only difference between these two names is that the global variable uses an underscore instead of a hyphen.) To confirm the variable setting, you can run aSHOW GLOBAL VARIABLESstatement against your MySQL instance:

If the statement returns a value ofIN, Then you're ready. If the statement returnsOF, then run the followingTO PLACEinstruction to activate the variable:

Once you have enabled local data loading on both the client and the server, you should be ready to run yourCHARGE DATAstatement. The following examples demonstrate 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 create them on your own system if you want to test the examples yourself.

Introduction ofLOAD DATAannouncement

Before going into the first example, it is important to understand the basic components that go into aCHARGE DATAdeclaration, which includes a series of clauses and subclauses. The following syntax simplifies the declaration a bit to give you an overview of the essential elements of the declaration and how they fit together:

HeCHARGE DATAclause is where you tell if you want to includeLOCALpossibility. As I mentioned earlier, this is the approach I've taken in this article. The next clause,INFIL, specifies the path and filename (in quotes) of the source text file. You can specify an absolute path or a relative path. If it is relative, the path is relative to the calling library.

You can then specifySUBSTITUTEoIGNORE, both optional. HeSUBSTITUTEThe option tells MySQL to replace existing rows that have the same unique key value. ThatIGNOREoption tells MySQL to ignore rows with the same key value. ThatIGNOREoption has the same effect asLOCALoption, so if you useLOCAL, you never need to useIGNORE. However, you can useSUBSTITUTEoption withLOCAL.

HeIND I EDGEclause specifies the name of the destination table. The most important thing here is to make sure that you have been granted the necessary privileges to add data to that table.

HeCAMPOScomes next and admits one or more of the following three clauses:

• HeDESTINED BYThe subclause specifies the string used in the text file to end each field. The string can be one or more characters. The default is\tfor tabs, which means that tabs are used to separate field values.
• HeCERRADO BYThe subclause specifies the character used in the text file to enclose values, such as quotes around string values. ThatOPTIONALThe keyword, which itself is optional, is used "if the input values ​​are not necessarily in quotes", according to the MySQL documentation. (More on that in a bit.) The default value forCERRADO BYsubclause is an empty string indicating that the fields are not enclosed in quotes.
• HeESCAPE BYThe subclause specifies the character used in the text file to escape characters that might affect the way MySQL interprets the data. The default is a backslash (\), which is also used in MySQL to escape characters, including the backslash. Many programming languages ​​also use backslashes to escape characters.

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

note theOPTIONALoption meCERRADO BYThe subjunctive is one of the most confusing elements ofCHARGE DATAadvertisement. Its use made no difference in the various tests I ran. For example, in a test, I enclosed all the values ​​inmanufacturerfields in double quotes except one. MySQL imported the data correctly whether you include it or notOPTIONALpossibility. I also tried the option usingNULLvalues ​​and empty strings and received the same results. There may be cases where the option makes a difference, but I have yet to figure them out. HoweverCAMPOSyLINESclauses iCHARGE DATAstatement are the same asCHOOSE... ON EXTRACTstatement, and much of the discussion in the MySQL documentation aboutOPTIONALoption is related toCHOOSE... ON EXTRACT, so maybe that's where it's most relevant.

LikeCAMPOSclause, theLINESThe clause is also optional. ThatLINESclause supports the following two clauses:

• HeSTART BYThe clause specifies 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 line does not contain that prefix, MySQL skips the line when importing the data.
• HeDESTINED BYThe clause specifies the string used in the text file to end each line. The string can be one or more characters. The default is\norte, which refers to a line feed character. I created my text file in Apple's TextEdit app, so the default worked on my system, but not all systems work the same. Yep. creates the text files in Windows, you may need to specify'\r\n'asDESTINED BYvalor.

If you include boatsCAMPOSclause andLINESclause, theCAMPOSThe clause must come first. ThatIGNORE norte LINESThe clause comes after these two clauses. ThatIGNORE norte LINESThe clause specifies the number of lines to skip at the beginning of the file when importing data. The clause is commonly used when the file contains a header row, in which case the clause will be written asIGNORE 1 LINES.

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

HeCHARGE DATAthe declaration contains a few other clauses, but the ones I've shown you here are enough to get you started. Still, I recommend you check out the MySQL topicData upload declarationfor more information on the different elements of the return.

Import a CSV file

Now that you've been introducedCHARGE DATAstatement, let's look at some examples that show it in action. You can refer back to the previous section if necessary as you work on the following sections.

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

I saved the file in the folder./Users/mac3/Documents/TravelData/on my local computer. If you plan to try the examples yourself, you can save the files to any location on your system that Workbench can access. Just make sure you update the file path in the examples before running your statements.

after i createdfabricantes1.csvfile, I ran the followingCHARGE DATAinstruction that stores the data formanufacturersshould ijourneydatabase:

As you can see, it isCHARGE DATAthe clause includesLOCALpossibility, andINFILclause specifies the source file. These are followed byIND I EDGEclause that points tomanufacturersboard

The next clause,CAMPOS, includesDESTINED BYclause that specifies that a comma is used as the field separator instead of the default tab. The declaration then provides the names of the two target columns:manufacturer_idymanufacturer- which is placed between parentheses.

When you execute the statement, MySQL extracts the data from the file and populates itmanufacturerstable. You can verify that the data has been added to the table by running the followingCHOOSEannouncement:

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

To keep things simple for this article, you can also run the followingTRUNCATEstatement to remove data frommanufacturerstable in preparation for the following example:

You should also have this statement handy. You'll want to run it after most of the following examples, except in a few cases where I'm demonstrating specific concepts, in which case I'll tell you not to run it.

Ignore the first few lines of an import file

Some of the source files that you work with may contain a header row that shows the names of the fields, or contain other types of information, such as comments about when and where the file was generated. You can skip these rows when importing the data by includingIGNORE norte LINESclause in hisCHARGE DATAannouncement.

To see how this works, create a text file namedfabricantes2.csvfile, add the following data to the file and save it in the same location asfabricantes1.csvfill:

Now run the followingCHARGE DATAstatement that includes aIGNORE 1 LINESclause that tells MySQL to skip the first row:

After having madeCHARGE DATAstatement, you can rerun yourCHOOSEto confirm that the correct data has been added. The results should indicate that the header row has been skipped. You can then run yourTRUNCATEstatement again in preparation for the next example.

HeIGNORE norte LINESclause is not limited to one row. For example the followingIGNORE norte LINESclause specifies five rows instead of one:

when you driveCHOOSEthis time, you should get the results shown in the following figure. (Don't truncate the table for this example or the next one, because I'll point out some other problems.)

As you can see, the table only contains the last three rows of the source file. However, suppose you were to execute the statement again, only this time specifying only one row.IGNORE norte LINESclause:

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

4 rows affected, 3 warnings: 1062 Duplicate entry '105' for key 'manufacturers.PRIMARY' 1062 Duplicate entry '106' for key 'manufacturers.PRIMARY' 1062 Duplicate entry '107' for key 'manufacturer. PRIMARY' Records: 7 Deleted: 0 Skipped: 3 Warnings: 3

The ad states that the existing ones will sufficemanufacturer_idvalues ​​of105,106, y107was omitted. This means that no new rows with these values ​​were inserted into the table. Only the first four rows were added. if you driveCHOOSEagain, you should receive results similar to those shown in the following figure. (Again, don't truncate the table; leave it for the next example.)

The table now contains all seven rows of data, but if you look closely at the timestamps in the figure, you'll see that the last three rows lead the first five rows by almost 30 seconds. (I ran the last twoCHARGE DATAstatements close together).

Now suppose you drive the sameCHARGE DATAstatement again, only this time it includesSUBSTITUTEpossibility:

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

14 rows affected Records: 7 Deleted: 7 Skipped: 0 Warnings: 0

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

Now you can drive yoursTRUNCATE EDGEstatement to preparemanufacturerstable for the following example.

Working with quoted fields in the import file

When you import data, your text files may contain some or all of the fields enclosed in quotes. For example, I createdfabricantes3.csvfile using the following data, including single quotes around the string values:

To handle quoted fields, you can add aCERRADO BYsubclause to suCAMPOSclause, as shown in the following example:

HeCERRADO BYsubclause indicates that a single quote is used to enclose fields. Quotes are preceded by a backslash to escape the character when it is sent to the database engine. if you don't useCERRADO BYclause, the database engine will treat the quotes as literal values ​​and store them with the rest of the values.

After having madeCHARGE DATAstatement, you can run yourCHOOSEstatement to confirm the results and then run yourTRUNCATEstatement to preparemanufacturerstable for the following example.

When you enter a single quote in theCERRADO BYclause, you can enclose it in double quotes instead of escaping it with a backslash:

In some cases, the text file will use double quotes to enclose field values ​​instead of single quotes. To demonstrate how to handle this, I createdfabricantes4.csvfile using the following data:

To handle this file, you mustCERRADO BYThe clause must be changed to indicate a double quote by enclosing it in single quotes:

After running thisCHARGE DATAstatement, you can rerun yourCHOOSEstatement to verify the results. Once you've reviewed them, you can run your ownTRUNCATEstatement in preparation for the next example. (You must do this for all the remaining examples.)

Work with different formats in your text files

The text files you are working with may be table-delimited rather than comma-separated, and may contain other elements that require special handling. Considerfabricante5.txtfile I created with the following data:

In this case, a tab is used as the field separator and each line is preceded by*,*sign. As a result, you do not need to specifyDESTINED BYsubclause iCAMPOSclause because tab is the default, but you need to take steps to handle the line prefix. To this must be added aLINESclause with aSTART BYclause specifying the prefix characters:

When you run this statement, MySQL will use the prefix characters to determine which rows to add, removing the characters in the process.

As already mentioned, the example above does not include aDESTINED BYsubclause iCAMPOSclause. Nor does it include oneDESTINED BYsubclause iLINESbecause the text file uses the default line feed value. However, you can still include both clauses if you want:

UsingSTART BYclause, note that your text file must use these prefixes consistently, or you may get unexpected results. For example, the following text file,fabricante6.txt, includes 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 followingCHARGE DATAsentence to see what happens:

When you execute this statement, MySQL skips the record with amanufacturer_idValue of104but still adds the record with a value of105. You can confirm this by running your newCHOOSEstatement 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 the usual line breaks or returns). For example, I createdfabricante7.txtfile using the following data, which separates the lines with triple marks (###):

To accommodate this file, you must include aDESTINED BYsubclause in yourLINESclause that specifies hashmarks:

When you execute this statement, the database engine will know how to interpret the hash marks and will insert 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 backslash to escape characters in fields. E.gfabricante8.txtThe file contains seven lines of comma-separated fields, one of which contains a comma in the manufacturer's name:

In this case, the comma in the name is escaped with a hyphen (^). Because this character is not a backslash (standard escape character), you must add oneESCAPE BYclause that specifies the point, as shown in the following example:

if not includeESCAPE BYclause, the database engine retains the drawing but abbreviates the manufacturer's name, as inpronto planes^. However, if you include the clause, MySQL will remove the hyphen and treat the comma as a literal value, resulting in a column value ofpronto volar, C., instead of the truncated version.

Start importing data into MySQL

As mentioned above it isCHARGE DATAthe declaration contains different elements than the ones I've shown you here. There are also other options for importing data, such asmysqlimportcommand line tool that generates and sendsCHARGE DATAstatements to the MySQL server. Most of the tool's capabilities directly correlate toCHARGE DATAadvertisement. Another possibility isshould Data Matterthe wizard in MySQL Workbench. The wizard guides you through the process of importing data from a CSV or JSON file.

If you regularly work with MySQL databases, you'll probably want to import data from text files, even if it's just to set up test or development environments. In most cases, what I've shown you here will be enough to get you started.CHARGE DATAadvertisement. Just know that you may run into situations that I haven't covered, in which case it's always a good idea to check out other MySQL documentation to help fill in the gaps.

Appendix: Preparing your MySQL environment

When I created the samples 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 interact 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 content of the files throughout the article along with the example.CHARGE DATAstatement. If you plan to try these samples, you can create the files on your own system while you work through these samples. However, before you begin, you must run the following script on your MySQL instance:

The script createsjourneydatabase and additionsmanufacturerstable. Otherwise, that's all you need to test the examples (other than creating the source text files). For most of the examples, I simply truncate the data to prepare the table for the next example. If you have already created the database and table for previous articles, I recommend that you recreate them now or at least truncate them.manufacturerstable before starting.