Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Check if Comma exists

Author  Topic 

arpana patil
Starting Member

24 Posts

Posted - 2012-10-16 : 01:55:46
How to check if a CSV files contains Comma(,) which is used to Bulk Insert.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-16 : 03:41:49
hi,

If you are asking for fieldterminator as comma,


bulk insert YourTable
from 'Your File Path'
with (fieldterminator=',',
rowterminator='\n')

--
Chandu
Go to Top of Page

arpana patil
Starting Member

24 Posts

Posted - 2012-10-16 : 04:10:27
No now presently i am doing in the same way, but if there is comma for any column inside csv it will split into 2 columns,so i want to check if comma exists in csv then i want to throw some error message.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-16 : 04:38:41
In that case you need to change your fieldterminator to any symbol( such as @ or tab(\t)....

--
Chandu
Go to Top of Page

arpana patil
Starting Member

24 Posts

Posted - 2012-10-16 : 06:13:56
I tryed by replacing FIELDTERMINATOR to tab but at that time bluk insert wont happen,table is blank
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 06:44:43
If you have a CSV file, where the delimiter is a comma, and if the data itself contains comma's you are in a no-win situtation; the file is really unuseable. One approach people use is to use double-quotes to escape data that has commas. For example
Restaurants, "McDonald's, Inc.", 1
Department Stores,"Walmart, Inc.",2
Even with the escape characters, bulk-insert cannot process such files correctly.

Your only choice as I see it is to ask the people who sent the file to regenerate it using a different delimiter such as pipe.
Go to Top of Page

arpana patil
Starting Member

24 Posts

Posted - 2012-10-16 : 06:57:14
There is no any way to check if CSV file has comma before doing bulk insert in SQL?

quote:
Originally posted by sunitabeck

If you have a CSV file, where the delimiter is a comma, and if the data itself contains comma's you are in a no-win situtation; the file is really unuseable. One approach people use is to use double-quotes to escape data that has commas. For example
Restaurants, "McDonald's, Inc.", 1
Department Stores,"Walmart, Inc.",2
Even with the escape characters, bulk-insert cannot process such files correctly.

Your only choice as I see it is to ask the people who sent the file to regenerate it using a different delimiter such as pipe.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 07:00:26
Does your file have escape characters as I indicated in my previous post? If you do, you can use SSIS (or even Import/Export Wizard) to import such files.

If your files don't have such escape characters then I know of no systematic way for a program, be it bulk insert or something else, to process such files correctly. When you think about it, one may not even be able to distinguish where one column ends and the next one begins even if one were to examine the file manually.
Go to Top of Page

arpana patil
Starting Member

24 Posts

Posted - 2012-10-16 : 07:16:05
csv file doesnt has escape characters
quote:
Originally posted by sunitabeck

Does your file have escape characters as I indicated in my previous post? If you do, you can use SSIS (or even Import/Export Wizard) to import such files.

If your files don't have such escape characters then I know of no systematic way for a program, be it bulk insert or something else, to process such files correctly. When you think about it, one may not even be able to distinguish where one column ends and the next one begins even if one were to examine the file manually.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 07:50:39
quote:
csv file doesnt has escape characters
In that case, as I said earlier, the only reasonable thing to do is to generate a new file with an unambiguous delimiter. If there only a few rows and if it is a one time thing, and if the data is clear enough that a manual inspection can fix the problems, that may be another alternative. But I would hate to be the person who has to do it if there is anything more than a handful of rows.
Go to Top of Page
   

- Advertisement -