Sql Server 2008 Read Specific Columns From Csv File

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

Table Of Contents

1. Why to Import CSV to SQL Server
2. How to Import CSV File in SQL Server (3 Easy Ways)
3. BULK INSERT
4. SQL Server Management Studio Import CSV Tools
5. ETL Tools – Skyvia Deject Solution
6. Conclusion

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 betwixt 2 or more systems of dissimilar platforms and architectures. Today, the reason is however the aforementioned. Imagine y'all have 2 proprietary systems. One is using a proprietary NoSQL database like PayPal'south. And the other is using SQL Server. It's like a Japanese talking to an Estonian. Understanding is virtually impossible. What is the solution you volition enquire?

Export a CSV file from the NoSQL database. And so, 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. And so, the bank provides payment records to the service visitor using a CSV file.
• An operational system built in-business firm needs to be integrated into an ERP system.
• Several SharePoint lists need to exist synced to a SQL Server database for data analysis.
• A biometrics arrangement needs to exist integrated into a human being resources arrangement for attendance purposes.

Though this can be done using JSON or XML, CSV is simpler to generate from the source. If the information requirement is non-hierarchical, CSV can exist a adept fit. What about the target organisation using SQL Server? It is rated four.5 out of 5 in Gartner Peer Insights. SQL Server in the cloud or Azure SQL is also one of the acme iii DBMS of 2020. And so, learning to import CSV to SQL Server is worth the effort. You will ask how to exercise it. The reply is described in detail below.

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

Before nosotros beginning, we need a sample CSV and a target table in SQL Server. Bank check it out in the screenshot below.

This will be used to upload to SQL Server using 3 of the different means to import CSV. And so, download a copy of the actor.csv file from here. Remember where yous saved it. You lot'll need it later on in this tutorial.

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

In this tutorial, I'one thousand using the server name MS-SQLSERVER. And the database name is CSV-MSSQL-TEST.

Using Majority INSERT

Majority INSERT is a control in SQL Server to import information files into a database tabular array. It tin can be used to upload various file formats, including CSV. If you love a little coding, this is the mode to become. Hither's the code on how to bulk insert CSV into SQL Server:

-- truncate the table starting time TRUNCATE TABLE dbo.Actors; GO  -- import the file Majority INSERT dbo.Actors FROM 'C:\Documents\Skyvia\csv-to-mssql\actor.csv' WITH ( 		FORMAT='CSV', 		FIRSTROW=2 ) GO

The first command simply deletes all the records in the target table. Then, the BULK INSERT control 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 = two because the commencement row contains the cavalcade names.

You can effect a BULK INSERT control from SQL Server Management Studio or whatever other SQL Server tool. Another style 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 demand to parse the data. Bulk INSERT does information technology for you. If the target column uses a data type too pocket-sized for the data, an error will occur;
• Scheduling of execution possible in SQL Server Agent;
• Also great for a 1-time import task.

Cons:

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

Using SQL Server Direction Studio Import CSV Tools

If y'all don't prefer coding, some other useful tool is the Import Data from SQL Server Management Studio (SSMS). Hither's how to import CSV to MSSQL tabular array using SSMS.

Pace 1. From the Object Explorer, Expand the Databases Binder

Beginning, you need to become to Object Explorer and select the target database. Aggrandize the Databases folder. Bank check information technology out below.

Footstep 2. Select the Target Database

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

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

Stride 3. Select a Apartment File Source

This part will allow you lot pick the information source. For this, click the drop-downwards listing and select Flat File Source. Afterwards, click Adjacent.

Step 4. Specify the CSV File

Now, you need to specify the file. Click Scan and specify the path of the CSV file equally shown in the screenshot. If you accept downloaded it, specify the download location. Then, in the file type, select CSV files (*.csv). And later on – actor.csv. Continue further past clicking Open up.

Stride 5. Configure the Columns

Can't SSMS detect the correct data types for each cavalcade? In our sample data, detection of the right 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 all-time to match the right types and sizes. Why? To avert errors and headaches.

Let's start by clicking Advanced. Then, you volition see a list of columns. Click each and set the blazon and size.

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

Column Name	DataType	OutputColumnWidth
id	four-byte signed integer [DT_I4]	Northward/A
lastname	cord [DT_STR]	20
firstname	string [DT_STR]	20
middlename	cord [DT_STR]	20
suffix	cord [DT_STR]	3

Step 6. Choose the Destination (SQL Server)

At present, y'all need to specify the SQL Server as the target of the import procedure. To do that, select Microsoft OLE DB Provider for SQL Server. And so, select the SQL Server proper noun and enter the necessary credentials. Select the Database name and click Adjacent.

Step 7. Specify the Database Table and Check Column Mappings

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

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

