Как открыть csv в sql

Импорт CSV-файла в таблицу MySQL

В этой статье мы расскажем вам, как использовать оператор LOAD DATA INFILE для импорта CSV-файла в таблицу MySQL.

Оператор LOAD DATA INFILE позволяет считывать данные из текстового файла и очень быстро импортировать их в таблицу базы данных.

Перед импортом файла, вам необходимо подготовить следующее:

• Таблицу базы данных, в которую будут импортированы данные из файла;
• CSV-файл с соответствующим числом столбцов и соответствующим форматом данных в каждом столбце;
• Учетную запись пользователя, который подключается к серверу базы данных MySQL и имеет привилегии FILE и INSERT .

Предположим, что мы имеем таблицу, которая называется discounts со следующей структурой:

Мы используем оператор CREATE TABLE , чтобы создать таблицу discounts :

Файл discounts.csv в первой строке содержит заголовки столбцов, в трех других строках — данные:

Следующий оператор импортирует данные из файла c:tmpdiscounts.csv в таблицу discounts :

Поля файла завершаются запятой, относящейся к FIELD TERMINATED BY ‘,’ , которая заключена в двойные кавычки, предусмотренные форматом ENCLOSED BY ‘»‘ .

Каждая строка CSV файла завершается символом новой строки, обозначающим TERMINATED BY ‘n’ .

Поскольку первая строка файла содержит заголовки столбцов, которые не должны быть импортированы в таблицу, мы игнорируем ее, указав опцию IGNORE 1 ROWS .

Теперь мы можем проверить, импортированы ли данные в таблицу discounts :

Преобразование данных при импорте

Иногда формат данных не соответствует целевым столбцам таблицы. В простых случаях, вы можете преобразовать их с помощью условия SET в операторе LOAD DATA INFILE .

Предположим, что столбец данных срока действия скидок в файле discount_2.csv имеет формат мм / дд / гггг:

При импорте данных в таблицу discounts мы должны преобразовать их в формат даты MySQL с помощью функции str_to_date() :

Импорт файла клиента для замещения на сервере базы данных MySQL

Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE .

При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или / TMP для Linux .

Эта папка не настраивается и не задается MySQL.

Давайте рассмотрим следующий пример:

Разница заключается только в опции LOCAL оператора. Если вы загружаете большой CSV файл, вы увидите, что с опцией LOCAL загрузка осуществляется немного медленнее, потому что требуется определенное время, чтобы передать файл серверу базы данных.

Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE , если используется опция LOCAL .

При импорте файла от клиента на удаленный сервер базы данных с помощью LOAD DATA LOCAL могут возникнуть некоторые проблемы с безопасностью , о которых вы должны знать, чтобы избежать потенциальных рисков.

Импорт CSV файла с помощью MySQL Workbench

MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.

3 Easy Ways to Import CSV File to SQL Server

Today, there are easier ways to import CSV to SQL Server. You can code less or use graphical tools. You can even get the CSV from cloud storage to your on-premise SQL Server. This post will focus on how you can do it in 3 easy ways.

Table of contents

Why to Import CSV to SQL Server

I read about comma-separated-values, or CSV, in Wikipedia. Can you believe that CSV was supported back in 1972? For what reason?

To exchange data between 2 or more systems of different platforms and architectures. Today, the reason is still the same. Imagine you have 2 proprietary systems. One is using a proprietary NoSQL database like PayPal’s. And the other is using SQL Server. It’s like a Japanese talking to an Estonian. Understanding is almost impossible. What is the solution you will ask?

Export a CSV file from the NoSQL database. Then, import the CSV file to SQL Server. Rather than invent new software, exchange a mature and common file format. That will be easier and less time-consuming.

Here are common scenarios when CSV can be useful:

• Customers pay a service company through a bank. Then, the bank provides payment records to the service company using a CSV file.
• An operational system built in-house needs to be integrated into an ERP system.
• Several SharePoint lists need to be synced to a SQL Server database for data analysis.
• A biometrics system needs to be integrated into a human resources system for attendance purposes.

Though this can be done using JSON or XML, CSV is simpler to generate from the source. If the data requirement is non-hierarchical, CSV can be a good fit. What about the target system using SQL Server? It is rated 4.5 out of 5 in Gartner Peer Insights. SQL Server in the cloud or Azure SQL is also one of the top 3 DBMS of 2020. So, learning to import CSV to SQL Server is worth the effort. You will ask how to do it. The answer is described in detail below.

How to Import CSV File in SQL Server (3 Easy Ways)

