ellerbe |
Advanced Member |
|
Seattle |
|
|
None Specified |
|
Wednesday, October 17, 2007 |
Wednesday, July 28, 2010 4:53:58 PM |
56 [0.31% of all post / 0.01 posts per day] |
|
And post #2
http://altovamapforceedi.blogspot.com/2010/07/control-numbers.html
Comments, anyone... anyone Bueller?
What are your tips and secrets for EDI with mapforce!
|
This is a first post of a series on how I typically do EDI with Altova Mapforce.
http://altovamapforceedi.blogspot.com/2010/07/interchange-ids-one-of-first-things.html
Comments are most welcome!
|
[quote=Oceanic679]Hi, Need some help on how to import the flat text file into db. Thank you for your help...
Here is the sample of the text file.
....
Please lay out a little more about what you are trying to achieve.... pseudo code\logic is fine
I'm guessing you will want to split your data (by line???) and the plus looks like some sort of separator???
Anyways then you can filter off of the first two digits and do a sql insert.
|
One thought is throw an exception if the data is missing ??
Don't know if that helps :)
-Michael
|
aptrdennis wrote:I found a great Case Study for doing control numbers using a flat file https://www.altova.com/documents/nff_case_study.pdfgo to page 8 of 12 in the pdf for details. I used the same premise to do so using a SQL DB Table. I created a master table in SQL for holding my control numbers. I then add this as an additional data source twice. (I will be posting back the incremented number into the second instance) I used a stored procedure to get the last number already issued from this master table, then add 1 to it using auto increment and post the result as the ISA and GS control numbers in my map, as well as posting back to the SQL table using the second SQL connection in my map. The only "gotcha" that I have experienced so far, is that when you compile the map using C#, edit the console file before compiling in Visual Studio to ensure that the Mapping instructions for the EDI file appear before the Auto Increment / Post to SQL in the Second SQL Connection otherwise your numbering system will be off by one everytime you run the map. (the EDI file will have number 0002 and the master sql table will show the last number used as 0001) I tried to post a screen shot of the SQL connection portion on my map, but was unable to.
Thanks, he he as I said I had done it before (thats my case study) :)
|
lbgtp wrote:Did you ever figure this out and get it working?... if so, please share.
I have done this using a flat file, though in the future will do it writing to a database.
|
You have got to be kidding me! I have been trying the most evil things to get this functionality and it has been built in all along!!! gah. What I ended up doing was creating a text file that contained a CRLF and then using that :) it shows the great flexibility of the product, but man was I making that harder on myself! So anyone out there looking to insert a CRLF or insert a TAB use the char-from-code function!
|
This one might be difficult (or maybe not possible)
I have an XML file with this sort of format.
<ASN> <HEADER> <BillTo>SYSCO</BillTo> <ShipTo>SYS RDC,VA</ShipTo> <ShipFrom>010</ShipFrom> <SalesOrderNumber>1136431</SalesOrderNumber> <BOLNumber>1136431</BOLNumber> <SCAC>BUEL</SCAC> <CustomerPO>Z11382 02608220</CustomerPO> <CurrentDate> <year>2009</year> <month>07</month> <day>17</day> </CurrentDate> <NumberOfPallets>014</NumberOfPallets> <GrossWeight>025722</GrossWeight> <NumberOfCases>0000828</NumberOfCases> <DETAILS> <PALLET> <SSCC>00100726081462665882</SSCC> <ProductKey>74865-07842</ProductKey> <PackFillFactor>1.0000</PackFillFactor> <DetailQuantity>60.0000</DetailQuantity> <OrderLine>1</OrderLine> <UOMFrom>CS</UOMFrom> <UOMTo>EA</UOMTo> <LotKey>39109-9F10G1N</LotKey> <ProductionDate>2009-06-10</ProductionDate> <PullDate>2011-06-10</PullDate> <Layers>6</Layers> <Cases>10</Cases> <TareWeight>0</TareWeight> <ShipWeight>0</ShipWeight> </PALLET> <PALLET> <SSCC>00100726081462673504</SSCC> <ProductKey>74865-54101</ProductKey> <PackFillFactor>1.0000</PackFillFactor> <DetailQuantity>30.0000</DetailQuantity> <OrderLine>6</OrderLine> <UOMFrom>CS</UOMFrom> <UOMTo>EA</UOMTo> <LotKey>39134-9F12J1N</LotKey> <ProductionDate>2009-06-12</ProductionDate> <PullDate>2011-06-12</PullDate> <Layers>6</Layers> <Cases>10</Cases> <TareWeight>0</TareWeight> <ShipWeight>0</ShipWeight> <ProductKey>74865-54101</ProductKey> <PackFillFactor>1.0000</PackFillFactor> <DetailQuantity>30.0000</DetailQuantity> <OrderLine>6</OrderLine> <UOMFrom>CS</UOMFrom> <UOMTo>EA</UOMTo> <LotKey>39475-9G07J1N</LotKey> <ProductionDate>2009-07-07</ProductionDate> <PullDate>2011-07-07</PullDate> <Layers>6</Layers> <Cases>10</Cases> <TareWeight>0</TareWeight> <ShipWeight>0</ShipWeight> </PALLET> </DETAILS> </HEADER> </ASN>
Which I need to convert into an 856 doc. The hard part is the hierarchical level segment. HL
For the above two pallets I would need.
HL~1~~S* TD1~CAS~14~~~~G~25722~LB* TD5~~~~T* REF~2I~1136431* DTM~011~20090717* N1~SF~NATIONAL-CHEHALIS~ 9~010* N3~CHEHALIS INDUSTRIAL PARK* N4~Chehalis~WA~98532* N1~ST~Sysco Corp : SYS RDC,VA~91~SYS RDC,VA* N2~1000 BAUGH DRIVE* N4~FRONT ROYAL~VA~22630~USA* HL~2~~O* PRF~02608220~~~20090717* REF~CR~Z11382* REF~YD~SYS~National Frozen Foods Corp.* REF~VR~009262064* REF~IL~19150639* REF~VN~19150639* N1~RE~~91~SYS RDC,VA* N1~VN~National Frozen Foods~ 9~009262064* HL~3~2~T* TD1~CAS~60~~~~N~1800~LB* MAN~GM~00100726081462665882* PAL~4~6~10* HL~4~3~I~0* LIN~1~UK~10074865078426~VN~74865-07842~PI~1440270~~~CH~US* SN1~~60~CA* HL~5~4~UT* SLN~1~~~60~CA~~~~LT~39109-9F10G1N* DTM~094~20090610* DTM~208~20110610* HL~6~2~T* TD1~CAS~60~~~~N~1800~LB* MAN~GM~00100726081462673504* PAL~4~6~10* HL~7~6~I~0* LIN~6~UK~10074865541012~VN~74865-54101~PI~8731697~~~CH~US* SN1~~30~CA* HL~8~7~UT* SLN~1~~~30~CA~~~~LT~39134-9F12J1N* DTM~094~20090612* DTM~208~20110612* HL~9~6~I~0* LIN~6~UK~10074865541012~VN~74865-54101~PI~8731697~~~CH~US* SN1~~30~CA* HL~10~9~UT* SLN~2~~~30~CA~~~~LT~39475-9G07J1N* DTM~094~20090707* DTM~208~20110707* HL~11~2~T* TD1~CAS~60~~~~N~1800~LB* MAN~GM~00100726081462681035* PAL~4~6~12* HL~12~11~I~0* LIN~5~UK~00074865001120~VN~74865-00112~PI~1055417~~~CH~US* SN1~~60~CA* HL~13~12~UT* SLN~1~~~60~CA~~~~LT~39207-9F17B1N* DTM~094~20090617* DTM~208~20110617*
So the way the HL works is that HL~X (X is just a incrementing by one sequence number) thats easy enough to handle with a autonumber. But for HL~X~Y (The Y indicates what the parent node is for the element)
So the first time you use the HL, you use it like HL~1~~S* (There is no parent node , and it is at the shipment level indicated by S)
The next time you use it like HL~2~~O*
So then it gets interesting, for the next you use HL~3~2~T* (this means #2 is its parent)
Then a little data then HL~4~3~I~0* (#3 is its parent)
Then a little data, then HL~5~4~UT* (#4 is its parent)
Then we start over again with HL~6~2~T* (#2 is its parent)
Then a little data then HL~7~6~I~0* (#6 is its parent)
...data then HL~8~7~UT*
ect...
so the data format is something like
1 **2 ***3,2 ****4,3 *****5,4 ***6,2 ****7,6 *****8,7
ect...
Any thoughts on how to achive this with mapforce? It's a sticky one. Any thoughts at all welcome!
-Michael
|
Ahh looks like this has been added in version 2009?
|
So lets say I have a db (simplified of course) that contains
Fish Bob Frog Kermit Dog Fred Cat Sam
I would like to output a file that would categorize alphabetically the returned data
So something like
A B C Cat Sam D Dog Fred E F Fish Bob Frog Kermit
If I could use the sql order by it might be easier but it doesn't look like I can? Anyone done anything like this before?
-Michael
|
|