Import CSV file into SQL Server

Question

I am looking for help to import a  csv file into SQL Server using BULK INSERT and I have few basic questions   Issues      The CSV file data may have    comma  in between  Ex  description   so how can I  make import handling these data    If the client creates the CSV from Excel then the data that have comma are enclosed within     double quotes   as the below example  so how do the import can handle this    How do we track if some rows have bad data  which import skips   does import skips rows that are not importable    Here is the sample CSV with header   Name Class Subject ExamDate Mark Description Prabhat 4 Math 2 10 2013 25 Test data for prabhat  Murari 5 Science 2 11 2013 24  Test data for his s test  where we can test 2nd ROW  Test   sanjay 4 Science  25 Test Only    And SQL statement to import   BULK INSERT SchoolsTemp FROM  C  CSVData Schools csv  WITH       FIRSTROW   2      FIELDTERMINATOR         --CSV field delimiter     ROWTERMINATOR     n     --Use to shift the control to next row     TABLOCK

User · Answer

I know that there are accepted answer but still, I want to share my scenario that maybe help someone to solve their problem TOOLS

ASP.NET
EF CODE-FIRST APPROACH
SSMS
EXCEL

SCENARIO i was loading the dataset which's in CSV format which was later to be shown on the View i tried to use the bulk load but I's unable to load as BULK LOAD was using

FIELDTERMINATOR = ','

and Excel cell was also using , however, I also couldn't use Flat file source directly because I was using Code-First Approach and doing that only made model in SSMS DB, not in the model from which I had to use the properties later.

SOLUTION

I used flat-file source and made DB table from CSV file (Right click DB in SSMS -> Import Flat FIle -> select CSV path and do all the settings as directed)
Made Model Class in Visual Studio (You MUST KEEP all the datatypes and names same as that of CSV file loaded in sql)
use Add-Migration in NuGet package console
Update DB

User · Answer

I know this is not the exact solution to the question above  but for me  it was a nightmare when I was trying to Copy data from one database located at a separate server to my local    I was trying to do that by first export data from the Server to CSV txt and then import it to my local table   Both solutions  with writing down the query to import CSV or using the SSMS Import Data wizard was always producing errors  errors were very general  saying that there is parsing problem   And although I wasn t doing anything special  just export to CSV and then trying to import CSV to the local DB  the errors were always there    I was trying to look at the mapping section and the data preview  but there was always a big mess  And I know the main problem was comming from one of the table columns  which was containing JSON and SQL parser was treating that wrongly   So eventually  I came up with a different solution and want to share it in case if someone else will have a similar problem     What I did is that I ve used the Exporting Wizard on the external Server   Here are the steps to repeat the same process  1  Right click on the database and select Tasks - gt  Export Data       2  When Wizard will open  choose Next and in the place of  Data Source   choose  SQL Server Native Client          In case of external Server you will most probably have to choose  Use SQL Server Authentication  for the  Authentication Mode      3  After hitting Next  you have to select the Destionation  For that  select again  SQL Server Native Client   This time you can provide your local  or some other external DB  DB     4  After hitting the Next button  you have two options either to copy the entire table from one DB to another or write down the query to specify the exact data to be copied   In my case  I didn t need the entire table  it was too large   but just some part of it  so I ve chosen  Write a query to specify the data to transfer         I would suggest writing down and testing the query on a separate query editor before moving to Wizard    5  And finally  you need to specify the destination table where the data will be selected        I suggest to leave it as  dbo   Query  or some custom Table name in case if you will have errors exporting the data or if you are not sure about the data and want further analyze it before moving to the exact table you want      And now go straight to the end of the Wizard by hitting Next Finish buttons

User · Answer

2  If the client create the csv from excel then the data that have   comma are enclosed within          double quotes   as the below   example  so how do the import can handle this    You should use FORMAT    CSV   FIELDQUOTE       options   BULK INSERT SchoolsTemp FROM  C  CSVData Schools csv  WITH       FORMAT    CSV        FIELDQUOTE            FIRSTROW   2      FIELDTERMINATOR         --CSV field delimiter     ROWTERMINATOR     n     --Use to shift the control to next row     TABLOCK

User · Answer

