Hi again,
Yes, the CSV file can have multiple rows per "order" - each order consists
of a Header, Line Items, and a Summary. To make matters more fun, the CSV
file can also contain multiple orders, such as:
HDR,1234,122334,CC1,112....
LI,PartID=12,12,$1.00,$.25...
LI,PartID=144,144,$12.00,$.57...
LI,PartID=172,172,$11.75,$.91...
SUM,3322,FedEx,Consignee...
HDR,144,2334,CC31,82....
LI,PartID=10,10,$1.00,$.25...
LI,PartID=141,141,$12.00,$.57...
LI,PartID=171,171,$11.75,$.91...
SUM,3322,FedEx,Consignee...
HDR,341,134,CC21,552....
LI,PartID=2,2,$1.00,$.25...
LI,PartID=34,34,$12.00,$.57...
LI,PartID=72,72,$11.75,$.91...
SUM,3322,FedEx,Consignee...
these files come in with no linking between them and the first process of
making sure all the orders go together was hard enough. I have brought
everything into a temp table and then use a number of queries to separate
orders and the only thing that is holding me back is getting this one
blasted
order description line!
To make matters more fun, there has yet to be a standard format for these!
I
find myself sorting through some files that are order separated (as
above),
yet others separate by product, and others by store... arrrghhh!!
Thanks for the recommendation, I was thinking I would need to do a loop of
some kind, but for the above example, I believe I would need to do 2, one
to
separate the order, then to go through that order and create the string.
Do
you have any sample code that I may work with? Thanks again
-gary
Jeff Boyce said:
So, you're saying that your CSV file is NOT one record per row, but could
have multiple rows to bring in all the related data for a single
"record"?
Right off the top of my head, you'd probably need to build a procedure
that
checks the first row, gets the "how many rows in this record"
information,
then cycles through records, appropriately appending data until the next
"record". Not an insignificant effort...
Another option might be to see if you could get all the data imported as
one
(long) record (but I don't understand your data structure well enough to
tell if this was a good possibitlity).
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Gary Dolliver said:
Hi Jeff,
Thanks for the reply. I am hoping to come up with a single description
line/field that lists all the parts ordered. I am working with EDI
data
that
brings in all the line items in separate rows, however, the vendors
requirement is also to have a single line summary description for each
PO.
The way the CSV file comes in is it has no headers, but the fields give
indicators for if they would be in the Header (HDR), Line items (LI) or
Summary (SUM) For example:
HDR,1234,122334,CC1,112....
LI,PartID=12,12,$1.00,$.25...
LI,PartID=144,144,$12.00,$.57...
LI,PartID=172,172,$11.75,$.91...
SUM,3322,FedEx,Consignee...
I have been able to convert everything correctly, except for the line
item
summary, which for the above example would need to read 12,144,172 -
Now,
as
different incoming POs may have different line items and such, I would
need
this to be dynamic. Hope that makes sense? Help?
-gary
:
Gary
You are describing a "how", how you've come up with to do something.
"What"
you are trying to do isn't clear yet (i.e., what will having this
allow
you
to do?).
By the way, having a [field1] "that can have "x" amount of records" is
not a
standard description of a relational table in Access. Could you be
more
specific? Is this a one-to-many relationship shoved into a single
field?
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
Hi all,
Is it possible to create a text string of column data? For example,
in
[table1], I have a field [field1] that can have "x" amount of
records.
[field1]
a
b
c
d
e
is there a way I can create a value to be [a,b,c,d,e] where I am
creating
a
sting of all 5 records in one line (with commas)? Also, if it comes
to
having 7 records, the string would then be [a,b,c,d,e,f,g] This
line
is
needed for a description line I will be performing filters on
other
columns
as well. Help is always appreciated, thanks!
-gary
