Type Conversion Failure When Importing txt File

[Eka1618]

Hello,

I am trying to resolve an issue a client is having while importing a .txt file.

The field on the table is AMT-1 and is a Number/Long Integer

The corresponding data on the txt file is 11 characters in length and can be positive or negative. The Txt file is also generated via mainframe programming so sometimes the number value can look like the following examples:

a) 0000000030}
b) 00000002900
c) 0000004920K

I think that the bracket or letter at the end of these numbers represent the HEX value of the negative number. The negatives in my situation are not being imported correcly anymore in the db I am trying to fix and it is possible my client mistakenly changed something about the datatype settings.

In addition, when the client imports the file, they use a saved Import Specification that was created to delmit the fields properly, so it is also possible that the settings for the AMT-1 field on the Import Specs no longer correspond the way it needs to with the Table design.

I did not create the db I am working on and there is no design information available for me to determine what the settings used to be. So my question is what should the Datatype & format settings be in order to import the negative amounts?

If this type of question has already been answered can someone please direct me to the post so I can take a look.

Thank in advance for your help!

~EKA~

[alansidman]

Unless you are planning on doing any mathematical calculations on the field, I would bring it in as a text field. If you need to alter it for the assumed Hex? values, that can be modified in a query. If you bring it in as text, it will retain the leading zeros. Is this important?

Alan

[Eka1618]

I think there may be additional calculations used on these fields. I can cetainly try this out though for now. Thank you.

~Erica~

[Eka1618]

Well changing the type to Text did work, but causes some other errors for my client. Thanks anyway for your help. I've tried all the different datatype/format combinations, but I cannot figure out why this is all of a sudden not working properly.

~Erica~

[Eka1618]

Another thing I want to mention is that previously, it looks as though the negative numbers were possibly not insterted into this database at all. in the number type field, there are several records where this value is blank, but the text file has always had either 11 zeros or another numeric value like in the examples i've provided above. The text file field is never blank or NULL.

[orange]

Can you post part of the text file? The one that comes from the mainframe with no alteration.
And show what import specification is being used.

[Eka1618]

Orange,

The Import Specification is as follows:

Field Name	Data Type	Start	Width	Indexed	Skip
FIELD 1	Long Integer	1	7	No	FALSE
FIELD 2	Text	8	1	No	FALSE
FIELD 3	Long Integer	9	8	No	FALSE
FIELD 4	Integer	17	1	No	FALSE
FIELD 5	Long Integer	18	8	No	FALSE
FIELD 6	Long Integer	26	9	No	FALSE
FIELD 7	Text	35	2	No	FALSE
FIELD 8	Text	37	50	No	FALSE
FIELD 9	Long Integer	87	8	No	FALSE
FIELD 10	Long Integer	95	8	No	FALSE
FIELD 11	Long Integer	103	8	No	FALSE
FIELD 12	Long Integer	111	8	No	FALSE
FIELD 13	Long Integer	119	8	No	FALSE
FIELD 14	Long Integer	127	8	No	FALSE
FIELD 15	Long Integer	135	8	No	FALSE
FIELD 16	Long Integer	143	8	No	FALSE
FIELD 17	Long Integer	151	8	No	FALSE
FIELD 18	Long Integer	159	8	No	FALSE
FIELD 19	Long Integer	167	8	No	FALSE
FIELD 20	Long Integer	175	8	No	FALSE
FIELD 21	Long Integer	183	8	No	FALSE
FIELD 22	Long Integer	191	8	No	FALSE
FIELD 23	Long Integer	199	11	No	FALSE
FIELD 24	Long Integer	210	4	No	FALSE
FIELD 25	Long Integer	214	8	No	FALSE
FIELD 26	Long Integer	222	8	No	FALSE
FIELD 27	Long Integer	230	9	No	FALSE
FIELD 28	Long Integer	239	8	No	FALSE
FIELD 29	Long Integer	247	7	No	FALSE
FIELD 30	Text	254	2	No	FALSE
FIELD 31	Long Integer	256	9	No	FALSE
FIELD 32	Long Integer	265	3	No	FALSE

Field 31 is the one of 3 fields having this issue. The following represents one record within the text file:

1234567O00000000 000000000D PLEASE MAIL TESTING 000000000000000000000000 20030000 00000000000000000000000000000000000200000050000 000000000 0000000 00000057J000


I have highlighted the area of the record causing the issue for you. Thanks for assistance with this!

~Erica~

[orange]

Are the spaces in the data sample really there or were they introduced by the software that operates this forum?
Can you attach a text file with 10 or so records?

According to NoteTab the record is 161 characters long?
I was expecting a record length of 267 based on your specification???

[Eka1618]

Orange,

Sorry about that, I'm new to this forum and those spaces were introduced by the software on here. Let's try the following:

Field Name	Data Type	Start	Width	Indexed	Skip
Field1	Long Integer	1	7	No	FALSE
Field2	Text	8	1	No	FALSE
Field3	Long Integer	9	8	No	FALSE
Field4	Text	17	1	No	FALSE
Field5	Long Integer	18	9	No	FALSE
Field6	Long Integer	27	3	No	FALSE

1234567O00000000 00000057J000

I hope this helps, Thanks!

~Erica~

[alansidman]

Erica;
Orange is asking that you upload a copy of the text file you are attempting to import but only with the first 10 or so records. Click on Advanced tab in the forum for instructions to upload a file.

Alan

[orange]

Thanks Alan.
Yes I'd like an attached file with a .txt extension.

[Eka1618]

Attached is a text file to go with the last Import Spec I gave to you. I am currently looking into whether or not the "Code Page" that is listed could be causing the problem. The user just told me that they needed to recreate the Import Spec the other day, so now I'm know that they did not set it up the same way as before. The code page is 'OEM United States'.

I hope this works for you this time, Thanks!LIRA 3.txt

~Erica~

[orange]

I get import errors in field5 row2 and row5 (expected in my view).
Where does the data originate? How is it produced?

If it's a sign or a different coding scheme that is important.

Here's a link to add to possible confusion
http://www.3480-3590-data-conversion...ed-fields.html

[Eka1618]

Orange,

The data is created via mainframe programming. The 'J' and '}' in the two rows having the issue represent a negative amount. The field in the mainframe programming is set as a N9.2 (123456789.12 for example).

A file is produced on the mainframe and we transfer that as a .txt file directly to a specific location for the user to pick up. When this amount is a negative, the last digit is stored into some character that I believe represents the HEX value of the last digit & also means it's a negative at the same time. The decimal is never stored in this file.

I will take a look at the link you have provided. Thank you!

[orange]

With the info at the link I gave is this paragraph which may be the key to solution.

To convert the zoned ASCII field which results from an EBCDIC to ASCII character translation to a leading sign numeric field, inspect the last digit in the field. If it's a "{" replace the last digit with a 0 and make the number positive. If it's an "A" replace the last digit with a 1 and make the number positive, if it's a "B" replace the last digit with a 2 and make the number positive, etc., etc. If the last digit is a "}" replace the last digit with a 0 and make the number negative. If it's a "J" replace the last digit with a 1 and make the number negative, if it's a "K" replace the last digit with a 2 and make the number negative, etc., etc. Follow these rules for all possible values. You could do this with a look-up table or with IF or CASE statements. Use whatever method suits you best for the language you are using. In most cases you should put the sign immediately before the first digit in the field. This is called a floating sign, and is what most PC programs expect. For example, if your field is 6 bytes, the value -123 should read " -123" not "- 123".