Converting a table containing Records of irregualar number of fields to a normal Table

[inetgnu]

I have a Table imported from text file containing Records of books.
BUT the fields and data
value is entered as the First and Second Column (ColA, ColB). The
records are separated by 2 empty rows. Some fields are missing for
some records: Some records may miss the 'author' whereas some records
may miss some other fields, say ,'subject' AND 'totalpage'.

The Objective is Making it as a "Normal" Table (Vertical Columns and
Horizontal Rows) by tranforming the record horizontally with Each
field in one column with missing field blanked so that all the field
are
aligned in a column e.g. colA for 'Title', colB for 'Author' with
each individual information of a particular book in s single
horizontal row. (See Figures 1 & 2)


How can we convert varying length Vertically-aligned records (Figure
1) to Horizontal Rows of list (tabular) (Figure 2)


(Figure 1- Three records: varying length- subject & totalpage
missing)
==================================================
(Coulmn A) (Column B)
Title Intro to Cpt
Author James, Page
publisher M-HILL
subject CPT
totalpage 311
isbn 123

(Coulmn A) (Column B) <-- Subject % totalpage missing
Title Basic A/C
Author Ben William
publisher JWS
isbn 721

(Coulmn A) (Column B) <-- Author Missing
Title Modern Phy
publisher P-HALL
subject PHY
totalpage 466
isbn 265


(Figure 2: (Objective) "Normal" table with each detail of a individual
book listed in one row
horizontally)
============================================
Title Author publisher subject
totalpage isbn
---------------- ------------------- -------------
---------- ------------- ------
Intro to Cpt James, Page M-HILL CPT 311
123
Basic A/C Ben William
JWS 721
Modern Phy P-HALL PHY 466
265


Thank in advance

 

[Ken Sheridan]

In a relational database tables are sets of rows. By definition sets do not
have any intrinsic order, so the statement "The records are separated by 2
empty rows" does not have any real meaning in terms of the set of rows. So,
just iterating through the table and creating a row in a new table from each
'block' of rows is not necessarily going to work. However, you might well be
able to do so by first adding another column of autonumber data type to the
table, ColKey say. Do this and then check to see that the rows are
numbered, including the empty rows, so that the sequence follows the order in
which you'll need to iterate through the table.

Assuming that's the case then the next thing to check is that the empty rows
have Null in ColA, rather than a zero-length string. If the column's Allow
Zero Length property is False (No) in table design and its Required property
is also False then its reasonable to assume that they are Null. The code
below assumes this to be the case, but can be easily amended if they contain
zero-length strings.

The next step is to add another column to the table, RecNo say, of long
integer number data type into which values will be inserted to identify each
'record', i.e. each block of rows which will be inserted into a single row in
the new table.

Create the new empty table, Books, with columns Title, Author, Publisher
etc. Make them all text data type for the moment. Add an autonumber column
BookID as the table's primary key.

The first stage of the process is to iterate through the original table,
which I'll call BookList, in ColKey order and insert values into the RecNo
column. This will require a little VBA function in a standard module like so:

Public Function InsertRecNo()

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngRecNo As Long

strSQL = _
"SELECT ColA,RecNo " & _
"FROM BookList " & _
"ORDER BY ColKey"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic

With rst
Do While Not .EOF
If Not IsNull(.Fields("ColA")) Then
lngRecNo = lngRecNo + 1
End If

Do While Not IsNull(.Fields("ColA"))
On Error Resume Next
.Fields("RecNo") = lngRecNo
If Err.Number = 3021 Then
Exit Function
Else
If Err.Number <> 0 Then
MsgBox Err.Description
Exit Function
End If
End If
On Error GoTo 0
.MoveNext
Loop
.MoveNext
Loop
End With

End Function

After you call this function the RecNo column should contain sets of
identical numbers from 1 onwards each number identifying a block of rows
making up a book 'record'.

Next you need to call another function to append one row to the new Books
table for each block of rows in BookList:

Public Function AppendBooks()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim lngTotalBooks As Long
Dim varTitle As Variant
Dim varAuthor As Variant
Dim varPublisher As Variant
Dim varTotalPage As Variant
Dim varISBN As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

lngTotalBooks = DMax("RecNo", "BookList")

