How to Remove empty columns from Excel import to SQL server in SSIS [Resolved]

Posted by Sriharim under Sql Server on 4/7/2015 | Points: 10 | Views : 13459 | Status : [Member] | Replies : 9

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I will get data in excel sheets.I want load data from excel sheet to SQL server database using SSIS tool, in my excel sheet some columns are empty, this empty columns are loaded has null values into sql server (as shown in below (also see attached file),newly add columns have all rows are nulls, but excel sheet, this null are not there).

In excel data is like:

CountryName1 ActorName ActorGender
United States Adam Sandler Male
United States Adrien Brody Male
United States Alan Rickman Male
United States Albert Finney Male
United States Alec Baldwin Male

After loading into sql server, few columns are added with null values. in sql server table , as shown below

NULL Country Name Actor Name Actor Gender NULL NULL
NULL United States Adam Sandler Male NULL NULL
NULL United States Adrien Brody Male NULL NULL
NULL United States Alan Rickman Male NULL NULL
NULL United States Albert Finney Male NULL NULL
NULL United States Alec Baldwin Male NULL NULL
NULL United States Andy Garcia Male NULL NULL
NULL United States Andy Serkis Male NULL NULL
NULL United States Angelina Jolie Female NULL NULL
NULL United States Anthony Daniels Male NULL NULL

Please, give solution, how Remove null columns as shown in image while loading data and to load columns which has data ?

Attachment

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 4/14/2015 [Member] [MVP] Platinum | Points: 50

0	select * from actortable where actorname is not NULL except select * from actortable where actorname =' ' Alternative for the above code: select * from actortable where NULLIF(actorname, ' ') is not NULL Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriharim on: 4/24/2015 [Member] Starter | Points: 25

0	Hi Thank You for reply. Regarding Query 2. For a employee, Joining salary is multiple with all the hike percentage . But after each hike,salary will be revised for the employee. For a employee, after 1st hike salary will be new_salary = joining_salary+joining_salaryhike_percentage1 , after 2nd hike salary will be new_salary2 = new_salaryhike_percentage2 , after 3rd hike salary will be new_salary3 = new_salary2hike_percentage3....like that salary will be revised after every hike. In a year, a Employee may get hike 1 or more than 2 times. Then adding up all the revised salary's to get total paid to employee. I am not able write query to add all the revised salary and to get total salary , average salary paid to employee during 2000 to 2015 year. Please help me. Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer*. \| Alert Moderator

Hi
Thank You for reply.

Regarding Query 2.
For a employee, Joining salary is multiple with all the hike percentage .

But after each hike,salary will be revised for the employee.

For a employee, after 1st hike salary will be new_salary = joining_salary+joining_salary*hike_percentage1 , after 2nd hike salary will be new_salary2 = new_salary*hike_percentage2 , after 3rd hike salary will be new_salary3 = new_salary2*hike_percentage3....like that salary will be revised after every hike.
In a year, a Employee may get hike 1 or more than 2 times.

Then adding up all the revised salary's to get total paid to employee.

I am not able write query to add all the revised salary and to get total salary , average salary paid to employee during 2000 to 2015 year.

Please help me.

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/27/2015 [Member] [MVP] Platinum | Points: 50

0	--Table Creation DECLARE @Employee table(EmployeeID int,EmployeeName varchar(100),JoiningSalary int) DECLARE @Salary_hike table (EmployeeID int,SalaryHikePercentage int,Year int) -- Sample data for both tables insert into @EMPLOYEE SELECT 1, 'AAA', 180 union all SELECT 2, 'BBB', 200 union all SELECT 3, 'CCC', 150 union all SELECT 4, 'DDD', 100 insert into @Salary_hike SELECT 1, 10, 2000 union all SELECT 1, 10, 2001 union all SELECT 1, 10, 2002 union all SELECT 1, 10, 2003 union all SELECT 1, 10, 2004 union all SELECT 1, 10, 2005 union all SELECT 1, 40, 2010 union all SELECT 1, 20, 2015 --Write a query to get total salary paid to employee during 2000 to 2015 year --Below Query is for getting Total pais salary based on employee DECLARE @RevisedSal bigint , @EmployeeID int = 1 SELECT @RevisedSal = ISNULL(@RevisedSal, JoiningSalary)+ ( ISNULL(@RevisedSal, JoiningSalary)SalaryHikePercentage)/100 from @Employee e join @Salary_hike eh on e.employeeID= eh.employeeID where e.EmployeeID = @EmployeeID and year between 2000 and 2015 SELECT @RevisedSal TotalSalPaid, @EmployeeID as EmployeeID Output: TotalSalPaid EmployeeID 529 1 Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator



