Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The excel to access migration headache (Access 2000)

    (Edited by HansV to activate link - see <!help=19>Help 19<!/help>)

    Hi everyone,
    If anyone can help me with this, it would be so so appreciated.
    I have over 800 excel sheets that I want to import into an access database.
    Here is a sample of one of them...
    http://www.w-projects.net/PML-VL48010-1of6.xls
    (File has been checked and is virus free)

    Basically I just want to import all fields
    from A-7 to 0-7,,,,as well as the two fields called Ref.DWG and DocuNo at the top right.
    (interestingly, they both seem to take up multiple columns but one cell).
    Anyway, is such an importation possible?. Or any tool that can do it?. Your help on this would
    be very much appreciated, as it has had my mind in a twist for the past 2 weeks. And I'm quite
    new to this data migration phenomenon. Thank you in advance.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    As you probably know alreay, Access can convert Excel worksheets into Access tables fairly easily if the Excel worksheet is a simple table of columns (fields), a row of column headers (field names), and rows of data (records). However, your sample worksheet indicates that your conversion is a little more challenging (hence, your post, right?). I could envision a nice relational database that organizes all your data between a projects table, a drawings table, construction units table, materials table (your "description" column), and a specifications table (linking the project to the materials with the specifications needed (length, weight factor, etc.)). I'm guessing a bit on how your "business" is structured, but hopefully you get the idea.

    If you're familiar with VBA automation code (e.g., "running" Excel from Access) and your tables are organized in a consistent way (e.g., the Job. No. is always in the same cell and your columns from Row 7 down always have the same labels), then VBA automation is probably the best way to go. It takes some time up front, not only to create your base table structure (to park all the data), but then to write the code to "poke" into your Excel sheets, pull out the data, and save it to the right places in your Access tables. If your workbooks have a consistent format, then once you get one converted, the remaining 799 will be gravy.

    If you're new to VBA, this is probably not a good "first project." I've done quite a few Excel to Access conversions of this type and they take me 4 to 6 hours to set up (depending on the complexity, of course), but this is very dependent on your comfort level with VBA, automation code, not to mention relational databases, etc.

    There are other options (copying your heading information into new columns, repeating them for each line item in the table, then removing the heading information, and then using Access's import table wizard, and finally appending all the tables together). I shudder to think about doing this for 800 Excel worksheets, though.

    Hope this helps a little anyway.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    Well for a start I cannot save this Excel sheet, it requires some sort of authentication, so this will be off the top of my head. I tend to like to try it myself first then describe just what I have done. If you wish to email the Authentication Iformation User name and Password then try my email address in my profile.
    If all your sheets are the same format, I would:
    1. build a table manually of where you want the data to ultimately end up.
    2. in VBA just use the TransferSpreadsheet to import the sheet into a temporary table (not the one you have already defined), this will include everything from the sheet.
    3. read the 2 fields you want from Ref.DWG and DocuNo and save them.
    4. delete the first 7 rows from the imported table.
    5. build up an append query that adds to the table (defined in 1 above) from the import table. You may need criteria here to weed out any rows you don't need.
    6. update the table (in 1 above) with the saved Ref.DWG and DocuNo.

    This should be all that is required to import 1 sheet.
    You could build code around this to look at all sheets in a certain directory with the view to importing the lot (all 800 of them).

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    Would you like to define for me the table that this is going to?
    Before I do finalize anything I need to know this.

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    Hi Pat,
    The access table would consist of
    ItemNo, QtyReqd, Description, MatlGrade, Length(mm), Length c+f, width(mm),weight factor, Weight(kgs),S.Area factor, S.Area,
    MrNo., Free Issue, Remarks, Sbm-Cost, RefDWG, DocNo, Const Part, Rev.
    Its been hard trying to figure out which one to make the primary key. I thought maybe the refdwg, but that would'nt be right, as the there are many sub items
    to each refdwg in the pml. So I think an autonumber might suffice.
    Just something I'm curious to ask, is it possible for vba to extract the size from the description column and put into access as a seperate record. You see the column where it says for example
    PIPE, 6", Sch. XS, BE, SEAMLESS, ASME B36.10, Charpy per ASME B31.3, Section 323.3 at -45

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    If the 6" is always between the 2nd and 3rd comma it should be straight forward. What do you mean you want to put it into a separate record?
    What do you wish to extract from:
    STUD BOLT 3/4"x 100 LG. C/w 2 Hvy. Hex. Nuts, Hot DipGalv. As per ASTM A153, Impact Testing to 34J @ -101

  7. #7
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    Hi Pat,
    6'' means 6 inches. I'm supposed to calculate the sum of each part based on its 'size'. Which happens to be located in the description field.
    So the only relevant part from below is 6''. the rest can be put into description, and 6'' is put into a field in the database called 'size'
    PIPE, 6", Sch. XS, BE, SEAMLESS, ASME B36.10, Charpy per ASME B31.3, Section 323.3 at -45

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    Hi Mark
    I have to reask the question
    What do you wish to extract from:
    STUD BOLT 3/4"x 100 LG. C/w 2 Hvy. Hex. Nuts, Hot DipGalv. As per ASTM A153, Impact Testing to 34J @ -101

  9. #9
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    Hi Pat,
    I'm so sorry if I've been a bother. Your help is much appreciated.
    To the answer, I wish to extract 3/4
    The size field is not in any of the excel sheets, this 3/4 will be put in access in a new field called "Size"
    So I could calculate the weight of the items according to their 'size', as I would not be able to do that from the description alone. So in other words,
    i would want to calculate the weight for all items that have a size of 3/4 for example.
    Hope that's clearer, I take it your in the eastern states, I'm in Perth, 2 hours behind [img]/forums/images/smilies/smile.gif[/img].
    Thanks Pat.
    Cheers
    Mark

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    No Mark, you are no bother. I just didn't realise what the time was.
    So do you need to scan the description for the first " and go back to the previous space and get everthing between the space and the " for the size field?
    I take it you wish to put this field (Size) in the same table with the rest of this information?

    Also, are all the sheets in the same format? They certainly need to be to do what you are asking.

  11. #11
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    yep, thats it. And I also would like the field size to be put in the same table with the rest of the information.
    The sheets are in the same format. I can send you a couple if you like to judge for yourself.?

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    If you could send a couple of sheets, that would be great.
    I'll finish this off in the morning.

  13. #13
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    I've zipped up and sent u 50 excel sheets for you to look at. Thanks once again Pat [img]/forums/images/smilies/smile.gif[/img]

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: The excel to access migration headache (Access 2000)

    I have not finished testing all 50 sheets yet, but I have tried 2 and they seem to work.
    Have a look at the database and try it out.
    What you will have to do is to create a new directory of where you will put the sheets.
    The table [tbl Control] has one field which shows the path of where the sheets reside (the new directory you have just created). You should change this first then try the form [frm Import].
    Let me know how it goes.
    Good night. I'm not being rude, I'm just stuffed.
    Attached Files Attached Files

  15. #15
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The excel to access migration headache (Access 2000)

    Thanks so much Pat. I'll try it out and let u know.
    Go to sleep mate, or it will be sunrise before u know it [img]/forums/images/smilies/smile.gif[/img]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •