Results are "shifted" when importing from Excel

[adam_rudin]

I am using ACT Pro 2012 and am trying to import data from an Excel spreadsheet into ACT.  After mapping the fields I preview the results and they are shifted.  I believe this has something to do with the fact that my spreadsheet has some fields that are blank (but won't always be the case).  To make it more confusing, I have another spreadsheet that I import frequently the results in to ACT and even with blanks it is never shifted.

Any help would be greatly appreciated!

-Adam

[adam_rudin · Accepted solution]

I figured out the problem.  I had several columns that had the exact same name (i.e. "Email Address", "Reason for leaving", etc.).  when this happens ACT does not 'shift'.  

Lesson learned:  don't have any columns named the same!

[ghollister]

Hello Adam,
If you remove the blank cells, does this resolve the issue?

[adam_rudin]

I tried removing the blank cells and the data is still shifted.  In the Import Wizard I can preview the mapping and it shows the data already shifted, so something is going on there.  Any ideas?

Thanks!

adam

[adam_rudin · Accepted solution]

I figured out the problem.  I had several columns that had the exact same name (i.e. "Email Address", "Reason for leaving", etc.).  when this happens ACT does not 'shift'.  

Lesson learned:  don't have any columns named the same!

[ghollister]

Glad to hear you figured this one out! I'll do some testing to this if this a program issue that be addressed. Were the columns with duplicate names side-by-side in your spreadsheet or separated by other columns?

[adam_rudin]

The columns were not side-by-side.

[Oldlaxdude]

I am having similiar problem

But none of the column names are similiar

[Oldlaxdude]

Same problem

2011 HP6 Excel 2007 W7

Did repair
Tried importing into new database

Tried as excel file and Comma

None of the fields match
Importing 30 records with 15 fields
Unique column names, no blank columns

[cthomas1017]

The real answer is that there are fundamental flaws in Act that the company does nothing to correct.  I've had this "shifting" problem for years.  Act support (paid support) can sometimes find the problem in a spreadsheet.  This happens in both excel and text files for me.  And it screws up exports as well.

Fundamentally, Act has not had a major rewrite of code and clean-up since the inception of their original windows product.  Don't expect problems to get corrected. Between their track record for not advancing the product and the move to the cloud, don't expect much from them ever.

[benhamilton]

Often we'll find that imports 'shift' things a column (field) over, the issue there is that there are quotes (i.e. ' or ") in the text of the column. 

This cause the import process to think it's the end of that column, start the next column.

The fix we've used is to remove any double quotes from the columns, then double quote all text fields.

i.e.

| Jane | Doe | Don't mention the "war" |

would become

|"Jane"|"Doe"|"Don't mention the war"|

Ben.