You first need to create a table in your database in which you will be importing the CSV file  After the table is created  follow the steps below       Log into your database using SQL Server Management Studio      Right click on your database and select Tasks - gt  Import Data         Click the Next  gt  button      For the Data Source  select Flat File Source   Then use the Browse button to select the CSV file   Spend some time configuring how you want the data to be imported before clicking on the Next  gt  button       For the Destination  select the correct database provider  e g  for SQL Server 2012  you can use SQL Server Native Client 11 0    Enter the Server name  Check the Use SQL Server Authentication radio button  Enter the User name  Password  and Database before clicking on the Next  gt  button       On the Select Source Tables and Views window  you can Edit Mappings before clicking on the Next  gt  button       Check the Run immediately check box and click on the Next  gt  button       Click on the Finish button to run the package   The above was found on this website  I have used it and tested

User · Answer

Firs you need to import CSV file into Data Table   Then you can insert bulk rows using SQLBulkCopy   using System  using System Data  using System Data SqlClient   namespace SqlBulkInsertExample       class Program             static void Main string   args                        DataTable prodSalesData   new DataTable  ProductSalesData                    Create Column 1  SaleDate             DataColumn dateColumn   new DataColumn                dateColumn DataType   Type GetType  System DateTime                dateColumn ColumnName    SaleDate                   Create Column 2  ProductName             DataColumn productNameColumn   new DataColumn                productNameColumn ColumnName    ProductName                   Create Column 3  TotalSales             DataColumn totalSalesColumn   new DataColumn                totalSalesColumn DataType   Type GetType  System Int32                totalSalesColumn ColumnName    TotalSales                   Add the columns to the ProductSalesData DataTable             prodSalesData Columns Add dateColumn               prodSalesData Columns Add productNameColumn               prodSalesData Columns Add totalSalesColumn                   Let s populate the datatable with our stats                 You can add as many rows as you want here                  Create a new row             DataRow dailyProductSalesRow   prodSalesData NewRow                dailyProductSalesRow  SaleDate     DateTime Now Date              dailyProductSalesRow  ProductName      Nike               dailyProductSalesRow  TotalSales     10                  Add the row to the ProductSalesData DataTable             prodSalesData Rows Add dailyProductSalesRow                   Copy the DataTable to SQL Server using SqlBulkCopy             using  SqlConnection dbConnection   new SqlConnection  Data Source ProductHost Initial Catalog dbProduct Integrated Security SSPI Connection Timeout 60 Min Pool Size 2 Max Pool Size 20                                   dbConnection Open                    using  SqlBulkCopy s   new SqlBulkCopy dbConnection                                         s DestinationTableName   prodSalesData TableName                       foreach  var column in prodSalesData Columns                          s ColumnMappings Add column ToString    column ToString                          s WriteToServer prodSalesData

User · Answer

May be SSMS  How to import  Copy Paste  data from excel can help  If you don t want to use BULK INSERT or don t have permissions for it

User · Answer

Import the file into Excel by first opening excel  then going to DATA  import from TXT File  choose the csv extension which will preserve 0 prefixed values  and save that column as TEXT because excel will drop the leading 0 otherwise  DO NOT double click to open with Excel if you have numeric data in a field starting with a 0  zero    Then just save out as a Tab Delimited Text file  When you are importing into excel you get an option to save as GENERAL  TEXT  etc   choose TEXT so that quotes in the middle of a string in a field like YourCompany LLC are preserved also     BULK INSERT dbo YourTableName FROM  C  Users Steve Downloads yourfiletoIMPORT txt  WITH   FirstRow   2   if skipping a header row  FIELDTERMINATOR     t   ROWTERMINATOR       n      I wish I could use the FORMAT and Fieldquote functionality but that does not appear to be supported in my version of SSMS

User · Answer

Because they do not use the SQL import wizard  the steps would be as follows      Right click on the database in the option tasks to import data  Once the wizard is open  we select the type of data to be implied  In this case it would be the       Flat file source   We select the CSV file  you can configure the data type of the tables in the CSV  but it is best to bring it from the CSV    Click Next and select in the last option that is      SQL client   Depending on our type of authentication we select it  once this is done  a very important option comes    We can define the id of the table in the CSV  it is recommended that the columns of the CSV should be called the same as the fields in the table   In the option Edit Mappings we can see the preview of each table with the column of the spreadsheet  if we want the wizard to insert the id by default we leave the option unchecked       Enable id insert    usually not starting from 1   instead if we have a column with the id in the CSV we select the enable id insert  the next step is to end the wizard  we can review the changes here   On the other hand  in the following window may come alerts  or warnings the ideal is to ignore this  only if they leave error is necessary to pay attention   This link has images

