Implementing the OUTPUT Clause in SQL Server
At work, I often find myself exporting data in .csv format for our clients. I am talking about thousands of data, to the point that I never really noticed this issue.
I am talking about data that contain special characters like a comma for example.
So once in a while, I’d encounter names with commas in them. Names with suffixes like “Smith, Jr”, “Donald, Jr”, or “Elizabeth, III” and so on.
When exporting to a comma separated value .csv file using SQL Server Integration Services (SSIS), the flat file destination connection manager assumes that this comma is the end of the data and therefore “spills” the rest of the data into the next column. The problem with this is that it disrupts the arrangement of the output data thereby causing inconsistent and even incorrect output.
In the following example, I will be showing how to correct this issue using a text qualifier:
Before I continue, I will create my sample data below:
CREATE TABLE dbo.Employee
(
EmpID INT,
FirstName VARCHAR(20),
LastName VARCHAR(20),
MiddleName VARCHAR(20),
BirthDate DATE,
HireDate DATE,
Salary MONEY,
EndDate DATE
)
INSERT INTO dbo.Employee (EmpID, FirstName, LastName, MiddleName, BirthDate,
HireDate, Salary, EndDate)
VALUES
(1, 'John', 'Brown, Jr.','K', '01-01-1960', '10-01-1985', 85000.00, GETDATE()),
(2, 'Pete', 'Dawn, III', NULL,'12-03-1965', '12-01-2000', 60000.00, NULL),
(3, 'Amanda', 'Lopez', NULL,'06-01-1980', '12-01-2005', 45000.00, NULL),
(4, 'Adam', 'Smith, Sr.', NULL, '02-26-1971', '04-23-2001', 70000.00, GETDATE()),
(5, 'Taylor', 'Ramsey, Jr', NULL, '03-01-1984', '11-11-2004', 60000.00, NULL)
SELECT * FROM dbo.Employee
My result is shown below:
Search for:
Removing duplicate records from a table in SQL server
At work, I often find myself exporting data in .csv format for our clients. I am talking about thousands of data, to the point that I never really noticed this issue.
I am talking about data that contain special characters like a comma for example.
So once in a while, I’d encounter names with commas in them. Names with suffixes like “Smith, Jr”, “Donald, Jr”, or “Elizabeth, III” and so on.
When exporting to a comma separated value .csv file using SQL Server Integration Services (SSIS), the flat file destination connection manager assumes that this comma is the end of the data and therefore “spills” the rest of the data into the next column. The problem with this is that it disrupts the arrangement of the output data thereby causing inconsistent and even incorrect output.
In the following example, I will be showing how to correct this issue using a text qualifier:
Before I continue, I will create my sample data below:
CREATE TABLE dbo.Employee
(
EmpID INT,
FirstName VARCHAR(20),
LastName VARCHAR(20),
MiddleName VARCHAR(20),
BirthDate DATE,
HireDate DATE,
Salary MONEY,
EndDate DATE
)
INSERT INTO dbo.Employee (EmpID, FirstName, LastName, MiddleName, BirthDate,
HireDate, Salary, EndDate)
VALUES
(1, 'John', 'Brown, Jr.','K', '01-01-1960', '10-01-1985', 85000.00, GETDATE()),
(2, 'Pete', 'Dawn, III', NULL,'12-03-1965', '12-01-2000', 60000.00, NULL),
(3, 'Amanda', 'Lopez', NULL,'06-01-1980', '12-01-2005', 45000.00, NULL),
(4, 'Adam', 'Smith, Sr.', NULL, '02-26-1971', '04-23-2001', 70000.00, GETDATE()),
(5, 'Taylor', 'Ramsey, Jr', NULL, '03-01-1984', '11-11-2004', 60000.00, NULL)
SELECT * FROM dbo.Employee
My result is shown below:
Search for:
SSIS – How to deal with commas in data when exporting to a CSV file
At work, I often find myself exporting data in .csv format for our clients. I am talking about thousands of data, to the point that I never really noticed this issue.
I am talking about data that contain special characters like a comma for example.
So once in a while, I’d encounter names with commas in them. Names with suffixes like “Smith, Jr”, “Donald, Jr”, or “Elizabeth, III” and so on.
When exporting to a comma separated value .csv file using SQL Server Integration Services (SSIS), the flat file destination connection manager assumes that this comma is the end of the data and therefore “spills” the rest of the data into the next column. The problem with this is that it disrupts the arrangement of the output data thereby causing inconsistent and even incorrect output.
In the following example, I will be showing how to correct this issue using a text qualifier:
Before I continue, I will create my sample data below:
CREATE TABLE dbo.Employee
(
EmpID INT,
FirstName VARCHAR(20),
LastName VARCHAR(20),
MiddleName VARCHAR(20),
BirthDate DATE,
HireDate DATE,
Salary MONEY,
EndDate DATE
)
INSERT INTO dbo.Employee (EmpID, FirstName, LastName, MiddleName, BirthDate,
HireDate, Salary, EndDate)
VALUES
(1, 'John', 'Brown, Jr.','K', '01-01-1960', '10-01-1985', 85000.00, GETDATE()),
(2, 'Pete', 'Dawn, III', NULL,'12-03-1965', '12-01-2000', 60000.00, NULL),
(3, 'Amanda', 'Lopez', NULL,'06-01-1980', '12-01-2005', 45000.00, NULL),
(4, 'Adam', 'Smith, Sr.', NULL, '02-26-1971', '04-23-2001', 70000.00, GETDATE()),
(5, 'Taylor', 'Ramsey, Jr', NULL, '03-01-1984', '11-11-2004', 60000.00, NULL)
SELECT * FROM dbo.Employee
My result is shown below:
Search for: