I just imported a bunch of excel forms into an access database.
The problem came when I hit txt fields in the middle of numeric data.

Is there a way to have access read in an excel sheet and see a text
field and report it.
The data would be something like

sample# record# value1 value2
001 001 1 1
002 002 no value 1

so a report would show
002 002 value1=text


is there a way to convert them on the fly ?
What is the best way to deal with this kind of crap.

I wanted to clear up one thing. Yes you get a report of type
conversion.
But since it only say line number and bla bla they are saying this is
insufficient.
(I said clean up this mess then send it to me...what mess the data is
there)

the reason I caught a lot of this is totalling their excel and my
tables and they did not match.

value 43
redone 2.5 === IS NOT 2.5 its a text field

I had 77 records out of 365 with this kind of stuff in it.


On Wed, 22 Apr 2009 06:33:23 -0500, sparks <sparks (AT) comcast (DOT) net> wrote:

I would consider importing into an all text Access table, clean that up,
then append to your production table.

"sparks" <sparks (AT) comcast (DOT) net> wrote

The main problem is the clean up.
It does no good with me editing their stuff.
If I change "redone 2.5" to 2.5
or the next record "01/12/08 66.453"

the worst one is "redo 3.99"
is that saying that 3.99 is incorrect and needs to be redone or that
it was redone and 3.99 is the correct value.
I can not make these determinations.

so the dept sending out this stuff needs to make the changes and
verify their data and even worse, later that dept will still have this
in their files.Send it to me or someone else again and it will never
be adressed by the ones who are putting this out.

God I hate excel.
I even asked for people to learn to use it and that notes can be put
in another column. That went no where fast.
IF I can put it in a cell it must be the way to do it.
And colors we need lots of colors AHHHHHHHHHHhhhhhhh

lol




On Wed, 22 Apr 2009 07:08:44 -0500, "paii, Ron" <none (AT) no (DOT) com> wrote:

I am sorry ron, I was not thinking when I read your post.
I am assuming that you are saying import it in a txt table and then
you can report errors from that.
DOH

On Wed, 22 Apr 2009 07:08:44 -0500, "paii, Ron" <none (AT) no (DOT) com> wrote:

sparks wrote:
text. Maybe there's a way to select the whole Excel spreadsheet and
format as text. Then import the Excel file and do an "error pass".
Maybe add 1 more column with error fields. Then scan the fields that
should be numeric and if the field isnumeric, leave alone else move the
field to the new field for errors. Then import into a table formatted
for numerics. You could then filter on the fields that weren't numeric.

You might also want to ask how to clean up an Excel spreadsheet in an
Excel newsgroup.

Try a different tool for this job. Why dont you use a proper ETL tool
like KETTLE (www.pentaho.com). I have used it may times for similar
tasks. You read the spreadsheet in to the tool and you can specify
what you want the columns to be, then you can run all sorts of logic
against it to change the values in the cells (if need be), then export
it out to almost anything you want, including Access and Excel.

The other way to do this would be to write some code to 'read' the
values in each cell as they are imported. Create an Excel object
inside Access, control it for what you want, then take the data as you
need it.

Or maybe even quicker and easier is to handle the data re-working in
Excel first. Create another sheet in the workbook, and use formulas to
populate the cells with the data you need the way that you need it.
Export the result or copy / paste the values into a 'clean' workbook,
and suddenly the you have a well prepared workbook with exactly the
data you want.

Just some thoughts.

Cheers

The Frog