Before we start, we need a sample CSV and a target table in SQL Server. Check it out in the screenshot below.

This will be used to upload to SQL Server using 3 of the different ways to import CSV. So, download a copy of the actor.csv file from here. Remember where you saved it. You’ll need it later in this tutorial.

Meanwhile, the target table is structured the same in SQL Server.

In this tutorial, I’m using the server name MS-SQLSERVER. And the database name is CSV-MSSQL-TEST.

Using BULK INSERT

BULK INSERT is a command in SQL Server to import data files into a database table. It can be used to upload various file formats, including CSV. If you love a little coding, this is the way to go. Here’s the code on how to bulk insert CSV into SQL Server:

The first command simply deletes all the records in the target table. Then, the BULK INSERT command includes the target table and the CSV file. The location of the CSV should follow the rules of the Universal Naming Convention (UNC). You also must tell SQL Server what file it’s dealing with. In this case, FORMAT=CSV because the file is in CSV format. Finally, specify what row the data starts. FIRSTROW = 2 because the first row contains the column names.

You can issue a BULK INSERT command from SQL Server Management Studio or any other SQL Server tool. Another way to do it is in PowerShell.

PROS AND CONS OF USING BULK INSERT

Pros:

• If you know the syntax, typing can be faster than using a GUI interface;
• No need to parse the data. BULK INSERT does it for you. If the target column uses a data type too small for the data, an error will occur;
• Scheduling of execution possible in SQL Server Agent;
• Also great for a one-time import job.

Cons:

• You cannot specify a CSV from cloud storage like Google Drive or OneDrive;
• Allows only SQL Server as the target database;
• Requires a technical person to code, run, and monitor.

Using SQL Server Management Studio Import CSV Tools

If you don’t prefer coding, another useful tool is the Import Data from SQL Server Management Studio (SSMS). Here’s how to import CSV to MSSQL table using SSMS.

Step 1. From the Object Explorer, Expand the Databases Folder

First, you need to go to Object Explorer and select the target database. Expand the Databases folder. Check it out below.

Step 2. Select the Target Database

In this example, the target database is CSV-MSSQL-TEST. Right-click that database and then select Tasks. And then, click Import Data.

The SQL Server Import and Export Wizard window will appear with a welcome screen. From here, click Next.

Step 3. Select a Flat File Source

This part will let you pick the data source. For this, click the drop-down list and select Flat File Source. Afterwards, click Next.

Step 4. Specify the CSV File

Now, you need to specify the file. Click Browse and specify the path of the CSV file as shown in the screenshot. If you have downloaded it, specify the download location. Then, in the file type, select CSV files (*.csv). And afterwards – actor.csv. Proceed further by clicking Open.

Step 5. Configure the Columns

Can’t SSMS detect the correct data types for each column? In our sample data, detection of the correct types and sizes is off. So, we need to set it up ourselves.

Pro tip: Whenever you import data from one platform to another, it’s best to match the correct types and sizes. Why? To avoid errors and headaches.

Let’s start by clicking Advanced. Then, you will see a list of columns. Click each and set the type and size.

Based on the data types and sizes, refer to table below on what to set for each column.

Step 7. Specify the Database Table and Check Column Mappings

After selecting the database, you also need to specify the table. Choose the Actors table.

Then select Edit Mappings to see if the columns from the source match the target.

Once the Column Mappings window appears, check if the columns from the source and target match. You can either pick delete rows in the destination table or append rows. In our case, we want to start cleaning. If you attempted to import using BULK INSERT earlier, there is data present in the target table. And so, an error will occur. To avoid that, choose Delete rows in the destination table. And then, click OK.

Finally, to end our setup for the target table, click Next.

Step 8. Optionally Save to an SSIS Package or Run Immediately

You can save the entire import configuration to an SSIS package. If you choose this, you can schedule the package to run at regular intervals. In our case, we will just run it immediately. For better understanding, check out the next screenshot.

Then, choose Next to see a summary of your chosen settings. Or click Finish to run the import process. You will see the progress in the next window if you click Finish.

That’s it for importing CSV to SQL Server using Import Data in SSMS. You can also choose Import Flat File, but it will always dump to a new table. And you cannot save it to an SSIS package.

PROS AND CONS OF USING SQL SERVER MANAGEMENT STUDIO IMPORT TOOLS

Pros:

• No coding is required;
• If column mappings match source and target, it just works;
• Allows many data sources and destinations, not just SQL Server;
• Saving to SSIS catalog and scheduling is possible, but limited to what was defined;
• Great for a one-time import job.

