SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Check if Comma exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arpana patil
Starting Member

India
24 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/16/2012 :  03:41:49  Show Profile  Reply with Quote
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

India
24 Posts

Posted - 10/16/2012 :  04:10:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/16/2012 :  04:38:41  Show Profile  Reply with Quote
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

India
24 Posts

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/16/2012 :  06:44:43  Show Profile  Reply with Quote
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

India
24 Posts

Posted - 10/16/2012 :  06:57:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/16/2012 :  07:00:26  Show Profile  Reply with Quote
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

India
24 Posts

Posted - 10/16/2012 :  07:16:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/16/2012 :  07:50:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000