Import a dot as null value in numeric field

[Henrootje]

I lack knowledge :s

Ladies an gentlemen, I have a problem!
(but do not worry, I will take care of my personal problems myself,
this specific problem is access-related )



I am importing flatfiles into an access database.

These flatfiles are generated by a system that uses a dot ('.') as a
representation for a null-value.
When I import these files into access database access logs importerrors
(conversionerror) and fills the field with a zero (0).
However, for this field it is important to know whether the value is
'zero' or 'nothing'.
With access replacing the dots with 0 I can no longer see whether the
field was empty or not.

these flatfiles are pretty big (up to 16 to 20 MB) and simply replacing
the dots in the importfile by blanks (by hand, would not know how to do
it otherwise) takes a lot of time and I have to import up to 16 of
these files on a regular basis.

 

[Van T. Dinh]

Is this Field being imported as a Text Field or a Numeric Field?

I am fairly sure can use Word to open the Text file and use the Replace All
option in Word to replace '.' with an emty String.

 

[Henrootje]

They are being imported as numeric fields. The Word option would
greatly reduce the time we need. But is there some way to automate
this? Maybe using the tables with importerrors?



Van T. Dinh schreef:

 

[Van T. Dinh]

1. You can automate Word from Access if you want to modify the Text file ...

2. Access Import Wizard normally inspect a number of rows at the beginning
of the file to decide the data type so it looks like the values of the first
X rows are numeric. You may need to move some rows with "." to the
beginning and link the Text file as a Linked Table in Access. After that
you can design a (real) destination Table and use an Append Query to append
the Records from Linked Table to the real destination Table.

3. Alternatively, you can open the text file for read then read one row at
a time cheking for ".", manipulate the data and append the Record with the
massage data to the real Table. This may be slow as the code will process
one row at a time.

 

[Chuck Grimsby]

Rather then import the file into a temp table and "play" around with
it to get it into the format you need, read the file in, line by line,
and build SQL statements to INSERT the data into the table(s) you need
the data to go into.

On average, this speeds up the import process by 1/3 - 2/3 (a file
that used to take 15 minutes, now only takes 5 - 10).

In the import routine, you can then change the imported data to Nulls
as needed on the fly.

 

[Henrootje]

Thank you for your contributions, I have now several options available
and will try them, thank you!


Chuck Grimsby schreef:

 

[valter junior]

Rather then import the file into a temp table and "play" around with
it to get it into the format you need, read the file in, line by line,
and build SQL statements to INSERT the data into the table(s) you need
the data to go into.

On average, this speeds up the import process by 1/3 - 2/3 (a file
that used to take 15 minutes, now only takes 5 - 10).

In the import routine, you can then change the imported data to Nulls
as needed on the fly.