Cons:

• If you don’t have the specifications of the column types and sizes in the CSV file, column mapping is cumbersome;
• No way to get the CSV from Google Drive, OneDrive, or a similar cloud storage.

Using ETL Tools – Skyvia Cloud Solution

Finally, we will use a cloud ETL tool to import the CSV file to SQL Server. One of the possible ways of importing CSV to SQL Server is from cloud storage like Google Drive. In this section, you will see how to use Skyvia to import the CSV file from Google Drive to SQL Server on a local PC. Before you can use it, you need an account on Skyvia and Google. Both are free to register.

The elements of a successful import using Skyvia are the following:

• Agent – you need an Agent to allow Skyvia to connect to a remote SQL Server.
• Connections – you need to define 2 connections: one for Google Drive and another for the remote SQL Server.
• Package – Skyvia uses packages to define tasks for the import process (like SSIS).

To start, you need to log in to Skyvia. You will be redirected to your default workspace. Then, from here, you can create all the 3 things above. Here’s how.

Step 1. Create an Agent

To create an Agent or a tunnel to a remote SQL Server, click NEW. Then, select Agent.

Once you’re on the next page, name your agent Skyvia-MyPC. Then, download and install the Skyvia agent. Please pay attention to where you install the agent. You will need this when you download the Agent Key. Finally, download the agent key file and store it where you install the Skyvia Agent.

From here, your agent configuration has been completed. But you need to test it. So, run the installed Skyvia agent application. Here’s a screenshot of the installed Skyvia Agent and Key files.

Here’s what it looks like after running the agent.

Once done, you can check the status on Skyvia. Here’s a screenshot of a good connection. This means that now it is possible to connect to your SQL Server from Skyvia.

Step 2. Create Two Connections for the Source and Destination

First, let us create a connection to Google Drive where the CSV is located. For this, click NEW and click Connection.

On the next page, you need to select a connector. Click Google Drive.

Then name the connection MyGDrive, and sign in to your Google account. Click Create Connection to create a connection. Here’s a completed Google Drive connection screenshot.

Second, let’s create a SQL Server connection.

Click NEW again and select Connection. Refer to the screenshot above. Then click SQL Server. Name your connection CSV-MSSQL-TEST. After that click Agent and select the Skyvia-MyPC agent created earlier. Then enter the server name, credentials, and database name. Here’s a screenshot of a completed SQL Server connection.

Step 3. Create the Skyvia Package to Import CSV File to SQL Server

We’re almost done. The final part is to create the Skyvia package. So, start by clicking NEW and then Import. This will create an import package.

In the opened package editor, name your package CSV-SQL-Test and indicate the source and target. Our source is MyGDrive Google Drive connection. And the target is CSV-MSSQL-TEST, the SQL Server connection.

Some other things to pay attention to:

• Select the the Use new runtime checkbox. Otherwise, the SQL Server connection won’t be visible in the drop-down list of targets;
• Choose CSV from storage service. Otherwise, the Google Drive connection won’t be selected;
• Select the Preserve task ordercheckbox. This will make tasks run in succession.

Step 4. Create Tasks in the Import Package

It’s time to create the tasks. First, whatever existing record there is in the Actors table, it should be deleted. Under Tasks, click Add new. A new window will appear. Then, select the actor.csv file in Google Drive. Then, make the Text Qualifier blank and the Code Page Western European Windows (1252). Finally, set the id column to DT_I4 (Integer).

Click Next step to proceed further. On the second page, under Operation, click Delete. This will delete the records in the target. Click Next step again to proceed to mapping settings. You will see a mapping between the id column of the source and target. Click Save.

Finally, the first task is completed.

The second and last task is to insert the rows in the CSV file to SQL Server. The steps are almost the same, except you need to define all column types and sizes based on Table 1 earlier. Then, click Next step and select the Insert operation instead of Delete. Finally, Save the task.

You can see below the screenshot of the completed package. Click Save to save the package.

Step 5. Run the Package

To run the package, click the Run button in the upper right corner of the page. Then, click Monitor to see the progress. See a sample screenshot below.

PROS AND CONS OF USING SKYVIA

Pros:

• A lot of data sources and destinations, including cloud storages;
• Experienced ETL professionals will experience an easy learning curve;
• Schedule an unattended execution of packages;
• Flexible pricing based on current needs and usage;
• No need to install development tools (except when Agent is required).
• Rated 4.8 in G2’s Best ETL Tools and 4.9 in Gartner Peer Insights.

Cons:

