Import Excell Data into Access

Mitch · February 10th, 2005, 03:25 PM

I am trying to import some data from an Excell spreadsheet into Access, but not every row contains the same data.

Basically I have one row that contains the Parent information and then rows directly underneith it that contain the child data, and the child data can be in various number of rows, so it is not constant.

1. Each Parent row has the word "Transformer" or "Transformer 2 Data" in column C.
2. Each Child row has a number from 1 to x in column C, and is directly below its parent.
3. Each row has its own ID in column A

I need to import the data and have the child data be linked to the Parent data.

I was thinking of importing into two Access tables one for the Parent and one for the Child.

Is there some way to automate this so that the child records all reference the parents ID? Maybe using VBA/macro in Excell or import the whold think into Access and do something in VBA??

Code:

A            B               C           D
ID    SUBSTATION    RECLOSER #    TYPE/RATING
===================================================================
5195    Grape 12470    Transformer 2 DATA (EAST)    "ABB 22.4 @ 65"
5196    Grape 12470    1    ABB
5197    Grape 12470    2    ABB
5198    Grape 12470    3    ABB
5199    Grape 12470    4    ABB

5200    Grape 12470    Transformer 1 DATA (WEST)    "ASEA 22.4 @ 65"
5201    Grape 12470    5    ABB
5202    Grape 12470    6    ABB
5203    Grape 12470    7    ABB
5204    Grape 12470    8    ABB

5206    Logan 4160/12470    Transformer 1 DATA    "Waukesha 22.4 @ 65"
5207    Logan 4160    1    W
5208    Logan 4160    2    W
5209    Logan 4160    3    W
5210    Logan 4160    4    ABB
5211    Logan 4160    5    AC
5212    Logan 4160    6    ABB
5213    Logan 4160    7    ABB

5214    Russ Street 12470    Transformer 1 DATA    "US Tran 10.5 @ 65"
5215    Russ Street 4160    3    SA
5216    Russ Street 12470    4    AC
5217    Russ Street 12470    5    W

5218    Russ Street 4160/12470    Transformer 2 DATA    "ABB 14.0 @ 65"
5219    Russ Street 4160    1    AC
5220    Russ Street 4160    2    AC
5221    Russ Street 4160    6    W
5222    Russ Street 4160    7    W
5223    Russ Street 4160    8    AC

JpJoe · February 11th, 2005, 05:49 AM

Hi Mitch, You can create two tables and use these subs to get the data into access. Note - the table structures, adjust to your requirements. Also these routines assume that the data does not change! Post back If you need any help.

Code starts - - - - - -
Sub ImportData()
Dim strSheetName As String
Dim strFilename As String

strSheetName = "Data" 'Sheet name
strFilename = "C:\YourWorkbook.xls" 'Filrname and path

ImportExcelData strFilename, strSheetName ' call import sub

End Sub

Sub ImportExcelData(WorkbookPathName As String, SheetName As String)
'This routine needs reference to the Excel 9.0 object library
'Also note that this routine is hard coded to the task of importing
'the excel data in your fixed format. If the format changes then this
'routine will encounter problems.

On Error GoTo ImportExcelData_Err
'================
Dim rstParent As DAO.Recordset 'Declare Database objects
Dim rstChild As DAO.Recordset
Dim db As DAO.Database

Dim xls As Excel.Application 'Declare Excel Objects
Dim wkb As Excel.Workbook
Dim wkSh As Excel.Worksheet

Dim pID As Long ''We use the Ecell count variable to Exit sub if more than 2 rows are empty

Set xls = New Excel.Application 'Create Excel application and open specified workbook
xls.Workbooks.Open (WorkbookPathName)

Set wkb = xls.ActiveWorkbook 'Create references to the excel objects
Set wkSh = wkb.Sheets(SheetName)
wkSh.Activate 'and activate the worksheet

Set db = CurrentDb
Set rstParent = db.OpenRecordset("tbl_Parent", dbOpenDynaset)
Set rstChild = db.OpenRecordset("tbl_Child", dbOpenDynaset)

wkSh.Range("A3").Select 'First Row Of DATA (not headers or '============ parts)

