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 YourTablefrom 'Your File Path'with (fieldterminator=',', rowterminator='\n')--Chandu |
|
|
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. |
|
|
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 |
|
|
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 |
|
|
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 exampleRestaurants, "McDonald's, Inc.", 1Department 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. |
|
|
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 exampleRestaurants, "McDonald's, Inc.", 1Department 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.
|
|
|
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. |
|
|
arpana patil
Starting Member
24 Posts |
Posted - 2012-10-16 : 07:16:05
|
csv file doesnt has escape charactersquote: 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.
|
|
|
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. |
|
|
|