Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 26 total |
Import table error |
Wed, Feb 15 2012 6:57 AM | Permanent Link |
Hershcu Sorin | 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 |
Wed, Feb 15 2012 7:10 AM | Permanent Link |
Hershcu 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 > > > |
Wed, Feb 15 2012 7:36 AM | Permanent Link |
Adam Brett Orixa Systems | >>> ... #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. |
Wed, Feb 15 2012 8:08 AM | Permanent Link |
Hershcu Sorin | 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. > |
Mon, Feb 20 2012 11:30 AM | Permanent Link |
Hershcu Sorin | 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. >> > > |
Mon, Feb 20 2012 12:01 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Mon, Feb 20 2012 12:24 PM | Permanent Link |
Hershcu Sorin | 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] > |
Mon, Feb 20 2012 1:09 PM | Permanent Link |
Roy Lambert NLH Associates 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] |
Tue, Feb 21 2012 2:45 AM | Permanent Link |
Hershcu Sorin | 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] > |
Tue, Feb 21 2012 3:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sorin
1. Does row 3 import correctly without alteration? 2. Please post the table structure Roy Lambert [Team Elevate] |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |