Excel column wise data save in rows

1

Hi,

I have attendance table.

Atn_No
Atn_Date
Atn_EmpNo
Atn_Type

I have excel file the data is given below.

EMPNO 1 2 3 4 5 6 7 8 9 10 11 12
935 RS RS RS RS OD OD OD OD OD OD EX EX
936 RS RS RS OD OD OD OD OD OD OD OD OD

How to save this into above table because 1, 2, 3 date as diffierent columns.

If above table is like below table then easily save into db.

EMPNO DAYS TYPE
935 1 RS
935 2 RS
935 3 RS
935 4 RS
935 5 OD
935 6 OD
935 7 OD
935 8 OD
935 9 OD
935 10 OD
935 11 EX
935 12 EX
936 1 RS
936 2 RS
936 3 RS
936 4 OD
936 5 OD
936 6 OD
936 7 OD
936 8 OD
936 9 OD
936 10 OD
936 11 OD
936 12 OD

Thanks
Basit.

2 
create table employee(
EMPNO int, 
[1] varchar(2),  
[2] varchar(2), 
[3] varchar(2),
[4] varchar(2),
 [5] varchar(2),
 [6] varchar(2),
 [7] varchar(2),
 [8] varchar(2),
 [9] varchar(2),
 [10] varchar(2),
 [11] varchar(2),
 [12] varchar(2))
 insert into employee
select '935',	'RS',	'RS',	'RS',	'RS',	'OD',	'OD',	'OD',	'OD',	'OD',	'OD',	'EX',	'EX' union
select '936',	'RS',	'RS',	'RS',	'OD',	'OD',	'OD',	'OD',	'OD',	'OD',	'OD',	'OD',	'OD' 


SELECT EMPNO, [Days], Types
FROM   
   (SELECT EMPNO, 
   [1], 
   [2], 
   [3], 
   [4], 
    [5],
    [6],
    [7],
    [8],
    [9],
    [10],
    [11],
    [12]
   FROM employee
   ) p  
UNPIVOT  
   (Types FOR [Days] IN   
      ([1], 
   [2], 
   [3], 
   [4], 
    [5],
    [6],
    [7],
    [8],
    [9],
    [10],
    [11],
    [12])  
)AS unpvt;
1 Like
3

Many Thanks, but sql read that excel column data?

Thanks
Basit.

4
  1. where is your excel file located at? on server or at another //server/files/ location?
  2. Is it a csv or xlxs
use sqlteam
go
create table employee(
EMPNO int, 
[1] varchar(2),  
[2] varchar(2), 
[3] varchar(2),
[4] varchar(2),
 [5] varchar(2),
 [6] varchar(2),
 [7] varchar(2),
 [8] varchar(2),
 [9] varchar(2),
 [10] varchar(2),
 [11] varchar(2),
 [12] varchar(2))

--from command line run the following
bcp.exe sqlteam.dbo.employee in "C:\_Personal\sqlteam\basit.csv" -T -c -t ,
5

Thanks you very much, i used .net code to import excel in grid view and store in db, but actual columns are date, and the dates is changing and we create a table hard coded columns, please help on this.

EMPNO 1-jun-18 2-jun-18 3-jun-18 4-jun-18 5-jun-18 6 and so on 7 8 9 10 11 12
935 RS RS RS RS OD OD OD OD OD OD EX EX
936 RS RS RS OD OD OD OD OD OD OD OD OD

Thanks
Basit.

6

so do you want us to provide you .net code or tsql code?

7

Many thanks, which is easiest please help.

Thanks
basit

8

Still don't understand your question. so do the column names change every time a new file comes?
Are you creating a new table with new columns dynamically?

9

Yes, column names changes every time a new files comes, every time column data type is date, that's why need to create column dynamically. Please help on this.

Thanks
Basit.

10

but is it always 12 day columns? or it could be 3 today 35 tomorrow?

11

Many Thanks for help. Its always 28, or 30 or 31 columns.

Thanks
Basit.

12

The Problem has been solved with below step.

  1. Temporary table has been created on that table import data from gridview. column was hardcode 1,2,3 upto 31

  2. After that put pivot SQL to get the data, example data look like below.

EMPNO DAYS TYPE
935 1 RS
935 2 RS
935 3 RS
935 4 RS
935 5 OD

After that design a form where add the datepicker to start the date and in button click add dateadd function and add the days column and saved in attendance table.

Many Thanks for your help.

Thanks
Basit.

1 Like