Do While eCellCount < 2

    If Not IsNumeric(xls.ActiveCell.Offset(0, 2)) Then 'If the cell is NOT numeric then must be a parent
        rstParent.AddNew
        rstParent!Parent_ID = Nz(xls.ActiveCell, 0)
        rstParent!Parent_Substation = xls.ActiveCell.Offset(0, 1)
        rstParent!Parent_Recloser = Trim(Nz(xls.ActiveCell.Offset(0, 2), "None Specified!"))
        rstParent!Parent_Rating = Trim(Nz(xls.ActiveCell.Offset(0, 3), "None Specified"))
        pID = rstParent!Parent_DBID 'Create reference to this parent record
        rstParent.Update 'and update the recordset
        eCellCount = 0
    Else 'Add a child record to the last parent created
        rstChild.AddNew
        rstChild!Child_Parent_DBID = pID 'This child must belong to the last Parent rec created
        rstChild!Child_Number = xls.ActiveCell.Offset(0, 2)
        rstChild!Child_Rating = xls.ActiveCell.Offset(0, 3)
        rstChild.Update
    End If

    xls.ActiveCell.Offset(1, 0).Select 'Move to next row
    If IsEmpty(xls.ActiveCell) Then eCellCount = eCellCount + 1 'Increment the Empty Cell Count var

Loop

MsgBox "Data Loaded!"

xls.Quit
rstChild.Close
rstParent.Close
Set xls = Nothing
Set rstParent = Nothing
Set rstChild = Nothing

'================
Exit_ImportExcelData:
    xls.Quit
    rstParent.Close
    rstChild.Close
    Set xls = Nothing
    Set rstParent = Nothing
    Set rstChild = Nothing
    Exit Sub
ImportExcelData_Err:
    Select Case Err.Number
        Case 0 'Add Exceptions

        Case Else
            MsgBox "An unhandled error has occurred! (" & Err.Number & ")" & vbCr & vbCr & Err.Description _
                , vbExclamation + vbOKOnly _
                , "Import Excel Data" _
                , Err.HelpFile _
                , Err.HelpContext
            Resume Exit_ImportExcelData
    End Select
End Sub

Code ends - - - - - -

hth

Jon

JpJoe · February 11th, 2005, 05:51 AM

Mitch, forgot to mention you need a reference to the MS DAO library aswell(make sure this is prioritised above ActiveX Data library)

Jon

rjweers · February 11th, 2005, 08:45 AM

Mitch,

To get the data you want, you could use a union query. E.g. (Not exact syntax)...

Select ID, Substation, Recloser, TypeRating, 1 as SetOrder From tblParent
UNION
Select Id, Substation, Indicator, FourthColumn, 2 as SetOrder From tblChild
ORDER BY SetOrder;

Not sure about there being a fourth column to your child. If not, just use Null instead of FourthColumn.

Are you using MS Query to fetch the data from Access? If so, you might want to create a temporary table out of this suggestion. Just change the above to an Append for your table. I've found that MS Query doesn't use union queries well. But the nice thing about MS Query is that you can refresh your Excel sheet without writing code.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Mitch · February 15th, 2005, 10:29 PM

99% there! :)

