Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reading multiple line edi file into one record in Access

Posted on 2012-08-30
18
Medium Priority
?
293 Views
Last Modified: 2012-11-09
I have an edi file that has 3 lines in it

1  sku  price  
4  item detail shipping price
5  shipping tracking number

How do I link or take this file and have access read then create the record from this file, so that i have one record holding all three lines as one.  it is tab del.  each file has multiple orders that are coming in on it.  and ther is no linking information between the row or I would just create 3 files with each one sorting out of the the lines then tying them together.  Any help is much appreciated.
0
Comment
Question by:fasse
  • 5
  • 4
  • 3
  • +2
15 Comments
 

Author Comment

by:fasse
ID: 38352030
Here is an example
1	0002	08/27/2012	4576024	09/10/2012	 0000000.00	 0000054.19	 0000000.00	DR
4	ap75194	2375194	 1	 1	0	 54.19	 54.19	00419	 
5	0001	tracking number here	UPS/USPS SERVICE CHARGE	5.00
1	0003	08/27/2012	4576025	09/10/2012	 0000000.00	 0000055.36	 0000000.00	DR
4	ap91912	0391912	 1	 1	0	 55.36	 55.36	00420	 
5	0001	Tracking number here	UPS/USPS SERVICE CHARGE	3.20

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38352058
Take a look at the LineInput Statement in Access help.

You will need to open the file for input, then read and parse each line, one at a time, then, after you have read all three lines, append one record to your table.

You should be able to use the Split() function to to parse the various fields into an array using the tab character as the delimiter.
0
 
LVL 40

Expert Comment

by:als315
ID: 38353776
Test this sample
TextImport27848587.mdb
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85
ID: 38353830
IMO the easiest way to do this is to use TransferText to move the EDI data into an incoming staging table, and then use standard VBA/SQL to move the data from the staging table into the live table.

This assumes that your incoming EDI file is structured (i.e. is Tab delimited, or is a standard CSV file), but most EDI data is very tightly structured, so that shouldn't be an issue.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38353923
Scott,  from the sample data, it looks like each "record" of the EDI file is composed of three separate lines of data, with different formats for each line.

Dale
0
 
LVL 85
ID: 38354418
composed of three separate lines of data, with different formats for each line.

Right, but there has to be some sort of correlation between those lines. In most cases the EDI format would define those - for example lines beginning with "ap" are a Sales order, and the line immediately after that is the shipping info .. .or something like that.

If you can get the data into a staging table it's a lot easier to work with. I work with EDI data a LOT, and it's always oddly formatted. Half of the battle is just getting it to where you can work with it (i.e. into a table).

We do often have to ready line-by-line, and of course that can work too. It's just more difficult to deal with, since it's hard to refer back to other lines. In cases like that we still use staging tables or arrays.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38354485
I guess the OP could define a "staging table" with enough fields for the longest row in the EDI file, with all of the fields being defined as string, since they are inconsistent.

Then use the TransferText method to get the info into that table, then write several queries that insert the data into a deployment table, but if each line doesn't have an identifier that defines the "row" it belongs to, they would then have to define that relationship as well.

For the sample data, I think Alexey's code is probably about right.
0
 

Author Comment

by:fasse
ID: 38355994
I am not that advanced in coding so I am going to have to read through it a couple of times to try and understand.  but what I do understand is it does just what I needed.  Thanks so much.
0
 

Author Comment

by:fasse
ID: 38356555
Well the code works on some files and not on others.  i get run-time error 9  subscript out of range.  I am guessing that it means some field is longer than the code can handle.

thanks
0
 
LVL 40

Expert Comment

by:als315
ID: 38356674
@fasse: I think you have different columns quantity in different files or sometimes you may have more or less then 3 lines in one record. Can you confirm that? Can you upload samples? What is 1,4 and 5 in first column? Can we use it as line number in record?
0
 

Author Comment

by:fasse
ID: 38357052
Yes you are correct sometimes there are several line 5's since it is the shipping line that holds tracking number.
Or if there is no tracking number line 5 may not even be there.

