Import CSV file into SQL Server

by

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
    )

More Related Contents:

  1. SQL Server bulk insert for large data set
  2. How to concatenate text from multiple rows into a single text string in SQL Server
  3. Where value in column containing comma delimited values
  4. Concat field value to string in SQL Server
  5. How to get export output in “real” CSV format in SQL Server Management Studio?
  6. Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
  7. Slow bulk insert for table with many indexes
  8. SQL Server – Best way to get identity of inserted row?
  9. How to update two tables in one statement in SQL Server 2005?
  10. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  11. Split function equivalent in T-SQL?
  12. Querying data by joining two tables in two database on different servers
  13. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
  14. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  15. How to find all connected subgraphs of an undirected graph
  16. What is SYSNAME data type in SQL Server?
  17. How to drop SQL default constraint without knowing its name?
  18. Solution for: Store update, insert, or delete statement affected an unexpected number of rows (0) [closed]
  19. Is it possible to delete from multiple tables in the same SQL statement?
  20. How to return the output of stored procedure into a variable in sql server
  21. How to select only the first rows for each unique value of a column?
  22. Modify Default value in SQL Server
  23. Produce DISTINCT values in STRING_AGG
  24. Function in SQL Server 2008 similar to GREATEST in mysql?
  25. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  26. How do I compare 2 rows from the same table (SQL Server)?
  27. How do I change db schema to dbo
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Bulk insert using stored procedure
  30. How to insert into a table with just one IDENTITY column?

Leave a Comment