For n = 1 To lngTotalBooks
varTitle = DLookup("ColB", "BookList", "ColA = ""Title"" AND RecNo =
" & n)
varAuthor = DLookup("ColB", "BookList", "ColA = ""Author"" AND RecNo
= " & n)
varPublisher = DLookup("ColB", "BookList", "ColA = ""Publisher"" AND
RecNo = " & n)
varTotalPage = DLookup("ColB", "BookList", "ColA = ""Totalpage"" AND
RecNo = " & n)
varISBN = DLookup("ColB", "BookList", "ColA = ""ISBN"" AND RecNo = "
& n)

strSQL = _
"INSERT INTO Books" & _
"(Title,Author,Publisher,TotalPage,ISBN) " & _
"VALUES(" & _
IIf(IsNull(varTitle), "NULL", """" & varTitle & """") & "," & _
IIf(IsNull(varAuthor), "NULL", """" & varAuthor & """") & "," & _
IIf(IsNull(varPublisher), "NULL", """" & varPublisher & """") &
"," & _
IIf(IsNull(varTotalPage), "NULL", """" & varTotalPage & """") &
"," & _
IIf(IsNull(varISBN), "NULL", """" & varISBN & """") & ")"

cmd.CommandText = strSQL
cmd.Execute
Next n

End Function

Some longer lines of the above code may be split over two lines when you
view this in your newsreader, so watch out for this and correct it if
necessary when you paste the code into the VBA window.

Ken Sheridan
Stafford, England

 

[inetgnu]

In a relational database tables are sets of rows. By definition sets do not
have any intrinsic order, so the statement "The records are separated by 2
empty rows" does not have any real meaning in terms of the set of rows. So,
just iterating through the table and creating a row in a new table from each
'block' of rows is not necessarily going to work. However, you might well be
able to do so by first adding another column of autonumber data type to the
table, ColKey say. Do this and then check to see that the rows are
numbered, including the empty rows, so that the sequence follows the order in
which you'll need to iterate through the table.

Assuming that's the case then the next thing to check is that the empty rows
have Null in ColA, rather than a zero-length string. If the column's Allow
Zero Length property is False (No) in table design and its Required property
is also False then its reasonable to assume that they are Null. The code
below assumes this to be the case, but can be easily amended if they contain
zero-length strings.

The next step is to add another column to the table, RecNo say, of long
integer number data type into which values will be inserted to identify each
'record', i.e. each block of rows which will be inserted into a single row in
the new table.

Create the new empty table, Books, with columns Title, Author, Publisher
etc. Make them all text data type for the moment. Add an autonumber column
BookID as the table's primary key.

The first stage of the process is to iterate through the original table,
which I'll call BookList, in ColKey order and insert values into the RecNo
column. This will require a little VBA function in a standard module like so:

Public Function InsertRecNo()

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngRecNo As Long

strSQL = _
"SELECT ColA,RecNo " & _
"FROM BookList " & _
"ORDER BY ColKey"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic

With rst
Do While Not .EOF
If Not IsNull(.Fields("ColA")) Then
lngRecNo = lngRecNo + 1
End If

Do While Not IsNull(.Fields("ColA"))
On Error Resume Next
.Fields("RecNo") = lngRecNo
If Err.Number = 3021 Then
Exit Function
Else
If Err.Number <> 0 Then
MsgBox Err.Description
Exit Function
End If
End If
On Error GoTo 0
.MoveNext
Loop
.MoveNext
Loop
End With

End Function

After you call this function the RecNo column should contain sets of
identical numbers from 1 onwards each number identifying a block of rows
making up a book 'record'.

Next you need to call another function to append one row to the new Books
table for each block of rows in BookList:

Public Function AppendBooks()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim lngTotalBooks As Long
Dim varTitle As Variant
Dim varAuthor As Variant
Dim varPublisher As Variant
Dim varTotalPage As Variant
Dim varISBN As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

lngTotalBooks = DMax("RecNo", "BookList")

For n = 1 To lngTotalBooks
varTitle = DLookup("ColB", "BookList", "ColA = ""Title"" AND RecNo =
" & n)
varAuthor = DLookup("ColB", "BookList", "ColA = ""Author"" AND RecNo
= " & n)
varPublisher = DLookup("ColB", "BookList", "ColA = ""Publisher"" AND
RecNo = " & n)
varTotalPage = DLookup("ColB", "BookList", "ColA = ""Totalpage"" AND
RecNo = " & n)
varISBN = DLookup("ColB", "BookList", "ColA = ""ISBN"" AND RecNo = "
& n)

strSQL = _
"INSERT INTO Books" & _
"(Title,Author,Publisher,TotalPage,ISBN) " & _
"VALUES(" & _
IIf(IsNull(varTitle), "NULL", """" & varTitle & """") & "," & _
IIf(IsNull(varAuthor), "NULL", """" & varAuthor & """") & "," & _
IIf(IsNull(varPublisher), "NULL", """" & varPublisher & """") &
"," & _
IIf(IsNull(varTotalPage), "NULL", """" & varTotalPage & """") &
"," & _
IIf(IsNull(varISBN), "NULL", """" & varISBN & """") & ")"

cmd.CommandText = strSQL
cmd.Execute
Next n

End Function

Some longer lines of the above code may be split over two lines when you
view this in your newsreader, so watch out for this and correct it if
necessary when you paste the code into the VBA window.

Ken Sheridan
Stafford, England

Thank you very much. The code performs the task successfully.

 

[David Portas]

I have a Table imported from text file containing Records of books.
BUT the fields and data
value is entered as the First and Second Column (ColA, ColB). The
records are separated by 2 empty rows. Some fields are missing for
some records: Some records may miss the 'author' whereas some records
may miss some other fields, say ,'subject' AND 'totalpage'.

The Objective is Making it as a "Normal" Table (Vertical Columns and
Horizontal Rows) by tranforming the record horizontally with Each
field in one column with missing field blanked so that all the field
are
aligned in a column e.g. colA for 'Title', colB for 'Author' with
each individual information of a particular book in s single
horizontal row. (See Figures 1 & 2)

How can we convert varying length Vertically-aligned records (Figure
1) to Horizontal Rows of list (tabular) (Figure 2)

Do you mean that the data source shown in (1) is actually a SQL table
with two columns and without a key? If so, then what you are asking
looks to be impossible. There is nothing in your table to specify
which "fields" defined in column A are supposed to be related to which
other fields to form a complete record. Tables have no logical
ordering so the order you wrote them out in your post is irrelevant.

What you could do is create a staging table with a key (a row number
for example) and populate that key at the time you load the data. You
could do this using DTS or Integration Services.

Every table should have a key. There are no exceptions.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Todos Menos [MSFT]]

yeah you'll need to parse this by hand

I swear I could do it in about 10 minutes

-Todos

 

[Tony Toews [MVP]]

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm