Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: Importing records as fields

    Hi,

    I have an Excel sheet with data that can be imported into Access.

    The data in Excel is laid out in columnar style so that when it's imported, the information that I need as fields gets imported as records.

    I want to construct that data into mailing labels.
    Is there a simple solution (besides killing myself)?

    Thanks. . .

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panhandler
    Hi
    Hi

    Quote Originally Posted by Panhandler
    Is there a simple solution
    Well yes actually - there is. It's...
    Quote Originally Posted by Panhandler
    (besides killing myself)?
    Ah crap



    You could import and then apply a crosstab-make-table query on that. Or if that wouldn't work - fancy posting some sample before and after data (enough to make it clear what you want)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    6
    The data is in Excel and looks like #1
    I want to get it into Access like #2
    Does this make sense? Is it easy to accomplish? Or not?
    Attached Thumbnails Attached Thumbnails bm1.jpg   bm2.jpg  

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh dear.

    No - that looks like a total nightmare. It is a salutary lesson to never allow anyone to store anything in Excel prior to making you import the data into a database if at all avoidable (I don't know your situation obviously but I learnt this lesson the hard way too).

    Anyway - my #1 choice would be to get typing and retype the whole lot in to the database. If you don't have too much data it might be quicker than sorting it out.

    Otherwise I guess you would be best off having a go at writing a load of excel formulas to pivot the data. IF the format of the data is always the same (i.e. each record is always 9 cells high and they are always spaced 1 cell apart and cell 1 is always business name, 2 contact name etc) then this should not be too bad. You will have about 9 empty rows per contact once you import the data but you can delete these easily enough.

    BTW - if there is any chance you will ever have more than one contact per company, or need to keep a historic record of contacts, I would look to put these into two tables (Company and CompanyContact) once done.

    HTH

    EDIT - actually I think the Excel formula thing wouldn't be too onerous. Only type if you have very little data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Like the attached.
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2006
    Posts
    6
    Its a large Xcell list. (and there's another one just like it waiting for me)
    Thanks for taking the time to mimic the excel sheet and showing the forumula.
    It looks cleaner than doofing myself.

    I'll have a go at it. (forumlating the spreadsheet)
    Your idea looks do-able and has springboarded some new ideas for me too.
    Thanks. I'll get back with the results when done.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would personally link to the Excel spreadsheet. Then append Columns B, C, and D into a table with an AutoNumber field (this should maintain the order of the spreadsheet in the table). Then write some code that reads every n records storing the data into variables then storing the variables into another table.

    I am assuming that the spreadsheet looks like the first two 'cells'.

    You might even be able to link to Column A and use the field as the column header.

    For example:

    rst.Fields(ColumnA)=ColumnB

    You will have to make sure ColumnA is not null or "".

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panhandler
    Environment: piles of paper, notes and books with spots of wood desk showing through
    Lol - love the sig. You wouldn't be spoofing me would you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2006
    Posts
    6
    Quote Originally Posted by pootle flump
    Lol - love the sig. You wouldn't be spoofing me would you?
    Sorry to report that no, it's no spoof. Its more of an insight to my technical challengedness.
    DCKunkle posted something that was way over my head - like a foreign language between two alien species on other planets.

    Your posted suggestion is working. Your attachment was a great "how-to" and I thank you for taking the time to type it all out for me.
    I tested it - and it works for the problem at hand.

    So yesterday I spent most of the day copying and pasting until my eyeballs felt like twisted rubber bands. And then I did a little bit more. And today will be more of the same.

    Thank you for being on this forum and taking the time to help me out. The knowledge pool here is great - like a huge deep lake and I'm just at the very edge. Great forum!
    Thanks to you.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panhandler
    So yesterday I spent most of the day copying and pasting until my eyeballs felt like twisted rubber bands. And then I did a little bit more. And today will be more of the same.
    Oh no - please not

    Get a blank sheet. Type anything into the cell. Notice the bottom right corner is a black square? Hover your cursor over and it becomes a cross. Left click, hold it down and drag down a few cells. Release and the cell content get copied down the page.

    You can highlight a line of cells and do the same and the formulae are copied too. So - you can drag it down the whole of a sheet in seconds rather than copy and paste.

    HTH and saves you some time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2006
    Posts
    6
    If I could, I would.
    The original data is on line - it's THAT data that is being copied and pasted into an excel spreadsheet.
    Not the way I lead you to believe, eh?

Posting Permissions

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