Import Text file Numbers as characters

[jgarry]

Hi, Thanks for your assistance

Im modifying a program that imports a lot of .txt files
this is no problem.

My problem is I have two fields in the text file that are numbers that need to be brought in as characters. I do not have access to the export that creates the .txt file so I cannot change the export field type to characters.

the line of inmport data looks like this:

555555555,2,12345,0,100,,,,,123456789011,272481884,C

the string of numbers can be up to 30 numbers. It will always be numbers. (so im told) I love it when I'm told it will always be something)


When it is imported it looks like :

555555555|2|12345|0|100|||||||7.12202E+11|272481884|C


Should I program a fix to input " " around the number? or is there a calculation I can do against the 7.12202E+11 to make this a full number then convert it to a character field?
thanks for the assitance

Jim

 

[Mike Gagnon]

do you have control over the table i gets imported into? Are the fields the correct maximum lenght. Have you tried this:

append from c:\test delimited with ,

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jgarry]

Mike, Thanks, that is close, the command that is used to import the file is :

APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited with '"'

now I have tried it in a few different ways
APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited with ,
this brings in all the other fields except the two that i need they come in blank

APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited
this comes in the same as the first.

any other suggestions. we are talking about 300 - 30000 lines of imported data.

Jim

 

[Stella740pl]

Jim,

if it works with
APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited with '"'
you don't need to change it to
APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited with ,

You need to change the structure of the table you import into, as Mike Gagnon already suggested.
In that table , increase the size of the field where you get 7.12202E+11 to the needed number of digits. If you want to make them character later on anyway, you can also change the type of the field to character, so you won't need to convert.

 

[jgarry]

Hi, thanks Stella740pl,

The field size in the Table is 30 characters 30 this data inbetween the comma's is 12 charcters (in this case). I can change the fox table to a nubmer field but would rather not at this time due to the fact that this is a conversion program someone else wrote and I do know where else the data is looked at. I could bring i into a varable then change it?


Also the APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' delimited with '"' dosent work since the length of numbers has gone from 10 - 12 and can incease to a lenght of 30 in the future.

Thanks folks you are helping

Jim

 

[jgarry]

I just looked at another file from earlyer days and discovered why the fild is a character field. there are numbers in the export that have - in them. so converting to an number filed will not work.

again they(the analists on this project) told me that it was always numbers.

Jim

 

[jimstarr]

Hi Jim,

When I do the APPEND into a cursor with all fields defined as c(30) I get 123456789011 and not 7.12202E+11. Am not sure what you're doing differently.

Jim

 

[jgarry]

Thanks jim

Could it be the text file. It does come from an excel spreadsheet? even tho it is suppose to be a .txt file could there be formatting?


Again thanks for all your help. Ive imported text files for year but have never seen this. I know it is something simple. either with the table,data or I am leaving out something.

I will work on it this weekend. thanks again.

Jim

 

[jimstarr]

Try

APPEND FROM ZPREFIX+'_EMPNETPAYS.TXT' TYPE XLS

Jim