Once the Column Mappings window appears, cheque if the columns from the source and target friction match. Y'all can either selection delete rows in the destination table or append rows. In our instance, we want to commencement cleaning. If yous attempted to import using BULK INSERT earlier, at that place is data present in the target tabular array. And then, an mistake will occur. To avoid that, cull Delete rows in the destination table. And 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 salve the entire import configuration to an SSIS package. If y'all choose this, y'all tin schedule the package to run at regular intervals. In our case, nosotros will simply run it immediately. For ameliorate understanding, check out the next screenshot.

And so, choose Side by side to see a summary of your chosen settings. Or click Finish to run the import process. You lot will see the progress in the next window if you click End.

That's information technology for importing CSV to SQL Server using Import Data in SSMS. You tin also cull Import Apartment File, but information technology will ever 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 cavalcade mappings match source and target, it just works;
• Allows many data sources and destinations, not but SQL Server;
• Saving to SSIS catalog and scheduling is possible, only express to what was divers;
• Not bad for a one-time import chore.

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 like deject storage.

Integrate data between 80+ cloud sources with no coding with Skyvia Data Integration

Using ETL Tools – Skyvia Deject Solution

Finally, nosotros volition utilise a deject ETL tool to import the CSV file to SQL Server. I of the possible ways of importing CSV to SQL Server is from deject storage like Google Drive. In this section, you volition run into how to use Skyvia to import the CSV file from Google Bulldoze to SQL Server on a local PC. Earlier you tin use information technology, you need an business relationship on Skyvia and Google. Both are gratuitous to register.

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

• Agent – y'all demand an Agent to allow Skyvia to connect to a remote SQL Server.
• Connections – y'all 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 offset, y'all demand to log in to Skyvia. You volition exist redirected to your default workspace. And then, from here, you can create all the 3 things higher up. Hither's how.

Pace 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, proper name your agent Skyvia-MyPC. Then, download and install the Skyvia agent. Please pay attention to where you install the agent. You volition need this when you download the Agent Key. Finally, download the amanuensis primal file and store information technology where you install the Skyvia Agent.

From hither, your amanuensis configuration has been completed. Just you need to exam it. And then, run the installed Skyvia agent application. Hither's a screenshot of the installed Skyvia Agent and Cardinal files.

Here'southward what it looks similar subsequently running the agent.

Once washed, you can check the condition on Skyvia. Here'south a screenshot of a adept connectedness. This means that at present information technology is possible to connect to your SQL Server from Skyvia.

Step 2. Create 2 Connections for the Source and Destination

First, allow united states of america create a connection to Google Drive where the CSV is located. For this, click NEW and click Connectedness.

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

Then name the connection MyGDrive, and sign in to your Google account. Click Create Connectedness to create a connection. Here'southward a completed Google Drive connexion screenshot.

2nd, let'due south create a SQL Server connexion.

Click NEW again and select Connection. Refer to the screenshot above. And then click SQL Server. Name your connection CSV-MSSQL-Test. After that click Amanuensis and select the Skyvia-MyPC amanuensis created before. Then enter the server name, credentials, and database name. Hither's a screenshot of a completed SQL Server connection.

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

We're about done. The terminal role is to create the Skyvia package. And so, offset past clicking NEW and then Import. This will create an import package.

In the opened parcel editor, proper 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 exist visible in the driblet-down list of targets;
• Choose CSV from storage service. Otherwise, the Google Bulldoze 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 in that location is in the Actors table, it should be deleted. Nether Tasks, click Add new. A new window will appear. Then, select the actor.csv file in Google Drive. And then, make the Text Qualifier blank and the Code Folio Western European Windows (1252). Finally, set the id column to DT_I4 (Integer).

Click Next step to proceed further. On the 2nd page, under Operation, click Delete. This will delete the records in the target. Click Next step again to proceed to mapping settings. You will run across a mapping between the id cavalcade of the source and target. Click Salve.

Finally, the start job is completed.

The 2nd and last chore is to insert the rows in the CSV file to SQL Server. The steps are almost the aforementioned, except you demand to define all cavalcade types and sizes based on Table one earlier. Then, click Next pace and select the Insert operation instead of Delete. Finally, Save the task.

You tin see beneath the screenshot of the completed package. Click Save to save the package.

Stride 5. Run the Package

To run the bundle, click the Run button in the upper right corner of the page. Then, click Monitor to run across 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 feel an easy learning bend;
• Schedule an unattended execution of packages;
• Flexible pricing based on current needs and usage;
• No demand to install evolution tools (except when Agent is required).
• Rated 4.eight in G2'southward 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.

Schedule your CSV data consign and import to cloud apps or databases without coding

Determination

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

• BULK INSERT – skilful for on-premise import jobs with a petty coding.
• SQL Server Management Studio Import Tools – expert for ane-time import and export of various on-premise data.
• ETL Tools – the nearly flexible for on-premise and cloud data of various types. You lot tin can use either SSIS or a cloud solution like Skyvia.

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

macdonnellfruck1991.blogspot.com

Source: https://skyvia.com/blog/3-easy-ways-to-import-csv-file-to-sql-server

Share :