[mso] Re: Importing Excel to Access + Empty fields in a CSV file/Glenda
- From: "Andrew Kendon" <tyto2820@xxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 17 Aug 2005 20:36:02 +0100
Hi Glenda,
You wrote:
> Hi Andrew. I'm interested in the part of your process that
> "rearranges data from another workbook..." How is that set
> up? /glenda
Well, I don't have the sheets in front of me (and I forgot to bring them
home from work). My 'solution' is a bit pitch-and-patch but it was (and is)
designed to see if what I wanted to do would work.
Currently I have an Excel sheet which contains details of exams clients have
taken - one line for an exam. This data needed transferring into an Access
database, which had already been set up.
I collect more data in the spreadsheet than goes into the database and some
of it is in a different format so, at the moment, I copy the info required
to update the database (all the records which aren't marked with a 'y' in
the DB column but which do have a 'P' or 'F' in the results column.
Automatic Filters are good for this: filter on blanks in the DB column and
non-blanks in the Results column.
I 'paste special' this filtered data into the first sheet of another
workbook which has two sheets. The second sheet picks up the data, line by
line, and formats it in columns labelled exactly as the database fields.
That's no big deal, really ... the main difference is that the exam
reference takes two columns (module no and paper number - there being four
papers one can choose from for each module) in the original data but the
database combines these two numerics in a text field. It's no rocket
science to convert two numeric fields into one text field in the format
required.
I then save this second sheet as a .csv file for import into Access and, in
Access, test my import first in a dummy table, then import into the real
one. The whole operation takes me less than 5 minutes, I guess.
Once the process is proved to work, I intended to automate it a bit more but
lack of time has been the problem. You might ask why I use a .csv file.
The answer is that I had some problems importing straight from the
worksheet. I think they were to do with the same problem of blank fields
that I get with the .csv - but it's easier to delete all the rows in the
.csv which contain blank data. If I can solve that problem and automate the
process of filtering the records, then it could all happen at the press of a
button. Indeed, there is actually no need to use an intermediary spreadsheet
but I wasn't allowed to alter the structure of the database, so I chose this
method while I was testing. If I have the time to implement it, I see no
reason why all my data should not go into the same database - complete with
the Pivot Table reporting tools which I think helped to earn me a promotion,
lol.
I hope that answered your question. If you have a specific problem which
might answer to a similar treatment, why not air it here?
Andrew
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail
http://uk.messenger.yahoo.com
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
- » [mso] Re: Importing Excel to Access + Empty fields in a CSV file/Glenda