• Sometimes queueing can take longer than the actual runtime duration. This can be improved.

Conclusion

You can use these 3 ways to import CSV to SQL Server: all are viable tools depending on your needs.

• BULK INSERT – good for on-premise import jobs with a little coding.
• SQL Server Management Studio Import Tools – good for one-time import and export of various on-premise data.
• ETL Tools – the most flexible for on-premise and cloud data of various types. You can use either SSIS or a cloud solution like Skyvia.

Was this post useful? If yes, please share it on your favorite social media platforms.

How to import a CSV file into a MySQL database?

Using mock data from a fictional app, you will learn how to import a CSV file into a MySQL database via the terminal.

To accomplish this task you will need to:

1. Identify your data
2. Connect to MySQL
3. Create a database
4. Create a table
5. Load the data

*I am using MySQL version 5.7.21 on macOS High Sierra

Step 1: Identify your data

The name of the file is called mock_data.csv

The data represents general information about our users in the app.

Let’s open the CSV file with a text editor (I’m using Sublime):

*Notice that the data is a string with the comma character as the field separator (delimiter). Each line of the CSV file is terminated by a newline character.

Step 2: Connect to the MySQL server

Once you have installed MySQL, log in as the root users

Step 3: Create a database

Before we create a database, let’s see all the current databases on the server.

Let’s add a new database called “app”

Yes. Let’s use the database “app” moving forward.

Step 4: Create a table

*Note: The column and datatype parameters in the SQL table that we will create must match the the number of columns and data types of the CSV file.

CSV to SQL: How to Convert, Open & Import

If you want to easily let your non-technical users update and manage SQL Server data from Excel, click here to download the SQL Spreads Excel Add-In.

In this article, we’re going to look at 3 ways to convert a CSV file to SQL. This is a common use case, so hopefully, you’ll find the following information useful in your day-to-day work.

Table of contents

Introduction

Comma Separated Values (CSV) files are commonly used to exchange data from one system or application to another. The simple format of the csv file makes them ideal for exporting data from one place and importing it to another. A common use case, for example, is to export the data from an accounting or sales application as a csv file and open it in Excel. Once in Excel, users can view and manipulate the data, and prepare reports, often using pivot tables or charts. But what if you want to perform some more complex data manipulations than are possible in Excel? Or what if the data has to be stored and managed in a database for other reasons? In this post, we’ll look at some of the options available for importing csv to sql server.

The options we’ll look at are:

1. a SQL script
2. the SQL Server Management Studio (SSMS) ‘Import Flat File’ wizard
3. an Excel add-in such as SQL Spreads

There are other options too. For example, you can create an SSIS package to do the import, or write a PowerShell script. However, the ones here are generally simpler to work through.

We’re going to be using a set of sample sales data that you can get here . The sample includes 10,000 records.

Option 1 : CSV to SQL using a SQL script

The SQL script to import CSV to SQL is quite straightforward. It simply involves creating a table and then performing an insert of the data. But, as you’ll soon see, whilst this is fine for a small dataset, if you need to import lots of data, then it will be slow and error-prone.

The first step is to create a dummy database. In SQL Server Management Studio (SSMS), execute the following script:

and then create the sales table in the dummy database by executing the following script:

The insert will look something like this, which shows the values for a single row:

We need to insert the values in exactly the correct order to match the order of the columns in the table and add quotes around the string values and date values. We obviously need to find a quick way to construct this part of the script. In Excel, we can do this using the CONCAT function to build up the necessary INSERT string for each row.

Once we have the INSERT statement for each row, we can then copy from Excel and paste into SSMS and execute the query. If you’ve been following along on your own using the sample data, you’ll no doubt have seen an error in SSMS when you ran the query:

I’m sure you can guess the problem, and it highlights one of the main drawbacks of this approach. If there are any apostrophes in the values in any of the text columns, you’ll need to cater for them. In this case, we would need to change ‘Cote d’Ivoire’ to ‘Cote d’’Ivoire’ using a find and replace function.

Option 2 : CSV to SQL using the SSMS ‘Import Flat File’ wizard

Because csv files are so common, SQL Server Management Studio (SSMS) has a standard way of importing them to SQL Server. We’ll now look at this simple wizard-driven approach to importing csv to SQL Server. For more information, you can check out this Microsoft resource .

We’re going to use the same database that we created earlier (‘csv_demo’), and create a new table called ‘sales2’.

Now we just need to follow the steps in the wizard, which you can launch by selecting the csv_demo database that we created earlier and right-clicking and selecting Tasks > Import Flat File

