If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
importing tables in proper format from word 2007 into access 2007
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 |
#2
|
|||
|
|||
importing tables in proper format from word 2007 into access 2007
On Jan 22, 7:50*am, s 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 |
#3
|
|||
|
|||
importing tables in proper format from word 2007 into access2007
s 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 Three newsgroups? Oh well. 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. 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 |
#4
|
|||
|
|||
importing tables in proper format from word 2007 into access 2007
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. |
#5
|
|||
|
|||
importing tables in proper format from word 2007 into access2007
s wrote:
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. The code I posted should work. If you had 1 record in table 1, 2 in table2, and 3 in table 3 the end result should be T1Rec1 T2Rec1 T3Rec1 T2Rec2 T3Rec2 T3Rec3 |
Thread Tools | |
Display Modes | |
|
|