Import to Access from Excel - database exists, not all fields match

  • Thread starter Thread starter Melanie
  • Start date Start date
M

Melanie

Hello! I have an interesting problem to deal with, and honestly I'm
just not sure how to work with it. I work for a university, and our
Health Services department is soon going to have to start submitting a
report to the government regarding the controlled substances they
distribute to students. On the surface it seems easy - the program
that they use to track it (NuScript its called) allows you to export
data to various formats to work with, plus it uses access databases in
the background that you can just tap into for the information
directly. Trick is the format the government wants this file - a
fixed width text file. No delimiters or anything, merely fixed
width. Well needless to say the setup of NuScript's databases don't
correspond directly to the length required for each field. I can
however define a table in Access that has the exact corresponding
lengths and such. Trick is this - how can I import an Excel or Access
file, for that matter, into an existing table, and define exactly
which fields go where? Keep in mind, this is something that needs to
be done monthly, and needs to be handled by the folks in the pharmacy,
not us here in IT. It has to be a relatively easy import/export
process that I can train any user to do. Danged fixed width issue is
making this much more difficult than it should be....
 
Hi Melanie

Not sure if you need to import.
If the NuScript db allows you, create a query with the info you need with
the fields in the right place.

Then you can export this as a fixed width file exactly as needed. I replied
to a similar posting today headed "Exporting w/ specific paramters" (spelt
exactly that way) - have a look at that to see if it helps. Post back for
more info.

If you do need to import...
Access to Access - can you link the tables and use a query to put right data
in right order?
Excel to Access - Think you'll need to import into a temporary table & then
use a query to place in final table. (Also possible to link to Excel to avoid
use of temporary table but depends on how the Excel file is used).

Hope this helps

Andy Hull
 
OK this helps a lot - another question tho - the best route, if I can
do it, is to work with the original database. Is there a way I can
specify the exact length of a field (even if it means truncating what
comes from the database). Can I do that thru input mask? Darned
specifications...our description is pretty complete, but it cant be
more than 15 characters....
 
Hi Melanie

Did you find the post I mentioned?
If not, the general steps are:
1) Create a query to pick the right fields in the right order
2) Create an "Export Specification" - This will allow you to specify the
exact position and length of every field.
3) Do the export with DoCmd.TransferText

Below are the steps in more detail (hope it helps)...

1) Set up a query with the relevant columns in the right place.

2) Create an export specification...
Select the query, click on File then Export.
In the "Save as type" drop down, choose "Text Files" and click Save
Choose Fixed Width and click the Advanced button
In the bottom half of the window that now shows, set up the field start and
width
Then click save as, enter a name for the export specification, click OK and
then cancel out of the export (we only needed to get as far as saving the
spec).

3) Now, you can use the following command to perform the export...

DoCmd.TransferText, acExportFixed, ExportSpecName, ExportQryName,
ExportFileName, False

where ExportSpecName is the name you used above - to save the spec,
ExportQryName is the name of your query and ExportFileName is the full path &
filename of the exported file. The False means don't export the column
headings (change to True if you want them).

You should be able to get the exact output you want by setting the Export
Specification - no need to mess with input masks etc

Regards

Andy Hull
 
Back
Top