The wizard dialog is now displayed as shown below.

Go ahead and click the ‘Next’ button.

Step 1: select the csv file

You can now select the csv file that you want to import. Note that the wizard will automatically populate the table name with the name of the file, but you can change it if you want to. I have changed it to ‘sales2’.

Click on the ‘Next’ button.

Step 2: preview the data

The wizard now displays a preview of the data. You’ll see that columns are re-named where necessary to remove spaces or invalid characters. You can modify them in the next step of the wizard if necessary.

Step 3: modify the columns as required

The Wizard now displays the schema that has been generated based on its inspection of the data in the csv file. It’s important here for you to take note of the data types that have been detected by the wizard and make any changes as necessary. You can also set a Primary Key for the dataset and specify whether Nulls are allowed. In the screenshot below, I’ve chosen the ‘Order_ID’ column as the PK and left the ‘Allow Nulls’ unchecked, which is the default.

Click ‘Next’ to see a summary of the operation including the database name, table name, and csv file to be imported. We can now click on ‘Finish’.

If everything went well with the import, then the screen below is displayed. This shows the operations that have been completed by the wizard, which in our case is an ‘Insert Data’ operation.

Step 5: check the data

You can check that the data has been imported correctly by executing a couple of SQL statements:

Option 3: CSV to SQL using the SQL Spreads Create Table feature

For this option, you’ll need to download and install SQL Spreads. The download is here , and the installation process is described here .

Once SQL Spreads is installed, open the sales.csv file in Excel. The SQL Spreads ribbon tab is now visible.

Click on the SQL Spreads ribbon tab and then click on the ‘Create new SQL Table’ button:

When you click the ‘Create new SQL Table’ button, you’ll see the following message. This simply means that to do the import correctly, SQL Spreads needs to know the extent of the dataset to import.

Click ‘OK’ and then convert the data range into a table by pressing CTRL + T. You can now go back and click the ‘Create new SQL Table’ button. You’ll be presented with the following dialog where you can specify the table name – in this case, I have entered ‘sales3’ as the table name:

We could now click the ‘OK’ button to create the table in SQL Server and import that data. This would use some default settings which we’ll look at now by clicking the ‘More Settings…’ button. The additional settings allow us to change any of the data types, set a Primary Key, and also specify whether an auto-incrementing ID column should be created. In this example, the sales.csv file already has a unique identifier column, ‘Order ID’, so I have set that as the PK and un-checked the auto-increment option.

Click ‘OK’. SQL Spreads will now create the table in SQL Server and import the data.

We can now go into SSMS to see the new table and the data:

Note that to see the new table in the SQL Spreads Designer, click the Design mode button twice to close and reopen the Designer.

This will refresh the list of databases and tables, and you will see your new table in the list.

Now that the data is in a table in SQL Server and also Excel, you can manage the data in either place and the data will always be synchronized.

Summary

We’ve looked at 3 options to import a csv data file into SQL Server:

1. SQL Script: this uses a couple of simple SQL scripts in SQL Server Management Studio (SSMS) to convert from csv to sql.
2. SSMS Import Flat File Wizard: a simple and robust import wizard in SSMS.
3. SQL Spreads Create Table feature: a simple and robust wizard in Excel using the SQL Spreads Excel Add-in.

Option (1) is simple but would be cumbersome to use for anything other than a simple CSV file with a few columns.

Options (2) and (3) are both user-friendly and reliable. The key difference is that Option (3) can be used by anybody that can use Excel, and doesn’t require any SQL DBA skills at all. This makes it a great choice for people and organizations that like the ease and flexibility of Excel in terms of data management, but also need the advantages of databases like SQL Server.

Download a trial version of SQL Spreads today to import your data from csv to SQL Server.

*This article was originally published on May 12, 2021 and was updated on January 31 2022 to refresh some screenshots.

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.

Leave a Reply

Comments (1)

Detta är precis vad jag letat efter. Satt tidigare i kväll och stångades med de andra metoderna att ladda en databastabell från Excel. Misslyckades med SQL Server Mgmt Studio.

Hi,
I am the founder of SQL Spreads, the lightweight data management
solution to
update SQL Server data using Excel

In this blog we share the Excel and SQL Server knowledge that we have learnt during our work
with hundreds of customers worldwide.

Hope you find it useful!

Best regards,
Johannes Åkesson

Love Excel? Need to update SQL Server data?

Use Excel to update your data in SQL Server?

Try the SQL Spreads data management solution to update and manage your SQL Server data from within Excel.