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
|
|||
|
|||
Table Manipulation
I have an excel spreadsheet that has contact data in one
colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G |
#2
|
|||
|
|||
Table Manipulation
Are the number of rows per address always consistent ie: each address is
exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr(),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G |
#3
|
|||
|
|||
Table Manipulation
Duane-
Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr(),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . |
#4
|
|||
|
|||
Table Manipulation
If the excel file isn't consistant then you may have to use a couple
recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr(),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . |
#5
|
|||
|
|||
Table Manipulation
Lets say the records including the blank row in the
spreadsheet are 5, 6 or 7 rows. Are you suggesting that I need to create a table with the 5, then create another with using a 6 in the crosstab and then another with a 7 by changing the 5 in the following crosstab? (three tables?) And then merging them together?I am not sure I follow. TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; -----Original Message----- If the excel file isn't consistant then you may have to use a couple recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr (),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . . |
#6
|
|||
|
|||
Table Manipulation
I would import the records to a table adding an autonumber field. Then open
the records in an ADO or DAO recordset. You can step through the records accumulating values until you hit a blank record. At that point, create a new record in a second table with the values. I don't think any type of crosstab would work if the number of "fields" is not consistent. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Lets say the records including the blank row in the spreadsheet are 5, 6 or 7 rows. Are you suggesting that I need to create a table with the 5, then create another with using a 6 in the crosstab and then another with a 7 by changing the 5 in the following crosstab? (three tables?) And then merging them together?I am not sure I follow. TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; -----Original Message----- If the excel file isn't consistant then you may have to use a couple recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr (),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . . |
#7
|
|||
|
|||
Table Manipulation
I don't understand what you mean by opening the the
records in an ADO or DAO recordset. DO you mean run a query with all records showing? I also don't understand what you mean by "You can step through the records accumulating values until you hit a blank record. At that point, create a new record in a second table with the values" -----Original Message----- I would import the records to a table adding an autonumber field. Then open the records in an ADO or DAO recordset. You can step through the records accumulating values until you hit a blank record. At that point, create a new record in a second table with the values. I don't think any type of crosstab would work if the number of "fields" is not consistent. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Lets say the records including the blank row in the spreadsheet are 5, 6 or 7 rows. Are you suggesting that I need to create a table with the 5, then create another with using a 6 in the crosstab and then another with a 7 by changing the 5 in the following crosstab? (three tables?) And then merging them together?I am not sure I follow. TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; -----Original Message----- If the excel file isn't consistant then you may have to use a couple recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr (),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . . . |
#8
|
|||
|
|||
Table Manipulation
Duane- FYI we adjusted the spreadsheet so that it would
have the same number of rows for each contact. The crosstab worked great!! Thank you very much for your solution on this. Jeff G -----Original Message----- I would import the records to a table adding an autonumber field. Then open the records in an ADO or DAO recordset. You can step through the records accumulating values until you hit a blank record. At that point, create a new record in a second table with the values. I don't think any type of crosstab would work if the number of "fields" is not consistent. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Lets say the records including the blank row in the spreadsheet are 5, 6 or 7 rows. Are you suggesting that I need to create a table with the 5, then create another with using a 6 in the crosstab and then another with a 7 by changing the 5 in the following crosstab? (three tables?) And then merging them together?I am not sure I follow. TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; -----Original Message----- If the excel file isn't consistant then you may have to use a couple recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr (),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . . . |
#9
|
|||
|
|||
Table Manipulation
Glad to hear this worked for you. Excel is a great program but not as a
database. -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... Duane- FYI we adjusted the spreadsheet so that it would have the same number of rows for each contact. The crosstab worked great!! Thank you very much for your solution on this. Jeff G -----Original Message----- I would import the records to a table adding an autonumber field. Then open the records in an ADO or DAO recordset. You can step through the records accumulating values until you hit a blank record. At that point, create a new record in a second table with the values. I don't think any type of crosstab would work if the number of "fields" is not consistent. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Lets say the records including the blank row in the spreadsheet are 5, 6 or 7 rows. Are you suggesting that I need to create a table with the 5, then create another with using a 6 in the crosstab and then another with a 7 by changing the 5 in the following crosstab? (three tables?) And then merging them together?I am not sure I follow. TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; -----Original Message----- If the excel file isn't consistant then you may have to use a couple recordsets in code to step through your imported field records and grab values to place in a "final" format. -- Duane Hookom MS Access MVP "Jeff G" wrote in message ... Duane- Unfortunately, no - some have one (or two) extra address lines. If I can get the contact information separated into records with maybe 5 or 6 different "generic" feilds using the crosstab query, the records could be manually cleaned up later. The only thing that is consistent in the spreadsheet is that there is one or two blank rows between each set of contact information. With only blank rows as the common trait, could the crosstab you suggest be modified to accomodate? Jeff G -----Original Message----- Are the number of rows per address always consistent ie: each address is exactly 4 rows and followed by a blank and then another address? If so, you can import these in to a table and allow Access to add an autonumber primary key. You can then use a crosstab to build your table. Assuming your table is tblImport with fields ID and Field1: TRANSFORM First(tblImport.Field1) AS FirstOfField1 SELECT [ID]\5 AS Expr1 FROM tblImport WHERE (((tblImport.Field1) Is Not Null)) GROUP BY [ID]\5 PIVOT ([ID]-1) Mod 5; Use this query as the basis for a maketable query. You can then add new fields to the made table for Town, State, and Zip. Use and update query with string functions like Mid(), Left(), Right(), Instr (),... -- Duane Hookom MS Access MVP "Jeff Gilstrap" wrote in message ... I have an excel spreadsheet that has contact data in one colume as follows: name1 company1 street1 town, st zip 1 blank line name2 company2 street2 town, st zip 2 blank line each line is in a different row. I would like to import it into an access table but the data is difficult to manipulate now without manually copying and pasting cells. Any ideas as to how I can manipulate this data into a more user friendly format such as: name1 company1 street1 town1 st1 zip1 name2 company2 street2 town2 st2 zip2 thanks much Jeff G . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Error Message | Di | New Users | 2 | June 30th, 2004 07:57 AM |
Image Control Table | John Gavin | General Discussion | 3 | June 28th, 2004 04:21 AM |
Footnotes in tables | Pam | Page Layout | 6 | June 18th, 2004 01:56 PM |
resize table from A4 size to A5 | ims | New Users | 3 | June 9th, 2004 01:05 AM |
trying to pull 2 fields from another table into this table | E Taylor | Database Design | 3 | May 21st, 2004 06:17 PM |