--Table Creation 
DECLARE @Employee table(EmployeeID int,EmployeeName varchar(100),JoiningSalary int)
DECLARE @Salary_hike table (EmployeeID int,SalaryHikePercentage int,Year int)

-- Sample data for both tables 
insert into @EMPLOYEE 
SELECT 1, 'AAA', 180 union all
SELECT 2, 'BBB', 200 union all
SELECT 3, 'CCC', 150 union all
SELECT 4, 'DDD', 100 

insert into @Salary_hike
SELECT 1, 10, 2000 union all
SELECT 1, 10, 2001 union all
SELECT 1, 10, 2002 union all
SELECT 1, 10, 2003 union all
SELECT 1, 10, 2004 union all
SELECT 1, 10, 2005 union all
SELECT 1, 40, 2010 union all
SELECT 1, 20, 2015 

--Write a query to get total salary paid to employee during 2000 to 2015 year 

--Below Query is for getting Total pais salary based on employee  
DECLARE @RevisedSal bigint , @EmployeeID int = 1

SELECT @RevisedSal = ISNULL(@RevisedSal, JoiningSalary)+ ( ISNULL(@RevisedSal, JoiningSalary)*SalaryHikePercentage)/100
from @Employee e
join @Salary_hike eh on e.employeeID= eh.employeeID
where e.EmployeeID = @EmployeeID
and year between 2000 and 2015

SELECT @RevisedSal TotalSalPaid, @EmployeeID as EmployeeID

Output:

TotalSalPaid	EmployeeID
529	          1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/9/2015 [Member] [MVP] Platinum | Points: 25

0	Is the target table created at the time SSIS package execution? or Table is already existed in database ? If already in database, how many columns are there in the table? It totally depends on how you are mapping Excel columns with table columns.. Which task you are using to load excel data into table? how you are mapping excel columns with table column ? check these and let me know further details so that i can help you Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Is the target table created at the time SSIS package execution? or Table is already existed in database ? If already in database, how many columns are there in the table?

It totally depends on how you are mapping Excel columns with table columns..

Which task you are using to load excel data into table?
how you are mapping excel columns with table column ? check these and let me know further details so that i can help you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 4/10/2015 [Member] Starter | Points: 25

0	Hi, Thank you for your response. I have taken 'Excel source' task and 'OLE DB Destination' task used. The table is created at the time SSIS package execution, as mentioned below. After adding Excel connection manager, when i right click on Excel source (edit excel source),when i click on "preview" , preview query result shows the null columns as mentioned in my earlier post (also in below). For OLE DB destination, after adding OLE DB destination connection manager, i selected data access mode as 'Table or view' and then 'name of the table or the view', clicked on 'NEW', then new table is created (table name and columns taken automatically ) and when i click on "View existing data" , preview query result shows the null columns as mentioned in my earlier post (also in below). But this null values columns are not there is excel sheet (in excel sheet, only 3 columns has data and remaining columns are empty(no data,even null values are not there))). NULL Country Name Actor Name ActorGender NULL NULL NULL United States Adam Sandler Male NULL NULL NULL United States Adrien Brody Male NULL NULL Please, suggest how to remove this colummns which are nulls Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Thank you for your response.
I have taken 'Excel source' task and 'OLE DB Destination' task used.
The table is created at the time SSIS package execution, as mentioned below.

After adding Excel connection manager, when i right click on Excel source (edit excel source),when i click on "preview" , preview query result shows the null columns as mentioned in my earlier post (also in below).