User · Answer

All of the answers here work great if your data is  quot clean quot   no data constraint violations  etc   and you have access to putting the file on the server  Some of the answers provided here stop at the first error  PK violation  data-loss error  etc   and give you one error at a time if using SSMS s built in Import Task  If you want to gather all errors at once  in case you want to tell the person that gave you the  csv file to clean up their data   I recommend the following as an answer  This answer also gives you complete flexibility as you are  quot writing quot  the SQL yourself  Note  I m going to assume you are running a Windows OS and have access to Excel and SSMS  If not  I m sure you can tweak this answer to fit your needs   Using Excel  open your  csv file  In an empty column you will write a formula that will build individual INSERTstatements like  CONCATENATE  quot INSERT INTO dbo MyTable  FirstName  LastName  VALUES    quot   A1   quot      quot   B1  quot    quot   CHAR 10    quot GO quot   where A1 is a cell that has the first name data and A2 has the last name data for example   CHAR 10  adds a newline character to the final result and GO will allow us to run this INSERT and continue to the next even if there are any errors    Highlight the cell with your  CONCATENATION   formula  Shift   End to highlight the same column in the rest of your rows  In the ribbon  gt  Home  gt  Editing  gt  Fill  gt  Click Down  This applies the formula all the way down the sheet so you don t have to copy-paste  drag  etc  down potentially thousands of rows by hand   Ctrl   C to copy the formulated SQL INSERT statements  Paste into SSMS  You will notice Excel  probably unexpectedly  added double quotes around each of your INSERT and GO commands  This is a  quot feature quot      of copying multi-line values out of Excel  You can simply find and replace  quot INSERT and GO quot  with INSERT and GO respectively to clean that up   Finally you are ready to run your import process  After the process completes  check the Messages window for any errors  You can select all the content  Ctrl   A  and copy into Excel and use a column filter to remove any successful messages and you are left with any and all the errors    This process will definitely take longer than other answers here  but if your data is  quot dirty quot  and full of SQL violations  you can at least gather all the errors at one time and send them to the person that gave you the data  if that is your scenario

User · Answer

The best  quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows  list separator setting to something other than a comma  such as a pipe    This will then generate a pipe  or whatever  separated file for you that you can then import   This is described here

User · Answer

Based SQL Server CSV Import     1  The CSV file data may have    comma  in between  Ex    description   so how can I make import handling these data    Solution  If you re using    comma  as a delimiter  then there is no way to differentiate between a comma as a field terminator and a comma in your data  I would use a different FIELDTERMINATOR like     Code would look like and this will handle comma and single slash perfectly      2  If the client create the csv from excel then the data that have   comma are enclosed within          double quotes   as the below   example  so how do the import can handle this    Solution  If you re using BULK insert then there is no way to handle double quotes  data will be inserted with double quotes into rows  after inserting the data into table you could replace those double quotes with      update table set columnhavingdoublequotes   replace columnhavingdoublequotes              3  How do we track if some rows have bad data  which import skips     does import skips rows that are not importable     Solution  To handle rows which aren t loaded into table because of invalid data or format  could be handle using ERRORFILE property  specify the error file name  it will write the rows having error to error file  code should look like   BULK INSERT SchoolsTemp     FROM  C  CSVData Schools csv      WITH           FIRSTROW   2      FIELDTERMINATOR         --CSV field delimiter     ROWTERMINATOR     n     --Use to shift the control to next row     ERRORFILE    C  CSVDATA SchoolsErrorRows csv       TABLOCK

User · Answer

Here s how I would solve it    Just Save your CSV File as a XLS Sheet in excel By Doing so  you wouldn t have to worry about delimitiers  Excel s spreadsheet format will be read as a table and imported directly into a SQL Table  Import the File Using SSIS Write a Custom Script in the import manager to omit modify the data you re looking for  Or run a master script to scrutinize the data you re looking to remove    Good Luck

[sql] Import CSV file into SQL Server

Examples related to sql

Examples related to sql-server

Examples related to csv

Examples related to bulkinsert

Examples related to bulk