Mixing
SSIS - remove character when it has alphabetic character infront en behind it
up vote
3
down vote
favorite
Let's say I have data imported from a csv in SSIS that looks like this:
ID,Name,Year,Place
1,"John",1994,"UK"
2,"Bill",1899,"USA"
3,"Gill",1989,"Germ"any"
In this case, there is a typo in Germany
I was wondering if there was a way (in derived column component maybe) to remove the " When the text is like _"_
_ being an alphabetic character or space.
This question came close to what I needed: SSIS - remove character X unless it's followed by character Y , but it's done in SQL, while my data is in SSIS from a csv.
sql-server ssis asked yesterday
DenStudent
5381625
add a comment |
up vote
3
down vote
favorite
Let's say I have data imported from a csv in SSIS that looks like this:
ID,Name,Year,Place
1,"John",1994,"UK"
2,"Bill",1899,"USA"
3,"Gill",1989,"Germ"any"
In this case, there is a typo in Germany
I was wondering if there was a way (in derived column component maybe) to remove the " When the text is like _"_
_ being an alphabetic character or space.
This question came close to what I needed: SSIS - remove character X unless it's followed by character Y , but it's done in SQL, while my data is in SSIS from a csv.
sql-server ssis asked yesterday
DenStudent
5381625
The wayward"is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.
– Larnu
yesterday
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
Let's say I have data imported from a csv in SSIS that looks like this:
ID,Name,Year,Place
1,"John",1994,"UK"
2,"Bill",1899,"USA"
3,"Gill",1989,"Germ"any"
In this case, there is a typo in Germany
I was wondering if there was a way (in derived column component maybe) to remove the " When the text is like _"_
_ being an alphabetic character or space.
This question came close to what I needed: SSIS - remove character X unless it's followed by character Y , but it's done in SQL, while my data is in SSIS from a csv.
sql-server ssis asked yesterday
DenStudent
5381625
Let's say I have data imported from a csv in SSIS that looks like this:
ID,Name,Year,Place
1,"John",1994,"UK"
2,"Bill",1899,"USA"
3,"Gill",1989,"Germ"any"
In this case, there is a typo in Germany
I was wondering if there was a way (in derived column component maybe) to remove the " When the text is like _"_
_ being an alphabetic character or space.
This question came close to what I needed: SSIS - remove character X unless it's followed by character Y , but it's done in SQL, while my data is in SSIS from a csv.
sql-server ssis sql-server ssis asked yesterday
DenStudent
5381625
asked yesterday
DenStudent
5381625
edited yesterday
asked yesterday
DenStudent
5381625
asked yesterday
DenStudent
5381625
asked yesterday
DenStudent
5381625
5381625
The wayward"is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.
– Larnu
yesterday
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago
add a comment |
The wayward"is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.
– Larnu
yesterday
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago
The wayward
" is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.– Larnu
yesterday
The wayward
" is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.– Larnu
yesterday
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago
add a comment |
3 Answers
3
active
oldest
votes
up vote
1
down vote
accepted
The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a character, otherwise the SSIS expression will fail.
""" + REPLACE(Place,""","") + """
answered yesterday
userfl89
1,01149
add a comment |
up vote
1
down vote
You can do like
CREATE TABLE T
([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));
INSERT INTO T
([ID], [Name], [Year], [Place])
VALUES
(1, '"John"', 1994, '"UK"'),
(2, '"Bill"', 1899, '"USA"'),
(3, '"Gill"', 1989, '"Germ"an"y"');
SELECT ID,
Name,
[Year],
CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;
Returns:
+----+--------+------+-----------+
| ID | Name | Year | Place |
+----+--------+------+-----------+
| 1 | "John" | 1994 | "UK" |
| 2 | "Bill" | 1899 | "USA" |
| 3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+
Demo
answered yesterday
Sami
6,27531038
add a comment |
up vote
0
down vote
If understood your question correctly!
There has inbuilt functionality to avoid such characters from FLAT-FILE.
As shown in below image, just add your Special character "Text qualifier" in Flat-file Connection String. As I have inserted DOUBLE-QUOTE("). Best thing in this is that You no need to have your special character in each columns and rows! It will only TRIM the text where your special characters do exist from both the sides of text!
Please do comment if this is not enough! But hope this will help you!
answered 17 hours ago
im_one
313114
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a character, otherwise the SSIS expression will fail.
""" + REPLACE(Place,""","") + """
answered yesterday
userfl89
1,01149
add a comment |
up vote
1
down vote
accepted
The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a character, otherwise the SSIS expression will fail.
""" + REPLACE(Place,""","") + """
answered yesterday
userfl89
1,01149
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a character, otherwise the SSIS expression will fail.
""" + REPLACE(Place,""","") + """
answered yesterday
userfl89
1,01149
The answer that @Sami posted is a good option, and if you are specifically aiming to use a derived column for this then the following expression will remove any " while still keeping the leading and trailing double quotes. Make sure to escape the " with a character, otherwise the SSIS expression will fail.
""" + REPLACE(Place,""","") + """
answered yesterday
userfl89
1,01149
answered yesterday
userfl89
1,01149
answered yesterday
userfl89
1,01149
answered yesterday
userfl89
1,01149
1,01149
add a comment |
add a comment |
up vote
1
down vote
You can do like
CREATE TABLE T
([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));
INSERT INTO T
([ID], [Name], [Year], [Place])
VALUES
(1, '"John"', 1994, '"UK"'),
(2, '"Bill"', 1899, '"USA"'),
(3, '"Gill"', 1989, '"Germ"an"y"');
SELECT ID,
Name,
[Year],
CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;
Returns:
+----+--------+------+-----------+
| ID | Name | Year | Place |
+----+--------+------+-----------+
| 1 | "John" | 1994 | "UK" |
| 2 | "Bill" | 1899 | "USA" |
| 3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+
Demo
answered yesterday
Sami
6,27531038
add a comment |
up vote
1
down vote
You can do like
CREATE TABLE T
([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));
INSERT INTO T
([ID], [Name], [Year], [Place])
VALUES
(1, '"John"', 1994, '"UK"'),
(2, '"Bill"', 1899, '"USA"'),
(3, '"Gill"', 1989, '"Germ"an"y"');
SELECT ID,
Name,
[Year],
CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;
Returns:
+----+--------+------+-----------+
| ID | Name | Year | Place |
+----+--------+------+-----------+
| 1 | "John" | 1994 | "UK" |
| 2 | "Bill" | 1899 | "USA" |
| 3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+
Demo
answered yesterday
Sami
6,27531038
add a comment |
up vote
1
down vote
up vote
1
down vote
You can do like
CREATE TABLE T
([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));
INSERT INTO T
([ID], [Name], [Year], [Place])
VALUES
(1, '"John"', 1994, '"UK"'),
(2, '"Bill"', 1899, '"USA"'),
(3, '"Gill"', 1989, '"Germ"an"y"');
SELECT ID,
Name,
[Year],
CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;
Returns:
+----+--------+------+-----------+
| ID | Name | Year | Place |
+----+--------+------+-----------+
| 1 | "John" | 1994 | "UK" |
| 2 | "Bill" | 1899 | "USA" |
| 3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+
Demo
answered yesterday
Sami
6,27531038
You can do like
CREATE TABLE T
([ID] int, [Name] varchar(45), [Year] int, [Place] varchar(45));
INSERT INTO T
([ID], [Name], [Year], [Place])
VALUES
(1, '"John"', 1994, '"UK"'),
(2, '"Bill"', 1899, '"USA"'),
(3, '"Gill"', 1989, '"Germ"an"y"');
SELECT ID,
Name,
[Year],
CONCAT('"', REPLACE(Place, '"', ''), '"') Place
FROM T;
Returns:
+----+--------+------+-----------+
| ID | Name | Year | Place |
+----+--------+------+-----------+
| 1 | "John" | 1994 | "UK" |
| 2 | "Bill" | 1899 | "USA" |
| 3 | "Gill" | 1989 | "Germany" |
+----+--------+------+-----------+
Demo
answered yesterday
Sami
6,27531038
edited yesterday
answered yesterday
Sami
6,27531038
answered yesterday
Sami
6,27531038
answered yesterday
Sami
6,27531038
6,27531038
add a comment |
add a comment |
up vote
0
down vote
If understood your question correctly!
There has inbuilt functionality to avoid such characters from FLAT-FILE.
As shown in below image, just add your Special character "Text qualifier" in Flat-file Connection String. As I have inserted DOUBLE-QUOTE("). Best thing in this is that You no need to have your special character in each columns and rows! It will only TRIM the text where your special characters do exist from both the sides of text!
Please do comment if this is not enough! But hope this will help you!
answered 17 hours ago
im_one
313114
add a comment |
up vote
0
down vote
If understood your question correctly!
There has inbuilt functionality to avoid such characters from FLAT-FILE.
As shown in below image, just add your Special character "Text qualifier" in Flat-file Connection String. As I have inserted DOUBLE-QUOTE("). Best thing in this is that You no need to have your special character in each columns and rows! It will only TRIM the text where your special characters do exist from both the sides of text!
Please do comment if this is not enough! But hope this will help you!
answered 17 hours ago
im_one
313114
add a comment |
up vote
0
down vote
up vote
0
down vote
If understood your question correctly!
There has inbuilt functionality to avoid such characters from FLAT-FILE.
As shown in below image, just add your Special character "Text qualifier" in Flat-file Connection String. As I have inserted DOUBLE-QUOTE("). Best thing in this is that You no need to have your special character in each columns and rows! It will only TRIM the text where your special characters do exist from both the sides of text!
Please do comment if this is not enough! But hope this will help you!
answered 17 hours ago
im_one
313114
If understood your question correctly!
There has inbuilt functionality to avoid such characters from FLAT-FILE.
As shown in below image, just add your Special character "Text qualifier" in Flat-file Connection String. As I have inserted DOUBLE-QUOTE("). Best thing in this is that You no need to have your special character in each columns and rows! It will only TRIM the text where your special characters do exist from both the sides of text!
Please do comment if this is not enough! But hope this will help you!
answered 17 hours ago
im_one
313114
answered 17 hours ago
im_one
313114
answered 17 hours ago
im_one
313114
answered 17 hours ago
im_one
313114
313114
add a comment |
add a comment |
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%2f53372467%2fssis-remove-character-when-it-has-alphabetic-character-infront-en-behind-it%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
The wayward
"is going to break the validity of the CSV. if you're using a double quote (") as the text identifier and it can appear in your data you need to use a different text identifier.– Larnu
yesterday
@DenStudent: Only by SSIS you want to do? If so then there has inbuilt functionality for that!
– im_one
18 hours ago