insert two raw as columns from excel

Question

Hi i want to insert some data from excel file i don't have the idea how to do that so the excel file goes like this :

Empid| 1/01/2019|2/01/2019 | 3/01/2019
-------------------------------------------
1    |    A     |    B     |    A
2    |    B     |    A     |    B
3    |    B     |    C     |    C
4    |    A     |    A     |    A

and the letters {A , B , C} stands for the shift type , i want to insert the table from excel file to the database to look like this:

Empid | Date     | Shift
---------------------
 1    |1/01/2019 | A
 1    |2/01/2019 | B
 1    |3/01/2019 | A
 2    |1/01/2019 | B
 2    |2/01/2019 | A
 2    |3/01/2019 | B
 3    |1/01/2019 | B
 3    |2/01/2019 | C
 3    |3/01/2019 | C
 4    |1/01/2019 | A
 4    |2/01/2019 | A
 4    |3/01/2019 | A

thank you very much.

Sounds cool. Let us know when you run into a specific issue. We are not here to write code for you. — Qirel, Jan 30 '19 at 10:39
Maybe take a look at [Php Office](https://github.com/PHPOffice). It won't be easy making sense of it, but it will likely help you — Eugene Anisiutkin, Jan 30 '19 at 10:45
Thank you i will do that now at least i have a starting point now :) — hady alzpen, Jan 30 '19 at 10:49
I wrote code for something very similar just a short while ago. https://stackoverflow.com/questions/54252775/how-can-i-transpose-this-data-set-into-this-specific-order/54253406#54253406 — Variatus, Jan 30 '19 at 10:55
When storing data in a relational database, be sure to use correct data types for that data. — Strawberry, Jan 30 '19 at 11:12
@Variatus The code worked perfectly as i wanted still one thing i need to add it gave me the empid with there Shift Column i also want the date with them how can i do that , again thank you very very much. — hady alzpen, Feb 03 '19 at 11:45

score 0 · Accepted Answer · answered Feb 05 '19 at 06:45

Here is the code amended to suit your purpose.

Sub TransposeData()

    Const EmpidClm As String = "A"              ' change as applicable
    Const DateClm As String = "B"
    Const ShiftClm As String = "C"

    Dim Rng As Range
    Dim Arr As Variant, Pos As Variant
    Dim Rl As Long, Cl As Long
    Dim R As Long, C As Long
    Dim i As Long

    With ActiveSheet
        Cl = .UsedRange.Columns.Count - .UsedRange.Column + 1
        Rl = .Cells(.Rows.Count, Columns(EmpidClm).Column).End(xlUp).Row
        Set Rng = Range(.Cells(1, EmpidClm), .Cells(Rl, Cl))
    End With
    Arr = Rng.Value
    ReDim Pos(1 To (UBound(Arr) * UBound(Arr, 2)), 1 To 3)

    i = 1
    For C = 1 To 3
        Pos(i, C) = Array("Empid", "Date", "Shift")(C - 1)
    Next C

    For R = 2 To UBound(Arr)
        For C = 2 To UBound(Arr, 2)
            i = i + 1
            Pos(i, 1) = Arr(R, 1)
            Pos(i, 2) = Arr(1, C)
            Pos(i, 3) = Arr(R, C)
        Next C
    Next R

    R = Rl + 5                                  ' write 5 rows below existing data
    Set Rng = ActiveSheet.Cells(R, EmpidClm).Resize(i, 3)
    Rng.Value = Pos
End Sub

insert two raw as columns from excel

1 Answers1