importing tables in proper format from word 2007 into access 2007
in [General]
|
Prev: access 2007 add contacts from outlook
Next: Visually represent project dates and activities as shapes in Excel
From: s on 21 Jan 2010 15:50 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
From: Steve on 21 Jan 2010 17:33 On Jan 22, 7:50 am, s <s...(a)mailinator.com> wrote: > 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 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
From: s on 24 Jan 2010 16:30
Salad wrote: Thanks for the response and code snippet. > Three newsgroups? Oh well. 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. 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 Will try this out and post the results. Thanks again for your advice and time. |