importing tables in proper format from word 2007 into access 2007

  • Thread starter Thread starter s
  • Start date Start date
S

s

The word 2007(.docx) is saved as a plain text(.txt) file first. Then,
I add a delimiter(#) to each row
manually. The tables in the document look like below

Table 1
Row 1 data#
Row 2 data#
Row 3 data#
Row 4 data#
..
..
..

Table 2
Row 1 data#
Row 2 data#
Row 3 data#
..
..
..
..

Table 3
Row 1 data#
Row 2 data#
Row 3 data#

..
..
..


and so on



Then, go into Access 2007, External Data, Text File to import the text
file data into Access choosing # as the delimiter

The table which is created in Access 2007 is as follows

Field1
Row 1 data of Table 1
Row 2 data of Table 1
Row 3 data of Table 1

..
..
..
Row 1 data of Table 2
Row 2 data of Table 2
Row 3 data of Table 2
Row 4 data of Table 2
..
..
..
Row 1 data of Table 3
Row 2 data of Table 3
Row 3 data of Table 3
Row 4 data of Table 3




whereas I want the table in Access to look like

Field 1 Field 2 Field
3 Field 4 .....Field N
Row 1 data of Table 1. Row 2 data of Table 1 Row 3 data of Table 1 Row
1 data of Table 2 .....
..
..


Can someone please advise if that can be done or do I need to develop
some VBA code for that?
If I need to use VBA code, some pointers in that direction would be
appreciated.


Thanks
 
The word 2007(.docx) is saved as a plain text(.txt) file first. Then,
I add a delimiter(#) to each row
manually. The tables in the document look like below

Table 1
Row 1 data#
Row 2 data#
Row 3 data#
Row 4 data#
.
.
.

Table 2
Row 1 data#
Row 2 data#
Row 3 data#
.
.
.
.

Table 3
Row 1 data#
Row 2 data#
Row 3 data#

.
.
.

and so on

Then, go into Access 2007, External Data, Text File to import the text
file data into Access choosing # as the delimiter

The table which is created in Access 2007 is as follows

Field1
Row 1 data of Table 1
Row 2 data of Table 1
Row 3 data of Table 1

.
.
.
Row 1 data of Table 2
Row 2 data of Table 2
Row 3 data of Table 2
Row 4 data of Table 2
.
.
.
Row 1 data of Table 3
Row 2 data of Table 3
Row 3 data of Table 3
Row 4 data of Table 3

whereas I want the table in Access to look like

Field 1                        Field 2                       Field
3                       Field 4                        .....Field N
Row 1 data of Table 1. Row 2 data of Table 1 Row 3 data of Table 1 Row
1 data of Table 2 .....
.
.

Can someone please advise if that can be done or do I need to develop
some VBA code for that?
If I need to use VBA code, some pointers in that direction would be
appreciated.

Thanks
Click to expand...

The data can't be that long as you manually added the # symbol as a
field delimiter......

If this is a one off, take your data and slip it into Excel in the
format and with the field names you want, and then import the excel
table.

If you are doing this regularly and taking data, the question is do
you have a fixed number of records each time? If so a relatively
simple VBA script can do the job. If not you are going to need to also
include an end of records delimiter as well as end of field.

Steve
 
Salad wrote:

Thanks for the response and code snippet.
Three newsgroups? Oh well.
Click to expand...

I wanted to copy to two newsgroups, but for some reason it did not
work so I
placed all groups in the To list.
With VBA, you have 3 recordsets and you want to merge them together.
Don't know if you have an autonumber, no idea if the record counts are
the same, or if there's 3 recs in the first file and 4 in the other 2.
Click to expand...

The record counts can vary, don't have an autonumbering.
Maybe a query can work, maybe better to read line by line via vba.

So lets say you did a FileImport using Docmd.TransferText.

There's only 1 column (field) in each table. I called the column
FieldName, change code below to reflect true field name

Lets say they are called table1, table2, table3. Change those names as
welll.

Lets assume the fields are something like T1R1, T1R2, T1R3, T2R1, T2R2,
etc, the T for the table number R for the row.

The tables as Table1...Table2, and Merged is the table name holding all
data from the 3 tables

YOu could have code similar to this (untested/uncompiled) in a routine
Dim intF1 As Integer
Dim intF2 As Integer
Dim intF3 as Integer

Dim intC1 As Integer
Dim intC2 as Integer
Dim intC3 as Integer

Dim s As String

Dim rst1 as Recordset
Dim rst2 as Recordset
Dim rst3 as Recordset
Dim rst as recordset

'Change to reflect your table names
Set rst = currentdb.Openrecordset("Merged",dbopendynaset)
Set rst1 = Currentdb.Openrecordset("Table1",dbopensnapshot)
Set rst2 = Currentdb.Openrecordset("Table2",dbopensnapshot)
Set rst3 = Currentdb.Openrecordset("Table3",dbopensnapshot)

Do While True
rst.AddNew
For intC1 = 1 to 3 '(assumes 3 recs for each row in Table1)
s = "T1R" & intC1
IF not rst1.EOF() then
'change fieldname to your fieldname
rst(s) = rst1("FieldName")
rst1.moveNext
Endif
Next
For intC2 = 1 to 4 '(assumes 4 recs for each row in Table2)
s = "T2R" & intC2
IF not rst2.EOF() then
'change fieldname to your fieldname
rst(s) = rst2("FieldName")
rst2.moveNext
Endif
Next
For intC2 = 1 to 4 '(assumes 4 recs for each row in Table3)
s = "T3R" & intC3
IF not rst3.EOF() then
'change fieldname to your fieldname
rst(s) = rst3("FieldName")
rst3.moveNext
Endif
Next
rst.Update
Loop

rst1.Close
rst2.Close
rst3.Close
rst.Close

Set rst1 = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst = Nothing
Click to expand...

Will try this out and post the results.

Thanks again for your advice and time.
 
Back
Top