Import Excel not working

[MikeKD]

Hi Folks,

I'm having brain fade. My import isn't working and I can't work out what I'm doing wrong. Existing records are updated, but new ones aren't added.

I'm importing an Excel file, matching existing records and adding new ones.I've attached grabs of the script and relevant dialogue.

Can anyone see what I'm doing wrong please?!

Cheers,

Mike

 

 

[LaRetta]

Your comment on line 15 says you don't want to update existing records but only add new ones.  Still ... it should add any new records.  A few things to consider:

1. The Data type of Code is the same.
2. ? is a very bad idea in field names.  That can cause failures when dealing with external sources.
3. What does your import log say?

Edited July 11, 2017 by LaRetta
typo

[MikeKD]

Hi LaRetta,

The import does update the merged fields of matching records, but doesn't add (new) un-matched records.

1. Code is a text field in FM and is formatted as text in Excel - is there anything else I need to check?

2. The "?"s are so the field names are the same as the spreadsheet to speed up the field matching; I'm not sure that the excel file always arrives with the fields in the same order.

3. To my shame, I don't know how to find the import log (or that it existed!) - Googling now.....

Many thanks!

Mike

mmm, there's no mention on the import log of today's imports:

2017-07-09 23:16:50.198 +0100    PupilGradeV096.fmp12    0    tables imported : 1
2017-07-09 23:16:50.198 +0100    PupilGradeV096.fmp12    0    Import completed
2017-07-09 23:21:02.479 +0100    PupilGradeV096.fmp12    0    Import operations committed
2017-07-09 23:21:02.479 +0100    PupilGradeV096.fmp12    0    ==================================================================

Is the Import Log always in the same folder as the database file?

Cheers!

Mike

[MikeKD]

OK - I checked the autocomplete box on import this time and it worked.

Strange - I'd made a specific note to NOT check it when I did it last time!!

[comment]

I don't think playing 20 questions is a good method to get to the bottom of this. No one can tell why something fails without being able to reproduce the problem on their own first. The proper way to present this would be to attach the two files - preferably reduced to the absolute minimum that still shows the problem. Without this, all we can do is generalize and/or guess.

In general, I can think of two possible reasons why records will not be imported:

1. There are no records to import;

2. There are records to import, but they do not pass validation.

Now for the guess part:

24 minutes ago, MikeKD said:

I checked the autocomplete box on import this time and it worked.

First, I don't know what you mean by "the autocomplete box on import". I am going to guess it's actually the "Perform auto-enter options while importing" option. And I am going to guess further that one or more of your fields is defined to auto-enter a value and validated as Not empty, Validate always. And I am going to guess that the source file contains no data to import into this field.

 

[MikeKD]

Thanks comment,

Your guesses are right! Thanks so much for helping out again with so little information.

Sorry I didn't attach a file - both database and excel files have personal information of students. The primary key is provided by school and is based on student names so would need to be changed as well as names DoB etc.

Best wishes,

Mike

 

[LaRetta]

5 hours ago, comment said:

I am going to guess it's actually the "Perform auto-enter options while importing" option. And I am going to guess further that one or more of your fields is defined to auto-enter a value and validated as Not empty, Validate always. And I am going to guess that the source file contains no data to import into this field.

WOW!  Great guess!

[MikeKD]

Yep, pretty amazing giving the info I provided :-/

 

[LaRetta]

Indeed, Mike.  I should add that Comment isn't really guessing (even though he says he is) ... he is deducing and nobody does it better than Comment.  

[MikeKD]

He is quite amazing TBH.

But in fairness - the only reason I knew to select all records before the import was because of something you'd posted elsewhere previously. So thanks to you too :-)

 

[LaRetta]

1 hour ago, MikeKD said:

he only reason I knew to select all records before the import was because of something you'd posted

Welcome to the world of knowledge based upon the input of others.  This is why Stephen's FMForums is so powerful.  

I too have learned far more from these forums than any FM Help manual and I am beholding to the folks here.  And that is why I, like others, give back here to assist with questions every chance I get, and still can't rate myself an expert.  I will NEVER be an expert particularly since FM functionality changes version to version.  If I EVER admitted I was an expert, it would be boxing myself in from future improvement.  Smugness and ego is a dead-end and has no place in FMForums.  Most folks here only want to help.

Edited July 12, 2017 by LaRetta
corrected typo

[comment]

1 hour ago, LaRetta said:

he is deducing

Elementary, Watson.

[Lee Smith]

3 hours ago, LaRetta said:

Comment isn't really guessing (even though he says he is) ... he is deducing and nobody does it better than Comment.

Plus 1

Amen