Sometime we need import data in CSV files into SQL server while it turns out that there are several ways for that. In this post, we mainly walk through 2 common ways, using SQL Server Management Studio or using PowerShell.
Using SQL Server Management Studio
Just using built-in SQL Server Management Studio, you can get CSV files imported into SQL databases. To do that, you have to:
- Firstly create a table in the database. The table is where you import CSV files into.
- Secondly use SQL Server Management Studio to locate your database.
- Thirdly right click the database and you will see a menu like what the following picture shows. Note that clicking on the entire database is required, or else you cannot get into the next step.
- Then choose Tasks in above menu and you will get another menu with options including Import Data, which is your click on for moving to the next step.
- Now fulfill some blanks and make some decisions on SQL Server Import and Export Wizard page.
Data source comes first and you need choose Flat File Source. Then click Browse button to search your CSV file. Before you open the file, we recommend you to select CSV filetype rather than TXT so that you can ensure your CSV file could be opened up correctly. At last, configure and review the data imported before your click on the Next button.
You may meet a pop-up Wizard welcome page before stepping into SQL Server Import and Export Wizard page. But you click Next button and you will see the page.
- Move into a new page for deciding Destination, Server name, Authentication and Database. In this step, Windows Authentication and SQL Server Authentication are optional to you but the latter requires you to enter user name and password. After establishing all answers, click Next.
- Then come to Select Source Tables and Views If you need, you can choose Edit Mappings or Preview before your click on Next.
- Now reach Save and Run Package page where you can directly select Run immediately and click Next on the bottom. Or you can choose Save SSIS Package and make some configurations. Then go into the next step.
- Finally see Complete the Wizard page that shows your previous configurations on importing your CSV file into SQL database. If you think they are what you want, just click Finish button to start importing.
PowerShell comes from Microsoft. It has built-in Export-CSV cmdlet, which supports creation of CSV files. Besides that, Powershell helps in importing CSV files into SQL server. In below, we talk about 2 kinds of ways to achieve importing by using PowerShell.
Before introducing the commands, I expect that you have already got local access and Sysadmin to SQL server. If you do, now follow our steps in below:
- Simple Way to Import CSV
Check out the following image that displays some codes, which can greatly deal with headers rows as well as optional quotes.
However, you should already find out that above codes contain WriteToServer that accepts DataReaders or DataTables as data resources. Thus, you need meanwhile take advantage of Out-Table script, which is to convert CSV to a DataTable. And you have to drag the whole CSV into memory, which costs more time as well.
- Fast Way to Import CSV
You can also process importing CSV into SQL server faster just by converting CSV into a DataReader. Rather than loading the entire CSV into memory, this method just streams CSV through memory, which saves much time.
You can browse DataReader for fast importing CSV on the GitHub. Or you can just download a simple DLL that works as a DataReader to expose CSV. The following image shows what it looks like after applying DLL into PowerShell:
This way also comes with some defaults. One of them is case-sensitive column names if you need customize file mapping.
Besides above way to import CSV into SQL server, you can also complete the task by LogParser (a command-line tool), Bulk Insert, SSIS, and etc. Or you can ask for help of your web host if it is kind enough. We check out many ASP.NET hosting providers, like DiscountASP.NET, Host4ASP.NET and GoDaddy gives detailed instructions on importing CSV files into your SQL server on their knowledgebase or blog. If you do not find one, you can talk with your host and ask whether he could help you.