Import and Append Error Msg

[Mortie]

I am consistently getting an error message represented by the image below called Image1-ErrorMsg

I have tried a variety of approaches to get data from table A in a master database into a smaller database that is a mirror of the larger (just smaller).

Every time I run an Append data or Import I get the message.

The image ExportTable is the structure from the larger table. The image ImportTable is the structure of the smaller table.

The numeric fields in the receiving table are not too large as numeric fields.

The unusual part of the operation is that when I click the OK button on the error message and then review the table all the data is in the table as it should be. I can find no number in any numeric field larger than 3 digits (plus 2 decimals. In any table in the entire set there is no field with a number larger than 4 digits.

Any suggestions as to where I should look or what the problem might be?

The code for the import is as follows and is generated from the import genie versus me doing the hard code.

temporary_import_table = file.temp_path_get()+ "driver_temp_table.dbf"
import_destination_table = "driver"

import_filename = filename_decode("[exe_folder]files\districts\central\27\districtexportdriver.txt")
if file.exists(import_filename) = .f. then
ui_msg_box("Error","File to import not found: '"+import_filename+"'.",ui_stop_symbol)
end
end if

ON ERROR GOTO ERROR2202200718596562


import.type = 0
import.names = "use"
import.file = import_filename
import.db = temporary_import_table
import.skip_lines = 0
import.skip_bytes = 0
import.field_sep = ","
import.record_sep = "<CR><LF>"
import.text_qualifier = "\""
import.record_len = 87
import.rem_blanks = .F.
import.escape_newlines = .F.
import.fields = 8
import.field1 = "hhnbr,c,0,16,0"
import.field2 = "policynbr,c,1,16,0"
import.field3 = "last_name,c,2,30,0"
import.field4 = "first_name,c,3,25,0"
import.field5 = "gender,c,4,7,0"
import.field6 = "birthday,d,5,8,0"
import.field7 = "pref150rate,n,6,9,2"
import.field8 = "age,n,7,2,0"
import()


a_tbl = table.open(import_destination_table)
append.t_db = temporary_import_table
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = 8
append.m_field1 = "HHNBR"
append.m_exp1 = "@driver_temp_table->HHNBR"
append.m_field2 = "POLICYNBR"
append.m_exp2 = "@driver_temp_table->POLICYNBR"
append.m_field3 = "LAST_NAME"
append.m_exp3 = "@driver_temp_table->LAST_NAME"
append.m_field4 = "FIRST_NAME"
append.m_exp4 = "@driver_temp_table->FIRST_NAME"
append.m_field5 = "GENDER"
append.m_exp5 = "@driver_temp_table->GENDER"
append.m_field6 = "BIRTHDAY"
append.m_exp6 = "@driver_temp_table->BIRTHDAY"
append.m_field7 = "PREF150RATE"
append.m_exp7 = "@driver_temp_table->PREF150RATE"
append.m_field8 = "AGE"
append.m_exp8 = "@driver_temp_table->AGE"
append.t_count = 0
a_tbl.append()


GOTO CONTINUE2202200718596562
ERROR2202200718596562:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Import Operation"+crlf()+error_text_get())
END
CONTINUE2202200718596562:

a_tbl.close()

'In case the destination table is not already in the Database, add it...
file_add_to_db(table.filename_get(import_destination_table))
'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if

[Stan Mathews]

Re: Import and Append Error Msg

Are either of the numeric fields in the destination table defined as calculated fields?

The unusual part of the operation is that when I click the OK button on the error message and then review the table all the data is in the table as it should be.

Because the append halted at the point the error occurred. Check the next record that would be appended from the transaction table to find the problem values.

[Mortie]

Re: Import and Append Error Msg

Thanks Stan!

That fixed the error probloem plus send me back to brainstorm as to resolving the two fields that are calculated. I believe I know what to do (which is a good thing I guess).

I would never have known that one, that's for sure.

[Raymond Lyons]

Re: Import and Append Error Msg

Thanks Stan!

That fixed the error probloem plus send me back to brainstorm as to resolving the two fields that are calculated. I believe I know what to do (which is a good thing I guess).

Bob,

You are not alone. Just FYI, I recently had a situation where a client told me he wanted field, say n_months to = n_yrs * 12, where n_yrs would never be more than 30 years and comes in via an import (actually an append, but it does not matter). So I created a calculated field via field rules 3 digits long (n_months) and a 3 digit field n_yrs where n_yrs would get its value via an import/append operation. Note that 2 digits for n_yrs should have been sufficient and would have given a better clue to the problem that was coming. In any case, all went along smoothly for a long time--until one day someone inputting data into the import file put in 360 instead of 30 years (room for it, lack of training and no validation). Although I had room (somewhat unfortunately!) for 360 to be imported into my n_yrs field, obviously 360*12 = 4320 and that won't fit into the calculated 3 digit n_months field. The error message, like yours, was not as helpful as it perhaps should be, but it was not totally incorrect either. Live and learn!

Ray Lyons