0

enter image description hereI have a csv file with few columns, Address column contain the values like:

"james street, green park hotel road, NewJersy"

this is my bulk insert script; its working fine when address field is not having ",". due to match with FieldTerminator value in script its treating address field value comma as a field terminator so the remaining part of value is moving to next field.As a customer given csv file we can't do any changes in it.So how to make them work without missing commas in address field

thanks in advance. enter image description here

Improve this question
6
  • Why do you assume that the contents are "james street, green park hotel road, NewJersy" instead of "james street, ? Only because you assume that there is a text separator, the " character. What if it was '? BULK INSERT isn't a CSV import tool, it's what its name says - a BULK import tools whose job is to load data as fast as possible without parsing Commented Apr 28, 2017 at 10:52
  • stackoverflow.com/questions/5541165/… Commented Apr 28, 2017 at 10:54
  • I just mentioned the address field value in double quotes here. Actually there is no " double quotes in field value Commented Apr 28, 2017 at 10:57
  • Then you don't have an address field at all. You have 3 fields. If you didn't use the double quotes, no human would be able to guess if this is just one field or many Commented Apr 28, 2017 at 11:20
  • The duplicate shows how to use a format file to use a different separator for each column, including text qualifiers. You can't import a single field with commas if there is no way to determine if these are one field or three. Try changing the export script to use a text qualifier, or a field separator that isn't likely to appear in text, eg a tab, | or ¤. Tab-separated files are the easiest way to deal with such problems Commented Apr 28, 2017 at 11:29

1 Answer 1

Reset to default
0

try this,

Error file property

  BULK INSERT dbo.NPI_tbl
    FROM 'd:\npiData\yourfilename.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'd:\npiData\ErrorFile.csv',
    TABLOCK
    )

oh sorry then use OPENROWSET it allow you to select then insert. so after select you can clean data then insert

INSERT INTO 
  dbo.NPI_tbl
SELECT *
FROM 
  OPENROWSET(BULK 'd:\npiData\yourfilename.csv',
  FORMATFILE = 'd:\npiData\npiFormat_c.fmt'
  ) AS e;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This would discard rows, not import them
this one skip the rows which have format problems
I have added my csv file
@Kumar..the script getting error "XML parsing: line 1, character 0, one root element".
how come you finsih so early ? first learn to use openrowset.learn to use formatfile.if fmt not working then use txt file.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.