I wrote the code below and it works great, except for when the data sets are in reverse order; i.e. Transformer 2 comes before Transformer 1. Not sure how to handle that one. I thought about checking that the two substation fields were the same (at least maybe the first 4 or 5 characters, since the ending numbers--suffix--probably won't match) but don't know how to code for that.

Code:

Private Sub btnImportData_Click()
On Error GoTo Err_btnImportData_Click

DoCmd.Hourglass True

'**Reference DAO 3.5
'**create two tables

Dim recordset1_AllData As Recordset 'recordset1  Sheet1
Dim recordset2_Parent As Recordset 'recordset2  T_Parent
Dim recordset3_Child As Recordset 'recordset3   T_Child
Dim recordset4_Location As Recordset
Dim lID As Long
Dim lLocationGroup As Long
Dim strLocation As String
Dim lLocationID As Long
Dim strCurrentSubstation As String
Dim strComonSubstation As String

'**Iterate through the dataset

'**Set recordset1_AllData = CurrentDb.OpenRecordset("Sheet1")
Set recordset1_AllData = CurrentDb.OpenRecordset("DATA")
Set recordset2_Parent = CurrentDb.OpenRecordset("T_Parent")
Set recordset3_Child = CurrentDb.OpenRecordset("T_Child")
Set recordset4_Location = CurrentDb.OpenRecordset("T_Locations")    'with an S

Do Until recordset1_AllData.EOF
    If IsNull(recordset1_AllData.Fields("RECLOSER #")) Then
        DoCmd.Hourglass False
        MsgBox "End of File"   'Null Found
        Exit Sub
        'recordset1_AllData.MoveNext
        'Debug.Print recordset1_AllData.Fields("RECLOSER #")
    End If
    If Not IsNumeric(recordset1_AllData.Fields("RECLOSER #")) Then
        sRecloser = recordset1_AllData.Fields("RECLOSER #")
        lID = recordset1_AllData.Fields("SORT AID")
 '**Need to figure out how to handle if these are in reverse order probaly around here.   
        If InStr(sRecloser, "Transformer DATA") > 0 Then  'This is to get the pair of transformers the same ID to link with.
            lLocationGroup = lID
        ElseIf InStr(sRecloser, "Transformer 1 DATA") > 0 Then
            lLocationGroup = lID
        End If

'**Begin Find location.
strCurrentSubstation = recordset1_AllData.Fields("SUBSTATION")
Do Until recordset4_Location.EOF
    strComonSubstation = recordset4_Location.Fields("SUBSTATION")
    If InStr(strCurrentSubstation, strComonSubstation) > 0 Then  'This is to get the location to link with.
        lLocationID = recordset4_Location.Fields("LocationID")
        recordset4_Location.MoveFirst   'Otherwise we stop at the end and all the rest will get this last number
        Exit Do
    End If
recordset4_Location.MoveNext
Loop
'**End Find Location

'**Begin fix CRLF
x = recordset1_AllData.Fields("TYPE_RATING")
If IsNull(x) Then
    t = "None Specified"
Else
    t = IIf(InStr(x, Chr(10)), Replace(x, Chr(10), " "), x)
End If
'**End fix CRLF

        With recordset2_Parent
            .AddNew
            .[ID] = lID
            .[SORT AID] = lLocationGroup     'recordset1_AllData.Fields("SORT AID")
            .[SUBSTATION] = lLocationID     'recordset1_AllData.Fields("SUBSTATION")
            .[RECLOSER #] = Trim(recordset1_AllData.Fields("RECLOSER #"))   'trimmed to take away the leading space in some cases
            .[TYPE_RATING] = t 'Change In Imported Data
            .[Date] = recordset1_AllData.Fields("DATE")
            .[OUTSIDE TEMP] = recordset1_AllData.Fields("OUTSIDE TEMP") 'Change In Imported Data
            .[TRANSFORMER OIL TEMP] = recordset1_AllData.Fields("TRANSFORMER OIL TEMP")
            .[TRANSFORMER WINDING TEMP] = recordset1_AllData.Fields("TRANSFORMER WINDING TEMP")
            .[TRANSFORMER PRESSURE] = recordset1_AllData.Fields("TRANSFORMER PRESSURE")
            .[REGULATOR 1  ACTUAL] = recordset1_AllData.Fields("REGULATOR 1  ACTUAL")
            .[REGULATOR 1  RAISE] = recordset1_AllData.Fields("REGULATOR 1  RAISE")
            .[REGULATOR 1  LOWER] = recordset1_AllData.Fields("REGULATOR 1  LOWER")
            .[REGULATOR 1  COUNTER] = recordset1_AllData.Fields("REGULATOR 1  COUNTER")
            .[REG 1 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 1 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION] = recordset1_AllData.Fields("DESIGN OPERATION")
            .[PERCENT REMAINING] = recordset1_AllData.Fields("PERCENT REMAINING")
            .[REGULATOR 2  ACTUAL] = recordset1_AllData.Fields("REGULATOR 2  ACTUAL")
            .[REGULATOR 2  RAISE] = recordset1_AllData.Fields("REGULATOR 2  RAISE")
            .[REGULATOR 2  LOWER] = recordset1_AllData.Fields("REGULATOR 2  LOWER")
            .[REGULATOR 2  COUNTER] = recordset1_AllData.Fields("REGULATOR 2  COUNTER")
            .[REG 2 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 2 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION1] = recordset1_AllData.Fields("DESIGN OPERATION1")   'Change In Imported Data
            .[PERCENT REMAINING1] = recordset1_AllData.Fields("PERCENT REMAINING1") 'Change In Imported Data
            .[REGULATOR 3  ACTUAL] = recordset1_AllData.Fields("REGULATOR 3  ACTUAL")
            .[REGULATOR 3  RAISE] = recordset1_AllData.Fields("REGULATOR 3  RAISE")
            .[REGULATOR 3  LOWER] = recordset1_AllData.Fields("REGULATOR 3  LOWER")
            .[REGULATOR 3  COUNTER] = recordset1_AllData.Fields("REGULATOR 3  COUNTER")
            .[REG 3 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 3 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION2] = recordset1_AllData.Fields("DESIGN OPERATION2")   'Change In Imported Data
            .[PERCENT REMAINING2] = recordset1_AllData.Fields("PERCENT REMAINING2") 'Change In Imported Data
            .[As Found AMPS PHASE A] = recordset1_AllData.Fields("As Found AMPS PHASE A")
            .[PEAK AMPS PHASE A] = recordset1_AllData.Fields("PEAK AMPS PHASE A")
            .[As Found AMPS PHASE B] = recordset1_AllData.Fields("As Found AMPS PHASE B")
            .[PEAK AMPS PHASE B] = recordset1_AllData.Fields("PEAK AMPS PHASE B")
            .[As Found AMPS PHASE C] = recordset1_AllData.Fields("As Found AMPS PHASE C")
            .[PEAK AMPS PHASE C] = recordset1_AllData.Fields("PEAK AMPS PHASE C")
            .[TRIP COUNTER] = recordset1_AllData.Fields("TRIP COUNTER")
            .[Neutral 'As Found' Current (calc)] = recordset1_AllData.Fields("Neutral 'As Found' Current (calc)") 'Change In Imported Data
            .[Average Amps (actual)] = recordset1_AllData.Fields("Average Amps (actual)")
            .[Average Amps (demand)] = recordset1_AllData.Fields("Average Amps (demand)")
            .[MVA] = recordset1_AllData.Fields("MVA")
            .[% of  Max Trans MVA] = recordset1_AllData.Fields("% of  Max Trans MVA")
            .[MVA (demand)] = recordset1_AllData.Fields("MVA (demand)")
            .[% of  Max Trans MVA (demand)] = recordset1_AllData.Fields("% of  Max Trans MVA (demand)")
            .[MU#] = recordset1_AllData.Fields("MU#")
            .[SERIAL #] = recordset1_AllData.Fields("SERIAL #")
            .[Capacitors Locations] = recordset1_AllData.Fields("Capacitors Locations")
            '
      End With
        recordset2_Parent.Update
    Else
        '**need to write to table two
        With recordset3_Child
            .AddNew
            .[ID] = lID
            '.[SORT AID] = lLocationGroup     'recordset1_AllData.Fields("SORT AID")
            .[SUBSTATION] = lLocationID     'recordset1_AllData.Fields("SUBSTATION")
            .[RECLOSER #] = Trim(recordset1_AllData.Fields("RECLOSER #"))
            .[TYPE_RATING] = recordset1_AllData.Fields("TYPE_RATING") 'Change In Imported Data
            .[Date] = recordset1_AllData.Fields("DATE")
            .[OUTSIDE TEMP] = recordset1_AllData.Fields("OUTSIDE TEMP") 'Change In Imported Data
            .[TRANSFORMER OIL TEMP] = recordset1_AllData.Fields("TRANSFORMER OIL TEMP")
            .[TRANSFORMER WINDING TEMP] = recordset1_AllData.Fields("TRANSFORMER WINDING TEMP")
            .[TRANSFORMER PRESSURE] = recordset1_AllData.Fields("TRANSFORMER PRESSURE")
            .[REGULATOR 1  ACTUAL] = recordset1_AllData.Fields("REGULATOR 1  ACTUAL")
            .[REGULATOR 1  RAISE] = recordset1_AllData.Fields("REGULATOR 1  RAISE")
            .[REGULATOR 1  LOWER] = recordset1_AllData.Fields("REGULATOR 1  LOWER")
            .[REGULATOR 1  COUNTER] = recordset1_AllData.Fields("REGULATOR 1  COUNTER")
            .[REG 1 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 1 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION] = recordset1_AllData.Fields("DESIGN OPERATION")
            .[PERCENT REMAINING] = recordset1_AllData.Fields("PERCENT REMAINING")
            .[REGULATOR 2  ACTUAL] = recordset1_AllData.Fields("REGULATOR 2  ACTUAL")
            .[REGULATOR 2  RAISE] = recordset1_AllData.Fields("REGULATOR 2  RAISE")
            .[REGULATOR 2  LOWER] = recordset1_AllData.Fields("REGULATOR 2  LOWER")
            .[REGULATOR 2  COUNTER] = recordset1_AllData.Fields("REGULATOR 2  COUNTER")
            .[REG 2 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 2 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION1] = recordset1_AllData.Fields("DESIGN OPERATION1")
            .[PERCENT REMAINING1] = recordset1_AllData.Fields("PERCENT REMAINING1")
            .[REGULATOR 3  ACTUAL] = recordset1_AllData.Fields("REGULATOR 3  ACTUAL")
            .[REGULATOR 3  RAISE] = recordset1_AllData.Fields("REGULATOR 3  RAISE")
            .[REGULATOR 3  LOWER] = recordset1_AllData.Fields("REGULATOR 3  LOWER")
            .[REGULATOR 3  COUNTER] = recordset1_AllData.Fields("REGULATOR 3  COUNTER")
            .[REG 3 OPERATIONS DURING LAST MONTH] = recordset1_AllData.Fields("REG 3 OPERATIONS DURING LAST MONTH")
            .[DESIGN OPERATION2] = recordset1_AllData.Fields("DESIGN OPERATION2")
            .[PERCENT REMAINING2] = recordset1_AllData.Fields("PERCENT REMAINING2")
            .[As Found AMPS PHASE A] = recordset1_AllData.Fields("As Found AMPS PHASE A")
            .[PEAK AMPS PHASE A] = recordset1_AllData.Fields("PEAK AMPS PHASE A")
            .[As Found AMPS PHASE B] = recordset1_AllData.Fields("As Found AMPS PHASE B")
            .[PEAK AMPS PHASE B] = recordset1_AllData.Fields("PEAK AMPS PHASE B")
            .[As Found AMPS PHASE C] = recordset1_AllData.Fields("As Found AMPS PHASE C")
            .[PEAK AMPS PHASE C] = recordset1_AllData.Fields("PEAK AMPS PHASE C")
            .[TRIP COUNTER] = recordset1_AllData.Fields("TRIP COUNTER")
            .[Neutral 'As Found' Current (calc)] = recordset1_AllData.Fields("Neutral 'As Found' Current (calc)")  'Change In Imported Data
            .[Average Amps (actual)] = recordset1_AllData.Fields("Average Amps (actual)")
            .[Average Amps (demand)] = recordset1_AllData.Fields("Average Amps (demand)")
            .[MVA] = recordset1_AllData.Fields("MVA")
            .[% of  Max Trans MVA] = recordset1_AllData.Fields("% of  Max Trans MVA")
            .[MVA (demand)] = recordset1_AllData.Fields("MVA (demand)")
            .[% of  Max Trans MVA (demand)] = recordset1_AllData.Fields("% of  Max Trans MVA (demand)")
            .[MU#] = recordset1_AllData.Fields("MU#")
            .[SERIAL #] = recordset1_AllData.Fields("SERIAL #")
            .[Capacitors Locations] = recordset1_AllData.Fields("Capacitors Locations")

       End With
        recordset3_Child.Update
    End If
    recordset1_AllData.MoveNext
Loop
'**Error trapping

Exit_btnImportData_Click:
    Exit Sub
    DoCmd.Hourglass False
Err_btnImportData_Click:
    DoCmd.Hourglass False
    MsgBox Err.Description

    Resume Exit_btnImportData_Click   
End Sub

I'm almost there, i can't beleive that I even got this far.