Tips for accurate import from Excel to Access?

[vector39]

Hi all

So awhile ago, I imported an excel (copy of the original) file into Access and I noticed there were tablename$_ImportErrors. For the most part, the table data, looked correct but I noticed some things, for example, some columns appeared shifted in places that didn't belong. This prompted me to refer to my copy vs the original and I noticed data was not consistent (for example, the values 'Yes' appeared in cells that should have been listed as 'No'). After re-doing my copy and matching the original as much as possible, I'm happy to say this new file has accurate and consistent data compared to the original. Now before I re-import this new file with correct data, I just wanted your thoughts on somethings. Everything in the original excel file was entered in free text. What I did was split the columns into two for most cells. For example, a column called EmployerDeclarationComplete has a bunch of yes or no values followed by trailing text (e.g. Yes follow up Jan 18/17). I split this into two columns, one with yes/no values and the other with any following text by using codes similar to this

=IF((LEFT(AB2,3)="YES"),LEFT(AB2,3),LEFT(AB2,2))

for various fields. I also filled in most blank cells with a default value. I also incorported a macro used to split date fields with following text but under a condition (e.g. ongoing = start date plus ten years). Now my question is, will the macro disrupt the import process into Access? If so, should I simply remove it or save the file as a macro-free? Will the code

=IF((LEFT(AB2,3)="YES"),LEFT(AB2,3),LEFT(AB2,2))

also hinder the import as well? And finally the columns storing trailing I set as comment fields, most are blank. Should these fields still have something stored in them? (e.g. N/A)? How can I make this a successful import with the most consistent and accurate data as possible?

As always any thoughts and suggestions are much appreciated.

[aytee111]

Access imports the raw data from Excel, not the way it looks. Only trial and error will answer your questions. Keep in mind that Access looks at the first 8 rows to determine how to import the data, then imports the rest as per those determinations (for instance, a date field that is blank in the first 8 rows will be imported as text).

[ranman256]

I sometimes have to 'force' my fields to be text by putting a single quote in front of the value.
I run a macro to do this ,THEN import via
transferspreadsheet,
or
attach as an external table and run an append query.

[vector39]

I notice for the comment columns in excel with a code like

=IFERROR(RIGHT(J2,LEN(J2)-FIND("@",SUBSTITUTE(J2,"(","@"))+1),"")

won't allow me to replace blank fields with some kind of default value. Do you think this could be problematic?

[vector39]

Thank you guys!