Appending Help

[Guest]

I need some help. I asked this question before and i got help and answer. Now
it seem that the file is coming in a new format and I need some help
I have 35 files that i need to append. the file has 8 columns of data that i
need. Now there is a 9th column i need to have added on automatically. this
is how the format looks like

date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#

Credits total by OPR isxxx)

The xxx is what I need the 9th column to be. so the table in the database
has to be
date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#|Opr ID

Please help

 

[John Vinson]

I need some help. I asked this question before and i got help and answer. Now
it seem that the file is coming in a new format and I need some help
I have 35 files that i need to append. the file has 8 columns of data that i
need. Now there is a 9th column i need to have added on automatically. this
is how the format looks like

date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#

Credits total by OPR isxxx)

The xxx is what I need the 9th column to be. so the table in the database
has to be
date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#|Opr ID

Please help

I'm not sure I understand. You have eight columns of data in the
import table; I have no idea what the phrase "Credits total by OPR is:
(xxx)" might mean. What's an OPR? Operator? How can you determine from
the input file what should be stored in the [OPR ID] field? And is
that actually an ID, a number presumably identifying an OPR, or is it
some sort of calculation?

John W. Vinson[MVP]

 

[Guest]

Im sorry, it is an Operator ID(3 digits to be exact)
the 8 columns are in the correct order, so when i do an append, it is
straight forward. No manipulation required

But for the 9th column, i need to get that operator ID and have it be
connected right after Autho#

I need some help. I asked this question before and i got help and answer. Now
it seem that the file is coming in a new format and I need some help
I have 35 files that i need to append. the file has 8 columns of data that i
need. Now there is a 9th column i need to have added on automatically. this
is how the format looks like

date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#

Credits total by OPR isxxx)

The xxx is what I need the 9th column to be. so the table in the database
has to be
date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#|Opr ID

Please help

I'm not sure I understand. You have eight columns of data in the
import table; I have no idea what the phrase "Credits total by OPR is:
(xxx)" might mean. What's an OPR? Operator? How can you determine from
the input file what should be stored in the [OPR ID] field? And is
that actually an ID, a number presumably identifying an OPR, or is it
some sort of calculation?

John W. Vinson[MVP]

 

[John Vinson]

Im sorry, it is an Operator ID(3 digits to be exact)
the 8 columns are in the correct order, so when i do an append, it is
straight forward. No manipulation required

But for the 9th column, i need to get that operator ID and have it be
connected right after Autho#

That's what I'm asking, Justin. Where do you get the operator ID FROM?

You can put a calculated field in your append query by typing

Opr ID: 345

in a vacant Field cell. This will put the number 345 into every record
that you import. But if the Opr ID is a variable or comes from
someplace else, then I need to know WHERE it comes from to help you.

Also note that you can append eight fields to a nine-field table
perfectly well, provided the ninth field isn't required or has a
default value. The field will simply be left NULL and can be updated
later, manually or using an Update query.

John W. Vinson[MVP]

 

[Guest]

the operator ID is coming from the next line underneath all data that is from
the 8 columns. I don't want the users to manually type anythign on the table.
I want it so that user will upload 35 files, and then can do their work
Like i said before, the format of file comes in like this

date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#
xxx xxxxx xxxx xxxxxxxxx xx xxx xxxx xxxx
..
..
..
..
"Operator ID" comes after all the data.

now, each file has different operator ID's. So for every ID, it shows all
the data for that ID. I just want to somehow get that ID tag and make that
for my 9th column during the appending

 

[John Vinson]

the operator ID is coming from the next line underneath all data that is from
the 8 columns. I don't want the users to manually type anythign on the table.
I want it so that user will upload 35 files, and then can do their work
Like i said before, the format of file comes in like this

date|Location|Batch#|CardNumber|Exp|Type|Amount|Autho#
xxx xxxxx xxxx xxxxxxxxx xx xxx xxxx xxxx
.
.
.
.
"Operator ID" comes after all the data.

The Access File... Import assumes that the file being imported is
homogenous - i.e. that all records have the same "shape", in the
number of fields and matching datatypes. This file doesn't.

I'm guessing that you'll need to write some VBA code to open the file,
step through it, and update the table in code; you might (depending on
the file size) be able to read the entire file into an array and
update it from there.

But I don't see any good way of using File... Import or a Query to do
this.


John W. Vinson[MVP]

 

[UpRider]

Justin, how were you doing the append before your input files added the 9th
field?
That's our starting point for modifying what you have.

UpRider

 

[Guest]

ok, the appending is done thru my upload screen
the user will have a form, where they will select the file(s) that they want
to upload. From there, the command button that starts the uploads, will
trigger my append query. It is searching for a specific word from the line.
Here is an example of what the data from the file looks like

Cashier : 21D
-------------------------------------------------------------------------------
Ref # Account Exp Type Amount Date Bus. Auth#
Terminal
-------- ------------------ ----- ---- -------- -------- ----- ------
--------
8599001 xxxxxxxxxxx xx/xx PA*P $2 08/30/05 08/30 084862 7801

Credit Totals for Cashier: 21D

this is an example of what the files have. My append query will search for
the ref#, account, exp, type, amount, date, bus. auth# and terminal. So from
this example it will take all data starting with "8599001" and append that
line to the table. and it will loop until it is done. Now, I am trying to
somehow have "Cashier" right after the terminal column. I want that cashier #
associated with that line im uploading

 

[UpRider]

Can't you just add CASHIER as a new additional field at the end of the table
definition?

 

[Guest]

Well, i need to somehow grab the Cashier's ID and add it to the field
that is where I am having the problem. Each data has different Cashiers' ID.
If it was a default ID, then I have no problem.
But from the example before, the cashier's ID is 21D and you see all the
data for this cashier.
Then in the same file, there will have cashier 32a, and you see that
person's data

 

[UpRider]

1. Does each of the 35 files have a single operator each, or are there
multiple operators in each file?
2. Does the operator record (which appears not to match the data record
format) come after the data records for that operator or before?
3. Is there any way to get your input files to include the operator in each
record for that operator's data?

Your easiest solution is 3.
For 1 or 2, I think you will have to open an input file, read each record,
determine if it is a data record or an operator record and .addnew as
appropriate to your output file.
If the operator record is after all the data records for that operator, it
really complicates things, in that you don't know what the operator is until
you have already passed over all the data records for that operator.

This is a big task. Your best bet is to have the input files reformatted to
include the operator field complete with each operator's data records.