Cleaning Up Imported Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using left sided queries to clean up and verify data. The data sources
are Excel Spreadsheets which I import. One of the worksheets has over 100
columns or fields in each record, basically a Yes/No, but either with an "X"
or empty. Out of all these fields only about 20 are used, all the others are
empty in each record.

How can these empty fields be listed or eliminated or marked so that I can
just delete them? Any ideas? This thing approaches the limits of Access
from some warning messages I have gotten while working with it.

Thank you.
 
Tried running an update query but got an error message "Too Many Fields
Defined" with 150 fields in the query. Any ideas appreciated.
 
Hi Jeff,

Without knowing more about your data it's hard to be sure. It sounds as
if you're importing data from these worksheets to temporary tables,
after which you run queries to match the imported data against your
"permanent" tables. I'll assume the latter have a normalised structure.

Possibilities include:

-Link the Excel data rather than import it, then just ignore the empty
colums.
-Use VBA code to inspect the worksheets before importing and delete any
columns you're not interested in.
-Save the "wide" Excel in CSV format and use the txtnrm.pl utility at
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm to convert it
into a tall, narrow table with the empty values omitted, ready to import
into Access.
 
Back
Top