Horrible Excel File Woes 2

[indyaries]

I have a large Excel file formatted thus;

COLUMN A
1 Full Name
2 Full address
3 City State Zip

Columns B and on have other information for the employee in Column A, and is located on the same row as the City-State-Zip.

I need to get the employee information in Col A into a tabular form, such as (A) Employee Name, (B) Address, (C) City, (D) State, (E) ZIP

I would prefer to do this in Access, but can try to do this in Excel if needed.

 

[dhookom]

Assuming you import the Excel table [tblExcelImport] into Access and allow the addition of a primary key field [ID]. You can create a crosstab like:

TRANSFORM First(tblExcelImport.A) AS FirstOfA
SELECT Max(tblExcelImport.ID) AS MaxOfID
FROM tblExcelImport
GROUP BY ([ID]-1)\3
PIVOT "Col" & ([ID]-1) Mod 3;

You can then create a query based on your crosstab and your import table.

SELECT ID, Col0, Col1, Col2, tblExcelImport.B, tblExcelImport.C
FROM qxtbExcelImport INNER JOIN tblExcelImport ON qxtbExcelImport.MaxOfID = tblExcelImport.ID;

That's all the difficult parts. Now you only need to parse the city state and zip using basic string functions.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[indyaries]

Hello Duane,

The SQL isn't working. Here is what happens when I create a query based on the table -- to show you the field names.

SELECT tblExcelImport.ID, tblExcelImport.EmplData
FROM tblExcelImport;


I pasted your crosstab into the query SQL view, and received this error:

"The Microsoft Jet database engine does not recognize 'tblExcelImport.A' as a valid field name or expression."

Here is the SQL as I pasted it into a new query;

TRANSFORM First(tblExcelImport.A) AS FirstOfA
SELECT Max(tblExcelImport.ID) AS MaxOfID
FROM tblExcelImport
GROUP BY ([ID]-1)\3
PIVOT "Col" & ([ID]-1) Mod 3;

Any suggestions on what I'm doing wrong ??

 

[indyaries]

DUH !!!!!

AFTER I posted the above, I compared the SQL, and noticed that your

TRANSFORM First(tblExcelImport.A) AS FirstOfA

would translate to...

TRANSFORM First(tblExcelImport.EmplData) AS FirstOfA

Once I made this change, it worked!

Thanks, Duane !