Jump to content
MikeKD

Import Excel not working

Recommended Posts

MikeKD    7

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

 

 

Import Excel script.jpg

Import Dialogue.jpg

Share this post


Link to post
Share on other sites
LaRetta    478
Posted (edited)

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 by LaRetta
typo
  • Like 1

Share this post


Link to post
Share on other sites
MikeKD    7

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

Share this post


Link to post
Share on other sites
MikeKD    7

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!!

Share this post


Link to post
Share on other sites
comment    1,371

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.

 

  • Like 2

Share this post


Link to post
Share on other sites
MikeKD    7

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

 

Share this post


Link to post
Share on other sites
LaRetta    478
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!

Share this post


Link to post
Share on other sites
MikeKD    7

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

 

Share this post


Link to post
Share on other sites
LaRetta    478

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.  :wink3:

Share this post


Link to post
Share on other sites
MikeKD    7

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 :-)

 

Share this post


Link to post
Share on other sites
LaRetta    478
Posted (edited)
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 by LaRetta
corrected typo

Share this post


Link to post
Share on other sites
comment    1,371
1 hour ago, LaRetta said:

he is deducing

Elementary, Watson.

Share this post


Link to post
Share on other sites
Lee Smith    145
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.