For OLE DB destination, after adding OLE DB destination connection manager, i selected data access mode as 'Table or view' and then 'name of the table or the view', clicked on 'NEW', then new table is created (table name and columns taken automatically ) and when i click on "View existing data" , preview query result shows the null columns as mentioned in my earlier post (also in below). But this null values columns are not there is excel sheet (in excel sheet, only 3 columns has data and remaining columns are empty(no data,even null values are not there))).

NULL Country Name Actor Name ActorGender NULL NULL
NULL United States Adam Sandler Male NULL NULL
NULL United States Adrien Brody Male NULL NULL

Please, suggest how to remove this colummns which are nulls

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/13/2015 [Member] [MVP] Platinum | Points: 25

0	1. Go to Flat File Source Editor by double-click on Flat File Source Task 2. choose 'Columns' option in the left pane 3. There you can remove the NULL Column names by unchecking the check box of unwanted NULL columns 4. Then drag & Drop OLE DB Destination task, create Table with only three columns (Country Name, Actor Name, ActorGender) Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

1. Go to Flat File Source Editor by double-click on Flat File Source Task
2. choose 'Columns' option in the left pane
3. There you can remove the NULL Column names by unchecking the check box of unwanted NULL columns
4. Then drag & Drop OLE DB Destination task, create Table with only three columns (Country Name, Actor Name, ActorGender)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 4/14/2015 [Member] Starter | Points: 25

0	Hi, Thank you for your response. I am getting data in excel sheet. Some times between rows in excel sheet, there will be empty space. This empty space in excel sheet will be loaded as Empty space or NULL to OLEDB destination. How to remove Empty space or NULL in Data flow task. In sql server, by using below query ( entered query in OLEDB destination editor ) i am able to remove Empty space or NULL select * from actortable where actorname is not NULL except select * from actortable where actorname =' ' but except keyword is not working in OLEDB destination editor, getting error. Please suggest , How to remove Empty space or NULL before loading data into table ? Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Thank you for your response.

I am getting data in excel sheet. Some times between rows in excel sheet, there will be empty space. This empty space in excel sheet will be loaded as Empty space or NULL to OLEDB destination.

How to remove Empty space or NULL in Data flow task.

In sql server, by using below query ( entered query in OLEDB destination editor ) i am able to remove Empty space or NULL

select * from actortable where actorname is not NULL
except
select * from actortable where actorname =' '

but except keyword is not working in OLEDB destination editor, getting error.

Please suggest , How to remove Empty space or NULL before loading data into table ?

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srihariblr12 on: 4/24/2015 [Member] Starter | Points: 25

0	Hi Bandi Please help me to solve below query 1. Table will log the websites traffic, table has website_name,page name,IP address, visited date columns. Write a query to get website names visited in last 30 days,total number of visit for each website,total number of unique page view, total number of unique visitors (using IP address). 2. Employee table has Employee ID,Employee name,Joining Salary columns. Salary_hike table columns are Employee ID,Salary Hike percentage,Year . Write a query to get total salary paid to employee during 2000 to 2015 year. Please help me Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Bandi
Please help me to solve below query

1. Table will log the websites traffic, table has website_name,page name,IP address, visited date columns. Write a query to get website names visited in last 30 days,total number of visit for each website,total number of unique page view, total number of unique visitors (using IP address).

2. Employee table has Employee ID,Employee name,Joining Salary columns. Salary_hike table columns are Employee ID,Salary Hike percentage,Year .
Write a query to get total salary paid to employee during 2000 to 2015 year.

Please help me

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 25

0	1. select websiteName, count(visit_date), count(distinct page_name), count( distinct IP_address) from website traffic where visit_date >= dateadd( dd, -30, getdate()) group by websiteName 2. select employeeID, SUM( JoiningSalarysalaryHikePercantage) from employee e join emploSalary_Hike eh on e.employeeID= eh.employeeID where year between 2000 and 2015 Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

1.

select websiteName, count(visit_date), count(distinct page_name), count( distinct IP_address)
from website traffic
where visit_date >= dateadd( dd, -30, getdate())
group by websiteName

2.

select employeeID, SUM( JoiningSalary*salaryHikePercantage)
from employee e
join emploSalary_Hike eh on e.employeeID= eh.employeeID
where year between 2000 and 2015

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts