Data formatting/input masks

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.

try using the Format() function in a query to display the data as needed.
also, in case you weren't awa 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


"Tony in Michigan" Tony in wrote in
message ...
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.

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" wrote:

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.

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

"June7" wrote:

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" wrote:

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.

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" wrote:

try using the Format() function in a query to display the data as needed.
also, in case you weren't awa 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


"Tony in Michigan" Tony in wrote in
message ...
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.

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.


"Tony in Michigan" wrote in
message ...
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" wrote:

try using the Format() function in a query to display the data as
needed.
also, in case you weren't awa 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


"Tony in Michigan" Tony in wrote in
message ...
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.