Hello

I run the Import table command:

IMPORT TABLE KTempImport
FROM "WorkFile.txt"
IN STORE ImportFiles DELIMITER CHAR #124

and it raise the error:
.... #1011 An error occurred with the value XXXXXXXXXXXXXX (The value would
result in truncation)'.

The field for this value in KTempImport table is defined as VarChar 30.

Any suggestion what can cause this error?

Thanks
Sorin

Ok I found the problem

At the end of some lines the value (Char #124) appears twice.
Still is this a logic reason?

Thanks
Sorin




> I run the Import table command:
>
> IMPORT TABLE KTempImport
> FROM "WorkFile.txt"
> IN STORE ImportFiles DELIMITER CHAR #124
>
> and it raise the error:
> ... #1011 An error occurred with the value XXXXXXXXXXXXXX (The value would
> result in truncation)'.
>
> The field for this value in KTempImport table is defined as VarChar 30.
>
> Any suggestion what can cause this error?
>
> Thanks
> Sorin
>
>
>

>>> ... #1011 An error occurred with the value XXXXXXXXXXXXXX (The value would
>>> result in truncation)'.

>>At the end of some lines the value (Char #124) appears twice.

EDB follows the SQL standard which is pretty hard-core about overflowing data. If you have a field VARCHAR(10) and you try to pass "1234567890A" into it (11 CHAR) you don't get "1234567890" you get NULL ... and in the "import" situation it gives you a nice error to ensure you realise!

ANSI CHAR #124 is just a vertical line ... so that CHAR itself shouldn't have caused a problem. I think it is more likely to be the length of the string.

... Unless somewhere under the hood EDB interprets || as a concatenation symbol (as in Firebird/Interbase) ...

--

The main takeaway advise is:
* Prior to exporting to TXT try to ensure field lengths & types match the data as closely as possible. If you can't do this import into a "loose" database structure (with longer VARCHARs than needed) & use DELPHI or SQL to truncate fields to the correct lengths.
* Run good checks on TXT files prior to importation! Remove random CHAR outside of the safe range (#1 - #31 is carriage returns line-feeds etc., #32 - #122 is letters & numbers characters, #123 - #255 is other stuff like $/£ etc.) Random ANSI CHARs do seem to get added to data from time to time (especially if the original user has pasted data having copied it from a screen-grab) ... use something like StringReplace in Delphi to pull them out.

Thanks Adam

The fact is that removing the | from the end of the line solve the problem.

Thanks
Sorin


<Adam Brett> wrote in message
news:F82C01D9-424A-4CBC-9681-184CD433DF76@news.elevatesoft.com...
>>>> ... #1011 An error occurred with the value XXXXXXXXXXXXXX (The value
>>>> would
>>>> result in truncation)'.
>
>>>At the end of some lines the value (Char #124) appears twice.
>
> EDB follows the SQL standard which is pretty hard-core about overflowing
> data. If you have a field VARCHAR(10) and you try to pass "1234567890A"
> into it (11 CHAR) you don't get "1234567890" you get NULL ... and in the
> "import" situation it gives you a nice error to ensure you realise!
>
> ANSI CHAR #124 is just a vertical line ... so that CHAR itself shouldn't
> have caused a problem. I think it is more likely to be the length of the
> string.
>
> .. Unless somewhere under the hood EDB interprets || as a concatenation
> symbol (as in Firebird/Interbase) ...
>
> --
>
> The main takeaway advise is:
> * Prior to exporting to TXT try to ensure field lengths & types match the
> data as closely as possible. If you can't do this import into a "loose"
> database structure (with longer VARCHARs than needed) & use DELPHI or SQL
> to truncate fields to the correct lengths.
> * Run good checks on TXT files prior to importation! Remove random CHAR
> outside of the safe range (#1 - #31 is carriage returns line-feeds etc.,
> #32 - #122 is letters & numbers characters, #123 - #255 is other stuff
> like $/£ etc.) Random ANSI CHARs do seem to get added to data from time to
> time (especially if the original user has pasted data having copied it
> from a screen-grab) ... use something like StringReplace in Delphi to pull
> them out.
>

Hello

I made several more experiments and my coclunsion is that the delimeter
isn't the problem.

The error raise only when at the at the end of the incoming row are one or
more empty columns

How can I solve that problem?

Thanks
Sorin


"Sorin H" <sorinh@zahav.net.il> wrote in message
news:B8FB65BA-F806-4207-A3CA-FCF0D4B8C940@news.elevatesoft.com...
> Thanks Adam
>
> The fact is that removing the | from the end of the line solve the
> problem.
>
> Thanks
> Sorin
>
>
> <Adam Brett> wrote in message
> news:F82C01D9-424A-4CBC-9681-184CD433DF76@news.elevatesoft.com...
>>>>> ... #1011 An error occurred with the value XXXXXXXXXXXXXX (The value
>>>>> would
>>>>> result in truncation)'.
>>
>>>>At the end of some lines the value (Char #124) appears twice.
>>
>> EDB follows the SQL standard which is pretty hard-core about overflowing
>> data. If you have a field VARCHAR(10) and you try to pass "1234567890A"
>> into it (11 CHAR) you don't get "1234567890" you get NULL ... and in the
>> "import" situation it gives you a nice error to ensure you realise!
>>
>> ANSI CHAR #124 is just a vertical line ... so that CHAR itself shouldn't
>> have caused a problem. I think it is more likely to be the length of the
>> string.
>>
>> .. Unless somewhere under the hood EDB interprets || as a concatenation
>> symbol (as in Firebird/Interbase) ...
>>
>> --
>>
>> The main takeaway advise is:
>> * Prior to exporting to TXT try to ensure field lengths & types match the
>> data as closely as possible. If you can't do this import into a "loose"
>> database structure (with longer VARCHARs than needed) & use DELPHI or SQL
>> to truncate fields to the correct lengths.
>> * Run good checks on TXT files prior to importation! Remove random CHAR
>> outside of the safe range (#1 - #31 is carriage returns line-feeds etc.,
>> #32 - #122 is letters & numbers characters, #123 - #255 is other stuff
>> like $/£ etc.) Random ANSI CHARs do seem to get added to data from time
>> to time (especially if the original user has pasted data having copied it
>> from a screen-grab) ... use something like StringReplace in Delphi to
>> pull them out.
>>
>
>

Sorin


By "empty columns" do you mean that the column in the database should be empty (ie NULL) or that there are more columns than exist in the table?

Roy Lambert [Team Elevate]

No the problem is with the Imported Txt file.

If the last column in a row is empty it raise an error

Thanks
Sorin

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:FCB25EBD-284D-4ED2-9358-C536F4B362CB@news.elevatesoft.com...
> Sorin
>
>
> By "empty columns" do you mean that the column in the database should be
> empty (ie NULL) or that there are more columns than exist in the table?
>
> Roy Lambert [Team Elevate]
>

Sorin


1. Is there a column in the table to receive the data?
2. If so is that column set to NOT NULL?

Roy Lambert [Team Elevate]

Thanks Roy

Maybe this will clarify the problem. The rows on the txt file I want to
import are:

XXXXXXXXXXXXX|158|xxxxxxxxxxxxxxxxxxxx| |08/02/12|12/03/12|10-349-13| |
XXXXXXXXXXXXX|159|xxxxxxxxxxxxxxxxxxxx| |08/02/12|12/03/12|20-123-51| |
XXXXXXXXXXXXX|160|xxxxxxxxxxxxxxxxxxxx|865265|08/02/12|21/03/12|56-755-00|
|80;1
XXXXXXXXXXXXX|161|xxxxxxxxxxxxxxxxxxxx|561456|12/02/12|12/03/12|23-160-15| |

The | is the delimiter.

When I try to import this file it raise the error.
If I remove the | char from the end of the row it's work ok.

Thanks
Sorin


"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:9DF97676-22F1-4693-9985-C69B9B80DDC1@news.elevatesoft.com...
> Sorin
>
>
> 1. Is there a column in the table to receive the data?
> 2. If so is that column set to NOT NULL?
>
> Roy Lambert [Team Elevate]
>

Sorin


1. Does row 3 import correctly without alteration?
2. Please post the table structure

Roy Lambert [Team Elevate]