Restructuring repeating data blocks

[Jamesss]

Hello,

I have a question on restructuring imported data from excel. The data I have is of excel format shown in the file Before.xls.

The data is grouped in terms of task/doc no, with component tasks beneath. The number of grouped entries will grow for additional tasks.

How would I import and columnate to an Access table format like that in After.xls. The problem I have is that I'm unsure of how Access would recognise the start of a new group (Task/Doc No.) and restructure accordingly.


Thanks
James

 

[The_Doc_Man]

I might consider doing something like this:

For a spreadsheet that has some things that repeat a lot but also some non-repeating details, where I wanted to import this rationally, the first thing I would do is import the raw spreadsheet to a table with no PK.I would then remove any records that were really just headers.

Next, impose some non-unique indexes on the fields that will be the one side of a one to many relationships. (This is for speed, not a more stringent need).

Now, write a query to select unique entries for the field that will be one of the entries in a one-side table. Make this query into an Insert query in a target table. Populate the table with the data extracted that way.

Now write another query to do a similar thing, this time using the information to look up whatever you are using as a key in that first table. Eventually you will write a query that stores the detail info in a table with required keys.

Stated another way, don't look to do this in a single, monolithic action. Do it piecemeal.

 

[DCrake]

If you can do it using VBA here is a brief solution

Function LoadExcelSpreadsheet(xlfilename)

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim cValue As String

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(1)


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ImportTable")

iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

With ActiveSheet.UsedRange
    iCol = .Cells(1, 1).Column + .Columns.Count - 1
    iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

'iRow is the last row in the worksheet that contains data

For nRow = 1 To irow
   cValue = oSheet.range("A" & nRow).Value
   If cValue <> "" Or cValue <> ([COLOR="Green"]Data heading Text[/COLOR]) Then
     'This is a row that contains data you want to import
     rs.AddNew
     rs(FieldName) = oSheet.Range("A" & nRow).Value
     rs(FieldName) = oSheet.Range("B" & nRow).Value  
     etc
     rs.Update
   End If
Next

rs.Close
oExcel.Quit

Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set rs = Nothing

All aircode not tested.

In essense it open up the named workbook and finds out how many rows contain data. Then is reads the contents of column A to see if it is either not null or is not a heading. If it is data is copies the values in each cell to the coressponding field in the import table.

 

[Jamesss]

Thank-you DCrake and The_Doc_man. This is somewhat more complex than I was expecting.


James

 

[Jamesss]

I also wanted to recognise the Document No. and populate this value into a separate column for each data entry.
I made some additions to DCrake's code and have some questions:

For nRow = 1 To irow
   ''' Recognise Document No.
   cGroupValue = oSheet.range("B" & nRow).Value
 
   If Instr(1, cGroupValue , "Document No:") = 1
      ''' Is this a valid way to keep the current Document No. row static until the next new Doc No.?
      cDNbr_Row = nRow - irow
      '''Extract Doc No for values after the colon
      cDNbr = Right(cGroupValue, Len(cGroupValue) - InStr(cGroupValue, ":"))
   End If
 
   cValue = oSheet.range("A" & nRow).Value
   Else If cValue <> "" Or cValue <> (Data heading Text) Then
      'This is a row that contains data you want to import
      rs.AddNew
      ''' To populate the DB with the recurring Document No.
      rs(DocNo_FieldName) = oSheet.Range("A" & cDNbr_Row).Value '''Should enter the same Doc Number for each value.
      rs(FieldName) = oSheet.Range("B" & nRow).Value
      rs(FieldName) = oSheet.Range("C" & nRow).Value 
      etc
      rs.Update
   End Else If
Next

I'm not convinced the way I have kept position of the Document No. static is correct.
Basically, I want to maintain the position constant until the next "Document No:" is detected.
Comments/Suggestions?

Thanks
James

 

[Jamesss]

In the method DCrake suggested, SpecialCells(xlCellTypeLastCell) when called from Access indicates that the variable xlCellTypeLastCell is not defined. I guess this needs to be defined locally in Access. I read elsewhere that it depends on the version of Excel such that,

Private Const xlCellTypeLastCell As Long = 11

Can someone explain what this value refers to and how does it changes with versions?

EDIT:
Also, when I attempt to reference "ActiveSheet.UsedRange" in Access it shows the ActiveSheet property it says it an undefined variable. What should I define this property as?

 

[gemma-the-husky]

Ive not looked, but if your spresadsheet is formatted with blanks under certain columns to indicate that the data is identical to the data in the preceeding row(s) then you need to be careful how you handle it

if you just load the table into Access, access may not maintain the same record order - and you can lose track of record order

if its just a one-off, i would see how difficult it is to fill in all the blank cells, so then the order doesnt matter -

alternatively, add a row count column to the excel data, so you can reconstruct the original order

you see, access doesnt care about row order - it assumes each record to be fully identified on its own (ie one facet of normalisation)

 

[Jamesss]

gemma-the-husky - It shouldn't be a problem since I am generating an extra column called "Document Number". The the one value in the one-to-many relation in Excel I am changing so that it many-to-many. I guess that would allow the data to be reseorted into the blocks.

Thanks