We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access - Excel Data Import

Medium Priority
496 Views
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?
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Yes,

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.