1	0002413	08/24/2012	4568891	09/10/2012	 0000000.00	 0000086.25	 0000000.00	DR
4	57768	6357768	 1	 1	0	 86.25	 86.25	00292	 PIF-275 INSTANT FENCE RECEIVER
5	0001	1Z843EW00334218590	UPS/USPS SERVICE CHARGE	.45
1	0002414	08/24/2012	4568892	09/10/2012	 0000000.00	 0000050.52	 0000000.00	DR
4	49677	1449677	 1	 1	0	 50.52	 50.52	00397	 3218SLID BSEMNT DBL WNDW32X18
5	0001	1Z843EW02221574107	UPS/USPS SERVICE CHARGE	14.00
1	0002420	08/24/2012	4568898	09/10/2012	 0000000.00	 0000072.75	 0000000.00	DR
4	34238	6834238	 1	 1	0	 72.75	 72.75	00402	 S36N FAN SHUTTER 36IN
5	0001	1Z843EW00351019136	UPS/USPS SERVICE CHARGE	9.00
1	0002410	08/24/2012	4570706	09/10/2012	 0000000.00	 0000592.78	 0000000.00	DR
4	93935	3193935	 1	 1	0	 592.78	 592.78	00395	 ESI2600IE 2600INVERT/GENERATOR
5	0001	1Z843EW11119864389	UPS/USPS SERVICE CHARGE	68.00
5	0001	1Z843EW11119864389	UPS/USPS SERVICE CHARGE	68.00
1	X0002283	08/24/2012	4570709	09/10/2012	 0000000.00	 0000000.00	 0000000.00	DR
4	00012	6000012	 0	 0	0	 150.27	 0.00	N / A	 5380415    COWA2135 VANITY COMBO WHITE    CAN NOT SHIP UPS/FEDEX/USPS
1	0002411	08/24/2012	4568887	09/10/2012	 0000000.00	 0000141.10	 0000000.00	DR
4	7250	0697250	 2	 2	0	 70.55	 141.10	00396	 50130198 GALV SHEEP TANK 44G
5	0001	995050744	UPS/USPS SERVICE CHARGE	56.00
1	0002412	08/24/2012	4568888	09/10/2012	 0000000.00	 0000059.38	 0000000.00	DR
4	13309	9113309	 1	 1	0	 59.38	 59.38	00394	 TORRIE BIRD BATH
5	0001	1Z1X25555348714675	UPS/USPS SERVICE CHARGE	22.00
1	X0002415	08/24/2012	4568893	09/10/2012	 0000000.00	 0000000.00	 0000000.00	DR
4	00012	6000012	 0	 0	0	 0.00	 0.00	N / A	 ERROR ON THIS LINE / ITEM
1	0002416	08/24/2012	4568894	09/10/2012	 0000000.00	 0000050.17	 0000000.00	DR
4	31330	3631330	 1	 1	0	 50.17	 50.17	00399	 SFP18F-S POLYFLAG/WHT POLE18FT
5	0001	1Z1X298wert6139489	UPS/USPS SERVICE CHARGE	22.00
1	0002417	08/24/2012	4568895	09/10/2012	 0000000.00	 0000162.56	 0000000.00	DR
4	03449	9203449	 1	 1	0	 162.56	 162.56	00400	 TETE-A-TETE GLIDER BRONZE FIN
5	0001	1Z1X29844448743492	UPS/USPS SERVICE CHARGE	65.00
1	0002418	08/24/2012	4568896	09/10/2012	 0000000.00	 0000025.26	 0000000.00	DR
4	88468	7688468	 1	 1	0	 25.26	 25.26	00401	 5275086 1.0GAL REPL PUMP 12V
5	0001	1Z1X29844447805060	UPS/USPS SERVICE CHARGE	2.00
1	X0002419	08/24/2012	4568897	09/10/2012	 0000000.00	 0000000.00	 0000000.00	DR
4	00012	6000012	 0	 0	0	 0.00	 0.00	N / A	 ERROR ON THIS LINE / ITEM

Open in new window

0
 
LVL 40

Expert Comment

by:als315
ID: 38357079
May be better to save lines 1, 4 and 5 to different tables? Lines 12 and 13 seems to be duplicate. Should we store duplicates?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38357099
Here's a Regex pattern for that:
((^|\n1))?((.|\n)*?)(\n1|$)

Since only the first match will include the leading "1", you will need to add this back in to your stored data.  How will you determine the key for each of these groups of lines?
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38357121
Sample with 3 tables and duplicates stored
TextImport27848587.mdb
0
 

Author Closing Comment

by:fasse
ID: 38583847
Works like a dream.  Just what I needed.  I have been using for awhile now with no problems.  Thanks so much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question