Reading multiple line edi file into one record in Access

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

Select allOpen in new window

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.

Test this sample
TextImport27848587.mdb

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.

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

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.

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.

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.

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

@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?

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

Select allOpen in new window

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?

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?

Works like a dream.  Just what I needed.  I have been using for awhile now with no problems.  Thanks so much.