Chad Miller is a SQL Server database admin and the senior manager of database administration at Raymond James Financial. In his spare time, he is the project coordinator and developer of the CodePlex project SQL Server PowerShell Extensions (SQLPSX<\/a>). Chad leads the Tampa Windows PowerShell User Group<\/a>, and he is a frequent speaker at SQL Saturdays and Code Camps.<\/p>\n Contact information: Windows PowerShell has built in support for creating CSV files by using the Export-CSV<\/b> cmdlet. However, the creation of a CSV file is usually only a short stop in an overall process that includes loading the file into another system. In this post, we’ll look at a few scripted-based approaches to import CSV data into SQL Server. Note<\/b>: SQL Server includes a component specifically for data migration called SQL Server Integration Services (SSIS), which is beyond the scope of this article.<\/p>\n The T-SQL BULK INSERT <\/a>command is of the easiest ways to import CSV files into SQL Server. The BULK INSERT command requires a few arguments to describe the layout of the CSV file and the location of file. Let’s look at an example of creating a CSV file by using Export-CSV<\/b>, and then importing the information into a SQL Server table by using BULK INSERT.<\/p>\n Requirements<\/strong><\/p>\n Setup<\/strong><\/p>\n <\/strong>1. Download the following script: Invoke-SqlCmd2.ps1<\/a><\/p>\n <\/a>2. Create a table disk space by copying the following code in SQL Server Management Studio. <\/p>\n Note<\/b>: The example uses a database named “hsg.”<\/p>\n CREATE TABLE dbo.diskspace(<\/p>\n UsageDate datetime,<\/p>\n SystemName varchar(50),<\/p>\n Label varchar(50),<\/p>\n VolumeName varchar(50),<\/p>\n Size decimal(6,2),<\/p>\n Free decimal(6,2),<\/p>\n PrecentFree decimal(5,2)<\/p>\n )<\/p>\n The following image shows the command in SQL Server Management Studio.<\/p>\n 3. Save the following script as Get-DiskSpaceUsage.ps1, which will be used as the demonstration script later in this post.<\/p>\n param($ComputerName=”.”)<\/p>\n Get-WmiObject -computername “$computername” Win32_Volume -filter “DriveType=3” | foreach {<\/p>\n new-object PSObject -property @{<\/p>\n UsageDate = $((Get-Date).ToString(“yyyy-MM-dd”))<\/p>\n SystemName = $_.SystemName<\/p>\n Label = $_.Label<\/p>\n VolumeName = $_.Name<\/p>\n Size = $([math]::round(($_.Capacity\/1GB),2))<\/p>\n Free = $([math]::round(($_.FreeSpace\/1GB),2))<\/p>\n PercentFree = $([math]::round((([float]$_.FreeSpace\/[float]$_.Capacity) * 100),2))<\/p>\n }<\/p>\n } | Select UsageDate, SystemName, Label, VolumeName, Size, Free, PercentFree<\/p>\n Now we will use the script Get-DiskSpaceUsage.ps1 that I presented earlier. It lists information about disk space, and it stores the information in a CSV file.<\/p>\n .\/get-diskusage.ps1 | export-csv -Path “C:\\Users\\Public\\diskspace.csv” -NoTypeInformation<\/span><\/p>\n The generated CSV file shows that Export-CSV<\/b> includes a text delimiter of double quotes around each field:<\/p>\n “UsageDate”,”SystemName”,”Label”,”VolumeName”,”Size”,”Free”,”PercentFree”<\/p>\n “2011-11-20″,”WIN7BOOT”,”RUNCORE SSD”,”D:\\”,”59.62″,”31.56″,”52.93″<\/p>\n “2011-11-20″,”WIN7BOOT”,”DATA”,”E:\\”,”297.99″,”34.88″,”11.7″<\/p>\n “2011-11-20″,”WIN7BOOT”,,”C:\\”,”48″,”6.32″,”13.17″<\/p>\n “2011-11-20″,”WIN7BOOT”,”HP_TOOLS”,”F:\\”,”0.1″,”0.09″,”96.55″<\/p>\n Although many programs handle CSV files with text delimiters (including SSIS, Excel, and Access), BULK INSERT does not. To use BULK INSERT without a lot of work, we’ll need to remove the double quotes. We can use a quick and dirty way of simply replacing all the quotes in the CSV file. In the blog post Remove Unwanted Quotation Marks from CSV Files by Using PowerShell<\/a>, the Scripting Guys explains how to remove double quotes. This method can be used for circumstances where you know it won’t cause problems. How do you know? Well, the data being generated from our Get-DiskspaceUsage<\/b> should never have double quotes or commas in the data. So here’s the code to remove the double quotes:<\/p>\n (Get-Content C:\\Users\\Public\\diskspace.csv) | foreach {$_ -replace ‘”‘} | Set-Content C:\\Users\\Public\\diskspace.csv<\/p>\n UsageDate,SystemName,Label,VolumeName,Size,Free,PercentFree<\/p>\n 2011-11-20,WIN7BOOT,RUNCORE SSD,D:\\,59.62,31.56,52.93<\/p>\n 2011-11-20,WIN7BOOT,DATA,E:\\,297.99,34.88,11.7<\/p>\n 2011-11-20,WIN7BOOT,,C:\\,48,6.32,13.17<\/p>\n 2011-11-20,WIN7BOOT,HP_TOOLS,F:\\,0.1,0.09,96.55<\/p>\n Now we are ready to import the CSV file as follows:<\/p>\n . .\\Invoke-SqlCmd2.ps1<\/p>\n <\/p>\n $query = @”<\/p>\n BULK INSERT hsg.dbo.diskspace FROM ‘C:\\Users\\Public\\diskspace.csv’<\/p>\n WITH (FIRSTROW = 2, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\\n’)<\/p>\n “@<\/p>\n <\/p>\n Invoke-SqlCmd2 -ServerInstance “$env:computername\\sql1” -Database hsg -Query $query<\/p>\n The following data shows that our CSV file was successfully imported.<\/p>\n
Blog: Sev17<\/a>
Twitter: cmille19<\/a><\/p>\nImporting CSV files into SQL Server<\/h2>\n
T-SQL BULK INSERT command<\/h2>\n
\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/8540.HSG-11-28-11-1.jpg\" "\\"Image")
<\/a><\/pre>\n<\/pre>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/3362.hsg-11-28-11-2.jpg\" "\\"Image")
<\/a><\/p>\n