Solved

Flat Files recognizing Numeric vs Char fields

Posted on 2003-11-03
11
277 Views
Last Modified: 2010-05-01
I am creating an ASCII file, which will be transmitted to an external source via FTP.  In the file, if field lengths are shorter then what the file layout requires, then I need to pad either with trailing spaces or leading zeros depending on the data type.

My question is this, when I have a numeric field, the leading zeros are removed, and in many cases I need them to exist.  So, if I use a string field and force a right justify by padding leading zeros, will there be anyway to tell when the data is extracted by the external source that it was not a numeric field?  
0
Comment
Question by:Able22
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 3

Expert Comment

by:aflat362
ID: 9672451
No
0
 

Author Comment

by:Able22
ID: 9672467
So this method will work?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9672545
It really depends on that is used to extract the data.  Some applications make the assessment as they import, others can use a format file, while still others make a guess based on the data in the field.  Do you know what the files are going to be used for and how?

Leon
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9672566
"My question is this, when I have a numeric field, the leading zeros are removed, and in many cases I need them to exist..."
But if you need them to exist, it is not a numeric field....
0
 

Author Comment

by:Able22
ID: 9672648
I understand your point Richie_Simonetti, however, here is an example:  The ASCII file layout that is required by the external vendor has a NUMERIC field, Cust_ID.  CUST_ID has a required length of 10.   However, my company's Cust_ID field is only a length of 6.  It is specified in the specs that if my field lengths don't match the ones in the given file layout, then I have to pad accordingly.  Therefore, I'd have to add 4 extra zeros to the beginning of my CUST_ID value to make it equal a length of 110.   So, I was wondering if I changed the CUST_ID to a string and added the zeros and printed it to the file, when the extract the data, will I cause a data-type mismatch.............
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Able22
ID: 9672654
oops,  in the above comment, 10, not 110....
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9672676
The answer is: "Not likely, but posible".  Most software pakages or application will convert it to numeric using some form of "type casting".

Leon
0
 
LVL 15

Accepted Solution

by:
SRigney earned 75 total points
ID: 9672685
The external application gets everything as a string.
By you meeting their format they probably already have an application set up to parse the field correctly and put it into their database.

They don't know or care if you store your data as a string or a number, as long as it's in the file in the format that they want.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9672709
Could you replace those leading 0 by spaces?
I am asking you this 'cause i do a regular job on this kind of things (convert excel files to fixed length ascci files for SAP).
0
 
LVL 9

Expert Comment

by:Dang123
ID: 9672750
Try writing your numbers using the format command

     Format(intCustID, "0000000000")


This will allow you to output your numbers with zero padding easily. The data will be pulled into the reading program as it expects it. I also work with COBOL, and there a field that contains any non-numeric characters (except for one period) is considered a character string. this method would definitely work to pass a number. The only way to be sure though is to send a test file to the program that needs to read your file.
0
 
LVL 3

Expert Comment

by:aflat362
ID: 9672910
In a test enviornment - print it numerically, print it as a string.  Compare the 2 output files.  If you can tell no difference - the application which reads it can't either.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now