Results 1 to 5 of 5
  1. #1
    lazlo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    2

    how can I import 12xls to my database?

    hi,

    can you help me out? I have max12 xlsx file that cover the 12 month. I would like to import it to 1 my MS access in 1 big data table. my 12 file has the same order, so every column is the same every month. however the line number is different, like example in feb I have 80k lines, march 100k.
    I managed the import and Append the tables to 1.

    however I have a struggle, because the current month data is need to be changing . so I would like to link 1 table as a linked table.

    I did the following
    link the current month table, and tried to add the old data tables (as append for the linked one), however i can not do that because I have an error: operation must use an updateable query?
    how can I fix it? or any idea how can I manage to append 12xls to 1 ms access data table. 1 table [the current month] need to be linked, because the values can change during the [current]month

    thank you

  2. #2
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Before attempting to import the data into Access you must ensure data integrity. Date Column must contains valid dates, numeric column should have valid numeric values. All column should have unique column headings. Should not have any blank lines in the data area. When you merge all the data together do you have any column with some value to identify the data belongs to which month.

    An easier way to check the data validity:
    1. Highlight one month's data area (say for January).
    2. Right-Lick on the range and Select the option Name a Range... from the displayed menu.
    3. Name the range as January.
    4. Link the Excel Table by Range Name: January to Access.
    5. Open the Linked excel table and check the Data field values are showing correctly.
    6. Check the Numeric and Date field values are showing correctly in all records.
    7. Repeat this process for all the months and keep them linked to your database separately.
    8. Once you are sure all the data records are in correct order and when merged ensure that you will be able to segregate each month's data, if required.
    9. Create a make table Query with January Data, before running it open it in data view mode and check all cells have data and without #Error.
    10. Run and create Table
    11. create append queries for other tables and add data to the first table.

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Import the previous months in a table (say tblPrevious) and create a linked table to the current month xls (say tblCurrent).
    Then, union the two tables via a query. For example:
    Code:
    SELECT ..... From tblPrevious 
    UNION ALL 
    SELECT ..... From tblCurrent
    and use this query in your project.
    You have to repeat this process every month but, of course, you can automate it.

  4. #4
    lazlo is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    2
    Quote Originally Posted by accesstos View Post
    Import the previous months in a table (say tblPrevious) and create a linked table to the current month xls (say tblCurrent).
    Then, union the two tables via a query. For example:
    Code:
    SELECT ..... From tblPrevious 
    UNION ALL 
    SELECT ..... From tblCurrent
    and use this query in your project.
    You have to repeat this process every month but, of course, you can automate it.


    thanks I tried it but look like I do something wrong

    I go to create-queries-query design and go to sql view
    insert this query
    SELECT * from tblPrevious
    union all
    select * from tblCurrent;

    and in the query1 result I see a table but I also have a syntax error msg. I i click 1 column like example period number I have following error:
    syntax erro (missing operator) in query expressions period number

    the period number is the number of the month 1 jan 2 feb 3 mar etc..

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I am sorry, I can't replicate the conditions of your project. Try to provide a sample database (or just the .xls files) with a few records (with dummy data) that causes the problem.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-01-2016, 10:41 AM
  2. Import data from one database to another
    By blueman83 in forum Access
    Replies: 1
    Last Post: 09-01-2015, 09:01 AM
  3. Import data from one Access database to another
    By wvinton in forum Import/Export Data
    Replies: 4
    Last Post: 10-17-2014, 09:21 PM
  4. Replies: 43
    Last Post: 03-27-2014, 01:51 AM
  5. Cannot import objects from secured database
    By focosi in forum Security
    Replies: 2
    Last Post: 09-10-2011, 02:33 AM

Tags for this Thread

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