Can Data Be Manipulated When Importing a Text File

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

I'm importing text files of data where some values cointain muliple 9's for
missing. For example 9999 or 99999. Is it possible to somehow set these to
null during the import process.
Along that same idea can I do unit conversions on import. For example, if a
field is metric (meters) could I convert it to feet during the import
assuming of course it's not 9999 which means it should be set to null?
 
Yes you can, but not with the built-in import wizards. If you want to do
anything out of the ordinary, you have to use low level I/O in code. On my
website (see sig below) are several sample database that illustrate this.
Most of them start with "Import". "ImportLineInput.mdb" is probably the
most pertinent.
 
You might import the the data into an intermediate table (or just link to it
instead of importing it), and then execute an append query that does the
manipulation on the intermediate (or linked) table and inserts records into
the destination table.
 
Why do it on import? Do the import first - check that it worked - then run
some update queries to do what you need. For example:

UPDATE MyTable SET TheField = Null WHERE TheField = 9999

HTH,
TC
 
Back
Top