Problems with Excel spreadsheet import

[vangogh228]

I have attempted to import an Excel spreadsheet of 409 records into an existing table. I got all my field names done OK, and all the data came in OK... EXCEPT:

In the table, I have six Y/N checkboxes. In the spreadsheet, I had the input clerk put in "Yes" and "No" in the appropriate cells. I got an error that said "There was an error during the import. Import not completed. 409 records were converted to NULL because of data conversion errors."

Once I simply deleted the columns in the spreadsheet that held the "Yes" and "No" data, the conversion completed fine.

Any ideas on how to import "Yes" and "No" data???? Thanks!!!

 

[Ally]

I did a test and it worked with the yes and no, so not sure why it's not working for you. What Data Type is the Yes / No field in Excel?

One way you could get round it is:

Convert (probably using Find and Replace) all your Yes' to -1 and No's to 0. Import this, then go into the design of your new table and change that field to Yes / No which will convert the -1's to Yes and 0's to No. (I tried this in my test and it worked - you'll get a message saying "the Data Type has changed and data may be lost", but it's ok. Blank fields are converted to No.

 

[mark curtis]

or try True or False

 

[The_Doc_Man]

The other approach is to use a query instead of a table.

For each column of the query corresponding to your Yes/No field, compute an expression

IIf( [YNField], "Y", "N" )

then export the query.