BULK INSERT some rows being added with quotation marks
up vote
0
down vote
favorite
I'm attempting to BULK INSERT a tab-separated text file into a database only containing VARCHAR data. For some reason, some of the data is getting double quotation marks placed around it randomly, while other rows do not:
domain sku type product
amazon.com b0071n529i laptop hp_4535s_a7k08ut#aba_15.6-inch_laptop
amazon.com b00715sj82 laptop "dell_64gb_mini_pcie_ssd_pata,_f462n"
The statement I'm using looks like this:
BULK INSERT database
FROM 'file.txt' WITH (FIRSTROW = 1, FIELDTERMINATOR = 't', ROWTERMINATOR = '0x0a');
sql-server tsql edited Nov 11 at 8:37
marc_s
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
add a comment |
up vote
0
down vote
favorite
I'm attempting to BULK INSERT a tab-separated text file into a database only containing VARCHAR data. For some reason, some of the data is getting double quotation marks placed around it randomly, while other rows do not:
domain sku type product
amazon.com b0071n529i laptop hp_4535s_a7k08ut#aba_15.6-inch_laptop
amazon.com b00715sj82 laptop "dell_64gb_mini_pcie_ssd_pata,_f462n"
The statement I'm using looks like this:
BULK INSERT database
FROM 'file.txt' WITH (FIRSTROW = 1, FIELDTERMINATOR = 't', ROWTERMINATOR = '0x0a');
edited Nov 11 at 8:37
marc_s
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
If you use a terminator (in your case thetab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution1;2;"This is a text;and it continues". Check if the quoted strings include a tab...
– Shnugo
Nov 11 at 11:14
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm attempting to BULK INSERT a tab-separated text file into a database only containing VARCHAR data. For some reason, some of the data is getting double quotation marks placed around it randomly, while other rows do not:
domain sku type product
amazon.com b0071n529i laptop hp_4535s_a7k08ut#aba_15.6-inch_laptop
amazon.com b00715sj82 laptop "dell_64gb_mini_pcie_ssd_pata,_f462n"
The statement I'm using looks like this:
BULK INSERT database
FROM 'file.txt' WITH (FIRSTROW = 1, FIELDTERMINATOR = 't', ROWTERMINATOR = '0x0a');
edited Nov 11 at 8:37
marc_s
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
I'm attempting to BULK INSERT a tab-separated text file into a database only containing VARCHAR data. For some reason, some of the data is getting double quotation marks placed around it randomly, while other rows do not:
domain sku type product
amazon.com b0071n529i laptop hp_4535s_a7k08ut#aba_15.6-inch_laptop
amazon.com b00715sj82 laptop "dell_64gb_mini_pcie_ssd_pata,_f462n"
The statement I'm using looks like this:
BULK INSERT database
FROM 'file.txt' WITH (FIRSTROW = 1, FIELDTERMINATOR = 't', ROWTERMINATOR = '0x0a');
edited Nov 11 at 8:37
marc_s
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
edited Nov 11 at 8:37
marc_s
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
edited Nov 11 at 8:37
marc_s
567k12710961246
edited Nov 11 at 8:37
marc_s
567k12710961246
edited Nov 11 at 8:37
marc_s
567k12710961246
567k12710961246
asked Nov 11 at 2:30
tsb8m
254
asked Nov 11 at 2:30
tsb8m
254
asked Nov 11 at 2:30
tsb8m
254
254
If you use a terminator (in your case thetab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution1;2;"This is a text;and it continues". Check if the quoted strings include a tab...
– Shnugo
Nov 11 at 11:14
add a comment |
If you use a terminator (in your case thetab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution1;2;"This is a text;and it continues". Check if the quoted strings include a tab...
– Shnugo
Nov 11 at 11:14
If you use a terminator (in your case the
tab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like 1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution 1;2;"This is a text;and it continues". Check if the quoted strings include a tab...– Shnugo
Nov 11 at 11:14
If you use a terminator (in your case the
tab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like 1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution 1;2;"This is a text;and it continues". Check if the quoted strings include a tab...– Shnugo
Nov 11 at 11:14
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
If your issue is those double quotes then you can do this after insertion that would be the better solution,
UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
answered Nov 11 at 2:39
Ajan Balakumaran
50229
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
|
show 1 more comment
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
If your issue is those double quotes then you can do this after insertion that would be the better solution,
UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
answered Nov 11 at 2:39
Ajan Balakumaran
50229
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
|
show 1 more comment
up vote
0
down vote
If your issue is those double quotes then you can do this after insertion that would be the better solution,
UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
answered Nov 11 at 2:39
Ajan Balakumaran
50229
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
|
show 1 more comment
up vote
0
down vote
up vote
0
down vote
If your issue is those double quotes then you can do this after insertion that would be the better solution,
UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
answered Nov 11 at 2:39
Ajan Balakumaran
50229
If your issue is those double quotes then you can do this after insertion that would be the better solution,
UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
answered Nov 11 at 2:39
Ajan Balakumaran
50229
answered Nov 11 at 2:39
Ajan Balakumaran
50229
answered Nov 11 at 2:39
Ajan Balakumaran
50229
answered Nov 11 at 2:39
Ajan Balakumaran
50229
50229
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
|
show 1 more comment
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file.
– tsb8m
Nov 11 at 3:05
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
could you check this,
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
stackoverflow.com/questions/12902110/…
– Ajan Balakumaran
Nov 11 at 3:08
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that.
– Ajan Balakumaran
Nov 11 at 3:10
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
My text file doesn't have any double quotes though... tabs are my text qualifier.
– tsb8m
Nov 11 at 4:49
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245341%2fbulk-insert-some-rows-being-added-with-quotation-marks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you use a terminator (in your case the
tab), which might occur within a text as well, the usual approach is to use text qualifiers. In most cases this is the double quote. Just imagine a CSV with the semi-colon as delimitier and a row like1;2;This is a text;and it continues. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution1;2;"This is a text;and it continues". Check if the quoted strings include a tab...– Shnugo
Nov 11 at 11:14