1 column spreadsheet to Good Access Table? 1

[dpav29]

I have to convert several thousand records from a spreadsheet to a good relational database format. The problem is that the excel spreadsheet is one column with 15,000 rows like so:

Name
Address
Phone
Name
Address
Phone
Name
Addres
Phone

etc, etc, etc. . . it goes on for ever.

After I import it, I want to convert the data to appropriate unique columns for name address and phone (then I'll parse out the pieces fo the address).

Can anyone help with accomplishing this. . .assuming the lines are consistent (i.e. every forth row starting with row one goes to column 1, every forth from row two goes to column two, etc.).

Thanks for any help as always!

 

[mordja]

dpav29,

So your excel file is basically two columns, the first the type (name,address etc) the second the value. If the sequence is exact in that there is always Name, Address, Phone .... then why dont you just filter for each value on the first column, copy the results to a new spreadsheet and repeat twice more.
You should then have three columns, name, address and phone.

MOrdja

 

[PHV]

Import the spreadsheet in a new table, say myTable.
In the table design window add to myTable an autonumber field, say myID.
In the SQL pane of the query design window paste this code:
SELECT N.Name, A.Address, P.Phone
FROM (myTable N
INNER JOIN myTable A ON N.myID=A.myID-1)
INNER JOIN myTable P ON N.myID=P.myID-2
WHERE (N.myID Mod 3)=1;
If the result is OK, transform this query to a make table query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dpav29]

Thanks for the reply mordaj,

Actually, it's only one column. . .no field names. It just looks like:

john smith
123 main st
555-1212
Sally jones
333 maple st
555-1237
etc. etc.

I'm looking for a way to build the columns using record number intervals in some way.

 

[PHV]

Oops, posted to quickly:
SELECT N.Field1 AS Name, A.Field1 AS Address, P.Field1 As Phone
FROM (myTable N
INNER JOIN myTable A ON N.myID=A.myID-1)
INNER JOIN myTable P ON N.myID=P.myID-2
WHERE (N.myID Mod 3)=1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dpav29]

Yes. . .I was just reading your original reply . . that looks like exactly what I need. Can't wait to try it!

Thanks a lot . . I'll let you know what happens!

 

[dpav29]

You've done it again!

I actually gave you a simplified version of what I was trying to accomplis. Here's the actually query I used. . worked like a charm!

Thanks again!


SELECT A.Field2 AS Address, I.Field2 AS ProvID, M.Field2 As ProvName, P.Field2 As Phone, C.Field2 As Accepting, S.Field2 As Status
FROM ((((CignaPCPtest A
INNER JOIN CignaPCPtest I ON A.ID=I.ID-1)
INNER JOIN CignaPCPtest M ON A.ID=M.ID-2)
INNER JOIN CignaPCPtest P ON A.ID=P.ID-3)
INNER JOIN CignaPCPtest C ON A.ID=C.ID-4)
INNER JOIN CignaPCPtest S ON A.ID=S.ID-5
WHERE (A.ID Mod 6)=1;

 

[dpav29]

Hey PH,

Sorry to bother you with this again, but just when I thought life was good. . .

The query displays the data perfectly, but I can't get the make table to run in Access. It returns an invalid syntax error and then screws up the sql code in the query (erases all of the joins).

Do I have to write the make table in SQL?

 

[PHV]

Save your query and create a new one based on it.
If this new query still display OK, then try to convert it in a make table query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dpav29]

Interesting. . .won't let me save it either. Even though it works fine, when I try to save it I get "invalid argument".

SELECT A.Field1 AS Address, M.Field1 As ProvName, P.Field1 As Phone, C.Field1 As Accepting, S.Field1 As Status
FROM (((derm A
INNER JOIN derm M ON A.ID=M.ID-1)
INNER JOIN derm P ON A.ID=P.ID-2)
INNER JOIN derm C ON A.ID=C.ID-3)
INNER JOIN derm S ON A.ID=S.ID-4
WHERE (A.ID Mod 5)=1;