Data formatting/input masks

[Tony in Michigan]

I'm trying to work out a few data formatting issues with a project I have at
work. I need to take existing data and write it to a new table, with a new
format, so the data can be exported as a csv file for processing by another
application. In my testing phase, I was working with small amounts of data,
so I could do it manually. Now I have hundreds of thousands of lines, to
work with in the actual process. In any event, I need to take the following,
I've used input mask formatting to illustrate the data.
I can parse the data, and reassemble it with several queries. Is there a
more efficient way?
AAA-0000000-0_099 and make it AAA 0000000-0

The next two examples are in the same field. I can either have an 8 or 10
digit number. If it is 8 digit, it is back filled with spaces.
0000000000 needs to be 0000.00.0000
00000000 needs to be 0000.00.00

Lastly my dates
MM/DD/YYYY or M/DD/YYYY needs to be MMDDYY

I'd appreciate a nudge in the right direction on this. I've been able to
address all of my other issues, amd am only left with these three fields.

 

[tina]

try using the Format() function in a query to display the data as needed.
also, in case you weren't aware: you don't need to write data to a new
table in order to export it. you can write a Select query, and export the
query dataset. the OutputTo action may work for you, or the TransferText
action. have a look at those topics in Help.

hth

 

[June7]

Try
1. AAA-0000000-0_099 : AAA 0000000-0;0;_ The 0 between the semis causes
value to be saved with symbols/spaces. Am not understanding need for the
_099 part.

2. 0000000000 : 0000.00.0099;0;_ the 99 should convert to spaces if nothing
entered, assume this is text not numeric data

3. MMDDYY : could be dates can't be stored this way and still be recognized
as a date by the program. Would be text and might have to use CDate()
function whenever wanted to display with date punctuation. So if you really
need to, maybe could treat as text with mask that does not save symbols -
00/00/"20"00;;_

 

[Tony in Michigan]

The first and second work perfectly! I still have to try the date. Thanks!

 

[Tony in Michigan]

Hi Tina,
The Masks the June sent worked, I just have to test the date. I can't
believe I was beating on it with no results for so long!

As for "writing out" the data. What I have is a large database of vendor
data, that I keep in its original format. I validate that data against US
Treasury dept data, and write out the "valid" lines.
I now have the original data,

invalid data, to correct, and revalidate (usually fails due to rounding
errors)

validated data to calculate and FTP as a CSV for processing For my next
challenge, I can send up to 9999 "files" with up to 100000 data lines in the
CSV. Here I need to figure out how to put a header on the validated data. I
currently insert it in Excel.

Most of my knowledge was gained by brute force. Are there any resources you
can suggest?

 

[tina]

for what you're describing, no, sorry, no resources specific enough to save
you any time over what you might find on your own - though chances are that
there *are* resources that would help you; it's just a matter of finding
them. good luck with it.