Access - Excel Data Import

Posted on 2011-04-19
Last Modified: 2012-05-11
I am trying to import an Excel spreadsheet into an Access 2010 database. There are several columns in the spreadsheet where the cell values are marked with "X" if a value of "Yes" was intended or left blank if a value of "No" was intended.

Is it possible to import these columns and have the "X" automatically converted to 'yes" and blanks converted to "no"? If so, how do I go about doing this?
Question by:rdracer58
    LVL 6

    Accepted Solution

    No, you can't.

    I would do a search and replace within the area where you have the X's, and replace X with yes. Blank = No automatically.

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Another option would be to link the Excel spreadsheet to Access, rather than import it.  Then write a query that actually imports the data from Excel into your Access table.  With this method, you could use the IIF() function to convert the X to "Yes", and the others to "No"

    IIF([SomeField] = "X", "Yes", "No")

    Author Comment

    Would it then be possible to link it to a new spreadsheet? The potential issue is that we have a need to consistently update the data through new spreadsheets.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    I normally use the TransferSpreadsheet method to link Excel spreadsheets to Access, then use Access queries to transform the data (as necessary) to get it into the format I want it in in Access.  I the next time I link that table from another spreadsheet, I don't have to worry about it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now