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

sam
  • 95
  • 1
  • 2
  • 13
  • 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 – Panagiotis Kanavos Apr 28 '17 at 10:52
  • http://stackoverflow.com/questions/5541165/commas-causing-a-problem-using-bulk-insert-and-a-format-file – Richard Hansell Apr 28 '17 at 10:54
  • I just mentioned the address field value in double quotes here. Actually there is no " double quotes in field value – sam Apr 28 '17 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 – Panagiotis Kanavos Apr 28 '17 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 – Panagiotis Kanavos Apr 28 '17 at 11:29
  • Unless it is impossible (I don't see why) I would ask the person that sent this file to send me tab delimited or CSV with quoted values. This doesn't answer the question I know, just a thought if possible to solve without extra coding why not? – Vladimir Bozic Apr 29 '17 at 08:07

1 Answers1

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;
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22