exporting to Access

[Lorien2733]

Let me preface this question by saying no know virtually nothing about Excel
- I am an Access programmer - so please dumb down your answer for a newbie.
I have an Excel file supplied by my user. It started life (I think) as a
..pdf file that they somehow converted to Excel worksheet. The original file
must have been a print file because the page headers, page numbers etc are in
the cells with the data. It looks like they took a report and split it into
columns. If I try to import it to Access as an Excel file I get an error
message saying "The Microsoft Jet database engine could not find the object
"July 92"s$" (July 92 is the name of the first workbook page.) When I export
the Excel file as text and import the text file to Access, I get all the page
headings, page numbers etc. Does anyone have any idea of how I can get JUST
THE DATA from the original file from any of the formats - XL, .pdf. or .txt -
into an Access database? Is there a way to export only certain row or columns
from Excel? I'm lost.
TIA,
Lynne

 

[Tyro]

Sounds to me like you're going to have a lot of manual clean-up to do on
that file. It is very easy in Excel to delete the the rows containing the
header and footer info and blank rows. All you have to do is click on the
row header at the left of the sheet and drag down to select the rows you
want to delete and press delete. If those rows were gone, would that solve
your problem? Perhaps you could do some of the clean-up in Excel and part
in Access.

Tyro

 

[CLR]

Tyro is right.........it sounds like you have a lot of cleanup to do on
that file......this is all doable in Excel, and if the situation occurs
regularly, this cleanup can all be done by macros.....I do it all the time
importing files from mainframe databases that come with a lot of junk
included

Vaya con Dios,
Chuck, CABGx3

 

[Lorien2733]

Thanks for the responses. How do I do this with macros? I've got hundreds of
pages of this junk. I don't want to have to go through page by page and do
this manually.

 

[CLR]

Sorry, but I did not mean to indicate that there is a quick and easy answer,
as each one of these situations is different. You must do a lot of hand work
in order to clean up files like these. It involves identifying unique
characteristics of the header sections as opposed to the data sections and
then individual macros can be written to delete them......there are also
existing macros (www.cpearson.com , among others) that will delete blank
rows. Chip also has a nifty add-in called Hexchars that will identify
invisible characters in the cells, such as carraige returns or line feeds,
etc..........the free add-in called ASAP Utilities, available at
www.asap-utilities.com will allow the deletion of individual characters.
Macros can be recorded while performing the manual operations to help with
future files from the same source, considering that the deviations will
probably be similar. Any way you cut it, it's a lot of work......but it does
feel pretty good when you get all the way through it and it all works.

hth
Vaya con Dios,
Chuck, CABGx3

 

[George Nicholson]

When I export

the Excel file as text and import the text file to Access, I get all the
page
headings, page numbers etc.

I'm going to assume that you need to keep this data in some sort of order (a
worse case scenario) and that you have lots of individual "pages" to
process.

If you can import all the data into a single existing table in Access that
has an autonumber field, you should then be able to query on some other
field that will allow you to delete all "extraneous" blank rows & Page
Headers/Footers at one time. Then restore the original order by sorting on
the autonumber field. That should give you a table with just data.

"Existing table" could be created by doing the import once to a new table.
That will define your fields (assuming all files have the same layout...).
Delete the contents of that table. Add an autonumber field. Then do all your
imports, in order.

 

[Mystif]

About one year ago I knew next to nothing about Excel either. I have
come a long way in only a year, but even more important is what I
learned I could do in Access.

I have code that allows me to import the desired range of rows and
columns from the desired Spreadsheets in a Workbook. It uses VB code
and commands, creates the tables automatically, includes an automunber
column and removes the empty rows. It is lightning fast.

It is all done from within Access without opening Excel.

The way my code was written I moved it into VB with the modification
of something like three lines of code.

I doubt my code will fit your needs perfectly but you are welcome to
take a peek if you would still like some help.

I wrote it with lots of imagination, determinination and, have no
doubt, with lots of help from people in forums like this one.

Mystif