0

While importing cvs files into database, it happens that there is a comma in the content itself. For example for the field address, it might be a valid content "1330 XYZ Ave, Washington, USA".Using MySQL, there is an option to skip the comma inside a field. Here is the option:

fields Escaped by '"'

So, my question is that do we have anything for MSSQL when using Bulk insert command?

Anything like (see last line)

bulk insert table1 from 'file_path.csv' with (
  rowterminated by '\n',
  filedterminated by ',',
  fieldescaped by '"')

Many thanks in advance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Espanta
  • 1,080
  • 1
  • 17
  • 27
  • Are you using SQL Server or some other RDBMS? – Politank-Z May 18 '15 at 20:58
  • I mentioned SQL in the question subject. I modify it – Espanta May 18 '15 at 21:03
  • I think that he meant, are you using MySQL, MSSQL, PostgreSQL. or any of the other SQL variants? Not all of them are the same. – Seer May 18 '15 at 21:17
  • I just saw in another post in SO, that SQL does not support such an option and there is no proper way to do so. Lets hope for some more thoughts and then we can close this post since it might be duplicate. – Espanta May 18 '15 at 21:22
  • Bulk insert is a SQL Server operation, so I changed the tag from mysql to sql-server. – Gordon Linoff May 18 '15 at 21:25

1 Answers1

2

Oh, that lovely CSV format. To most of the world, it simply means values with commas separating them. However, if a value contains a comma, then double quotes are used.

To the best of my knowledge, SQL Server does not have a built-in method for handling true CSV formatted files. What I have faced this problem in the past, I have opened the file in Excel and saved it out with tab separators rather than comma separators. This will not work in all cases, but it has worked for me in the past.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I preferably take it to MySQL and from there export if to SQL using Link Server ;) – Espanta May 18 '15 at 23:46
  • 1
    @Espanta . . . I haven't imported CSV values into MySQL, but I see the documentation supports quoted fields. Shocking, shocking that MySQL supports the format. Excel has supported them pretty much since day 0, so you would think that other software from the same company would be able to read the format. – Gordon Linoff May 19 '15 at 01:06
  • 1
    This is Microsoft ;-) pretty inconsistent ;-) – Espanta May 19 '15 at 22:40