Pulling data from EDI converted file into access 1

[CMooreJr]

Hey all! OK, I have a company that sends data to another company via EDI. They want this to come to them now as an Excel sheet to be imported into access. Someone else is doing the EDI to Excel stuff, and here is a sample of the file she gave me....

ITEM CHO35
AVAILABLE
LOC/QTY 346 1 353 2 755 1
SOLD
LOC/QTY 346 0 353 0 755 0

OK, to explain, it is all for Item CH035.

Next line, store 346 has 1 availble, store 353 has 2 available and store 755 has 1 available.

Next line, store 346 sold 0, store 353 sold 0, and store 755 sold 0.

Now, all of these are in COLUMNS and not rows. I'm having problems importing into Access due to the way the data is broken down. any suggestions??

Thanks!

 

[Blorf]

This data can be normalized with vba code. First though, can you outline what the ideal structure would be like for you? I guess that because the number of stores may not be constant for every item, you can't just say store X, qty Y accross a record. I will wait to see what you are after, then help you with the code.





Ascii dumb question, get a dumb Ansi

 

[CMooreJr]

the IDEAL structure would be the following

Item Store number Number on Hand Number Sold

CH035 346 1 0
CH035 353 2 0


etc....that would be PERFECT!....

Thanks!

 

[Blorf]

Cool. First you are linking to the spreadsheet, or are you importing it? Or do you want that handled through VBA?

Either way, first one would create a working table, append records from the spreadsheet, and then run a short vba script to modify it.

Can you list the field names in the excell spreadsheet? Or what Access calls them after you link or Import? The code for this will be pretty short and sweet.


Ascii dumb question, get a dumb Ansi

 

[CMooreJr]

Well, they want it as simple as possible, so I guess if I could create an "Import" button that all they have to do is click on to start the process then all will be great. It will always be put in one folder, and I can have them name it the same thing everytime to help in the import routine. So the fields on the Access table would be

Date_Beginning
Date_End
Item_Number
Store_Number
On_Hand
Sold

This is the probelm I see. The way the data is in excel is as follows....dashes indicates a new row (') indicates a new column....

01/01/04 ' 01/07/04
--------------------------------------------
ITEM ' CBC1
--------------------------------------------
AVAILABLE
-------------------------------------------
LOC/QTY ' ' 353 ' 2
-------------------------------------------
SOLD
-------------------------------------------
LOC/QTY ' ' 353 ' 0


and that would be it! I see the probelm being the way the grid is set up in excel....thanks for your help!

 

[Blorf]

I know this is tedious, but I have one more question. The example above only includes store 353. What will the spreadsheet look like if there are more stores for the same item CBC1?

After I get that...

ChaZ

Ascii dumb question, get a dumb Ansi

 

[CMooreJr]

if there are more stores, there will be more columns with the store number and the quantity...for instance...

353 ' 2 ' 810 ' 3 4100 ' 0 etc...


there could be upwards of 40-50 stores, but MOST of the time thre will only be 2-10....

thanks again!

 

[Blorf]

Ok.

Does the spreadsheet have column headings at all? Or is it just the raw data.


Ascii dumb question, get a dumb Ansi

 

[CMooreJr]

just raw data unfortunetly....

 

[CMooreJr]

In fact, if you want, shoot me your e-mail and I will send you the xls grid so you can see how goofy it is...if you dont want to post your e-mail on here, just send it to my e-mail address...moorech@comcast.net

Thanks!

 

[Blorf]

lexicon_one@hotmail.com

Ascii dumb question, get a dumb Ansi

 

[CMooreJr]

Thnaks alot! It worked PERFECTLY!!!! I can't thank you enough for what you have done, other than giving you a big star!

-Chuck

 

[CMooreJr]

I sent you another e-mail...it worked great! The only thing I saw was that the
Date_Beginning and
Date_End

were not carried over from the first field of the xls file. (I think the dates were 4/20/03) and (4/26/03) this is how they will pull reports for the week/quarter, etc....can that be easily added?

thanks again!