Results 1 to 5 of 5
  1. #1
    ne1gold is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    6

    Import data into MSAccess

    Im really struggling to import data into access from the attached excel document.Attached is a copy of the database of how it must look. I did it manually and it a very time consuming exercise as I have loads of these tables to create. Is there any easy way to copy or import the data to look the same similar. Help would gladly be appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you may want to build a macro to format the data into an importable format.
    the macro would build a new sheet, say: importJobs
    build data into the sheet,
    then save the file AND save a copy to a generic importing file say: c:\temp\Import.xls

    In access youd have c:\temp\Import.xls linked as an external table.
    then run an append query to import the data.

    then your steps would be:
    open new excel doc,
    run excel macro for reformat,
    close xl.
    open access,
    run import macro.
    done.

  3. #3
    ehld6 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    you may want to build a macro to format the data into an importable format.
    the macro would build a new sheet, say: importJobs
    build data into the sheet,
    then save the file AND save a copy to a generic importing file say: c:\temp\Import.xls

    In access youd have c:\temp\Import.xls linked as an external table.
    then run an append query to import the data.

    then your steps would be:
    open new excel doc,
    run excel macro for reformat,
    close xl.
    open access,


    run import macro.
    done.
    I am having similar problems as ne1gold. I have two tables I need to populate with over 500 rows of data in xl and instead of retyping, I thought it would be easier to "just import the files". Knowing how good xl is with importing data I expected the same from Access. Many hrs later I know that the opposite is true.

    If I would understand all about the macros and append queries that you are talking about (which I don't), I still would need to know what the basic rules are that Access expects me to stick to when building my xl spreadsheet. I am looking for those rules. Once I know them, it would be easy to just manually configure the columns the way Access wants it.
    Simple rules like:
    - the names of the columns have to exactly match the field names and their data type ( I got that)
    - the order of the columns have to match the field order in the table (do they? probably not)
    - if not, should the primary key be in the first column
    - if the primary key is the default Access autonumber, do you still have to provide that value in your datastream?
    - the data type should match ( more than likely, but Access has more data types than xl, so what is the match?)
    - etc.
    I have googled and looked at help... Nah. Many people must have had that same problem. Isn't this info available anywhere?

    Here I am trying to do a onetime append of data into existing tables. i have not tried yet to build new tables as part of the import process and then append the data from my existing tables into the new tables. Retyping would be the third and final option.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ehld6,
    What you have done by posting your question in this thread is called "Hi-jacking a thread".
    This is bad for two reasons.
    1) This is NOT your thread!
    2) Because it is not your thread, your question is buried - very few people will see your question. You should have started your own thread and referred to this thread as being like your problem..


    @ne1gold
    I did it manually and it a very time consuming exercise as I have loads of these tables to create. Is there any easy way to copy or import the data to look the same similar
    How many is "loads"?
    Are the formats of the spreadsheets identical to these two spreadsheets (one format or the other)?

    For the "Process Design & Productivity Improvement" spreadsheet - will/are there only 4 columns (E - H) that have data? Will it ALWAYS have only 4 columns?
    Same for the "Departmental Communications" spreadsheet - will/are there only 10 columns (E - N) that have data? Will it ALWAYS have only 10 columns?

    I think you need 2 more fields in each table: a field for Verified Date and a sequence field.
    The Verified Date allows you to select the 4 records from a specific spreadsheet. Lets say you imported 20 spreadsheets. How would you know which record came from which spreadsheet?
    The sequence field would allow sorting; column E, then column F, then column G, then column H.

    You also NEED to fix the field names. There should NOT be spaces, punctuation or special characters in the field names.
    I would also shorten the field names.... no more than 10 - 15 characters if possible.


    You could use automation to import the data from the spreadsheets.... but it is involved. How good are your VBA skills?


    Lets say you have a folder for the spreadsheets of both "Departmental Communications" and "Process Design & Productivity Improvement" types.
    You could loop through each spreadsheet in the folder.
    There would be two subroutines, one for each spreadsheet type (format).
    You would have 2 loops; the outer loop would step through the columns and the inner loop would step through the rows.


    Pseudo code/steps:
    You would open the spreadsheet
    Determine the name to know which spreadsheet type (Process or Department)
    Do some set up things
    Loop through the columns and rows
    Append the new records
    Close the spreadsheet
    Loop until all spreadsheets have been imported.
    Do clean up stuff

  5. #5
    ehld6 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    I apologize to all for trampling the rules by trying to "hi-jack the thread".

    I read and tested some more with importing from xl and have to revise my previous comments. I found that when building a new table, the import wizard is very helpful. Then I used an Append query to move the data from the new table into the existing one, which was also a breeze. Few little snags here and there, but I got what I needed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-04-2017, 05:06 PM
  2. Learn MSAccess by playing MSAccess Jeopardy
    By pkstormy in forum Sample Databases
    Replies: 4
    Last Post: 11-17-2016, 07:27 AM
  3. Replies: 4
    Last Post: 09-14-2016, 11:09 AM
  4. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  5. Compare msaccess data
    By dref in forum Queries
    Replies: 10
    Last Post: 05-14-2010, 05:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums