Table Manipulation

[Jeff Gilstrap]

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

 

[Duane Hookom]

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(),...

 

[Jeff G]

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

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

.

 

[Duane Hookom]

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

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

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

.

 

[Jeff G]

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

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


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



.

.

 

[Duane Hookom]

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

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

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" <[email protected]>
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



.

.

 

[Jeff G]

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

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


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



.



.

.

 

[Jeff Gilstrap]

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

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


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



.



.

.

 

[Duane Hookom]

Glad to hear this worked for you. Excel is a great program but not as a
database.

--
Duane Hookom
MS Access MVP

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

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


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



.



.

.