Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Database Text Import For Append

Posted on 2015-01-08
11
Medium Priority
?
128 Views
Last Modified: 2015-01-27
When I am importing a text file for appending to an existing Access table I am having problems with the import wanting to define a field as a number when it needs to be text.  As a result I am getting import errors as there is text ultimately showing up in the field.  Why doesn't an Access import for append take on the field properties of the table being appended to?  

What is a better way of importing the data without having to import into a new table each time and then append?  Or, am I approaching this in the wrong way?  Thanks.  - Reilly
0
Comment
Question by:tomfarrar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40538033
What is a better way of importing the data without having to import into a new table each time and then append?
Well ... that is the best way - i.e. import into a "staging" table, and then validate that data prior to moving it to your live table. This allows you to catch any data issues before it hits your live tables. You can provide users with a form where they can review data, or correct errors, etc and then use a standard APPEND query to move the data over to live.

If you simply cannot do the staging table, then you'd need to setup an "Import Spec", which allows you to define the characteristics of the incoming fields and such. Capricorn1 shows how to do that here: http://www.experts-exchange.com/Database/MS_Access/Q_28532814.html
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40538047
You might also consider rather than importing the data, linking to it.  Then, once you have the linked table (which really ends up being your staging table), you would write a query that imports the data into your production table and does the explicit conversions for you, so you might use cstr([fieldName]), to ensure that the field which contains numbers is actually appended as a string.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40538111
Yea, the staging table may end up the solution.  A minor problem it is causing is there are multiple source "text" files (same fields) that would be required to import into the staging table one at a time.  Just trying to eliminate the additional steps.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 40538135
As Scott mentioned above, the other advantage of a staging table (either imported or linked) is that you can "validate that data prior to moving it to your live table".  This is the technique that I generally use because you never know when someone has attempted to manipulate the raw text or Excel file.  By using the staging table, you can test for values in one or more fields that may not be valid and can warn the user before importing those records, or could block importing of those records all together.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40538140
Linking might ultimately be the solution as the data are download (.csv) from a data warehouse.  Ideally Access could link directly to the warehouse query results, but that is not going to happen any time soon (I don't think).  So I am working with 5 download files I need to get into an Access table.  That is where I am.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40538146
So, here is the question.  If I use a staging table, do I need to re-create it each time I bring one of the text files in?  Or is there a way to create a staging table (with correct data types or whatever) that would ensure the data comes in correctly each time I import one of the text files?  Thanks.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40538227
It is the import spec that is going to control the format of the columns.  Use the TransferText method and specify the import spec.  To create the import spec initially, you need to run the import ONCE manually so you can step through it and define all the data types.  Then press the "Advanced" button to save the spec.  Once it is saved, you can automate the import.

Since you want to reuse one name for the import table, run a delete query before you import the next set of data.  Run any validation against the staging table, then run an append query to the final table.  You can automate everything but handling errors may require user interaction.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40538242
I got the part about the import spec, I think.  But I am not sure of the TransferText method.  Is that some macro command?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40538250
the command line is

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", "C:\folderName\myText.csv" , True

if you have looked at the link posted by Scott, the code is there
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40547740
Thanks All - It appears Scott's post had all the answers I needed, and with the additional guidance from the rest of you I was able to get there.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40573939
Think this question's points have been awarded.  Not sure why attention is needed..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question