Text files-Importing number field

  • Thread starter Thread starter Sara
  • Start date Start date
S

Sara

Hi,
I'm not sure if I posted this question in the correct
place. If not, sorry. I'm trying to import a text file
into my access database. The problem is one of the fields
in the table is a number field and has to be to do
calculations. In the text file, sometimes there's an X in
the field - I get a Numberic Overflow error and it will
not import. I need to change any X to a 0 in my text file
before I import. Is there any way to do this
automatically? Can a batch file do this?

Thanks!!
Sara
 
Hi,

Is the text file a comma separated list of values or tab separated perhaps?

If yes you could for example simply open the text file in your favourite
text editor (i.e. EditPlus) and for a comma separated list for example
do a search and replace of find: , X, with: , 0,

Regards,
Peter
 
It would be possible to write code to pre-process the text file before
importing it, but it will be simpler to import it first (using a text
column) then run an update query to fix it up:

UPDATE tblTest SET tblTest.TestNum = IIf([TestText]="x",0,Val([TestText]))
WHERE (((tblTest.TestNum) Is Null));

Where TestText is the text colum into which you imported the data, and
TestNum is the numeric column that will store the 'fixed up' result.

Note: When you add a numeric column to a table, Access sets the Default
Value property to 0 (zero). The SQL statement above depends on the column
'TestNum' having no default value, so that we can use the test for Is Null
to process only records that have not previously been processed.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top