Import CSV file into SQL Server
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 beinserted with double quotes into rows.after inserting the data into table you could replace those double quotes with ''.
update tableset 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 behandle using ERRORFILE property, specify the error file name, it will write the rowshaving 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 )
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 -> Import Data...
• Click the Next > 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 > 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 > button.
• On the Select Source Tables and Views window, you can Edit Mappings before clicking on the Next > button.
• Check the Run immediately check box and click on the Next > button.
• Click on the Finish button to run the package.
The above was found on this website (I have used it and tested):
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 SchoolsTempFROM '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)