Access - Excel Data Import

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?
rdracer58Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
scifo_dkConnect With a Mentor Commented:
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.

//Scifo_dk
0
 
Dale FyeCommented:
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")
0
 
rdracer58Author 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.
0
 
Dale FyeCommented:
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.
0
All Courses

From novice to tech pro — start learning today.