In this case, we would need to change ‘Cote d’Ivoire’ to ‘Cote d’’Ivoire’ using a find and replace function. If there are any apostrophes in the values in any of the text columns, you’ll need to cater for them. I’m sure you can guess the problem, and it highlights one of the main drawbacks of this approach. 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: Msg 102, Level 15, State 1, Line 1375 Once we have the INSERT statement for each row, we can then copy from Excel and paste into SSMS and execute the query. In Excel, we can do this using the CONCAT function to build up the necessary INSERT string for each row. We obviously need to find a quick way to construct this part of the script. 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. The insert will look something like this, which shows the values for a single row: INSERT INTO sales1(Order_ID,Region,Country,Item_Type,Sales_Channel,Order_Priority,Order_Date,Ship_Date,Units_Sold,Unit_Price,Unit_Cost,Total_Revenue,Total_Cost,Total_Profit) ![]() In SQL Server Management Studio (SSMS), execute the following script: CREATE DATABASE csv_demoĪnd then create the sales table in the dummy database by executing the following script: CREATE TABLE sales1( ![]() The first step is to create a dummy database. 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. It simply involves creating a table and then performing an insert of the data. The SQL script to import CSV to SQL is quite straightforward. We’re going to be using a set of sample sales data that you can get here. However, the ones here are generally simpler to work through. For example, you can create an SSIS package to do the import, or write a PowerShell script.
0 Comments
Leave a Reply. |