Export and Import losing first field..

[Aaron Briggs]

Hi, Strange this one.

I have a file with 17000 records in. I am planning to export records to an identical file off site as a backup. Instead of exporting the whole file (because of size) I am now exporting all the records.

This all works fine. The script exports all the fields after sorting them. The problem is importing them into a cloned copy of the same file. The import works but it didn't import the first field. All the other fields came in OK. I went back and manually imported the first field from the file as an update records. This worked fine and took in the info.

Next I tried making two export files. Each one with around half the fields in each. Again the exports work fine. When I came to import the first file it imports..all except the first field. The second file imports all OK.

Next I figured it may be the first field itself. Therefore I swopped the importing scripts so the second file is imported first then the second (I hope your following this??) Again it was the first field in the 2nd file that didn't import.

I'm scratching my head now. I then do a manual import of just the first field into the file, and surprise it takes the information in OK. Has anyone have an ideas why its not importing the first field of information.

This is the first time that an import has gone funny on me and really I can't see the wood for the trees now.

Any help appreciated.

[Raybaudi]

Is the import operation scripted ?

If yes, double check the script step ( or make a new one ) verifing the import options.

[Aaron Briggs]

Its all scripted and I have double checked all the export options and import options. I have recreated it a couple of times now and it still happens. I have also changed the sort order incase that was a problem. It just seems to be the first field. As I mentioned i have exported files bigger than this before and it all worked fine.

The field was an auto enter field and I have even switched that option off. The option to perform lookups etc has been switched off as well.

Its a strange one... I don't think the problem is with the export as I have looked at the .tab file and all the information is there. It does import back in, except the first field. When I reimport it manually after the scripted import it imports the first field. The problem must lie in the first import into the empty file but I can't see where its going wrong.

[Raybaudi]

First field ?

Uh, excuse me.. I have read first RECORD !

Can you reproduce the problem in a new file to post ?

[Aaron Briggs]

I have attached a copy of the script. This is the export script. The two files are named stockitems1 and 2. The list of the fields are also shown left to right they are 1 and 2 respectively.

The import script is identical except for the obvious (that is that they import the fields in this order). The first field of stockitems1 (stock ref) doesn't get imported if it is run first. Changing the import order to import file 2 first has the result that the first field (scrapped date) isn't imported.

I hope this image will suffice.

As I did mention this was originally one file, but was divided into two to try and work out where the import was going wrong.

[comment]

IIUC, you're saying that the export works fine, and the problem is with the import?

[Aaron Briggs]

Yes, no probs with exporting. All the correct info.

When i come to import it whichever file I start with it won't import the first field info. Although the info is in the tab file that has been exported. However if I import just the first field from the same file straight after the first import, it pulls in the info with no problem. So the problem must be with the first import of the data. I thought it was because it was an auto enter field, but I think I have eliminated that.

The import file is basically find all records, delete, import stockitems1 then stockitems2. The import order is the same field listing as the export order.

Edited September 19, 2008 by Guest

[Raybaudi]

You have sure an import problem, not an export one.

Have you tried to create a new field ( text ) in the import table and set it as the first field ?

[Aaron Briggs]

I sure have.

I have tried a new field approach, however if the files are swopped the first field of the second file is a date field.

I will try it again though...

[Raybaudi]

so your first field ( in the import table ) is a date field ?

and you wanted to import a text into it ?

[Aaron Briggs]

No I am not importing text into a date field.

If you look at the post the date field is the first field of the second file. The point being exactly that. The first import file is a text field and when imported first it doesn't import. To test that theory I swopped to importing the second file first., This field is a date field. The import tab file starts with a date field. I am importing like for like.

[Raybaudi]

Ok

Please attach the stockitems1.tab and stockitems2.tab generated by your export script.

[comment]

Did you check the field mapping in your import script? These things have a nasty tendency to break up if a field is deleted.

If that's not it, I suggest you post your importing file along with a sample of the source.

[Aaron Briggs]

I have just tried with just the stockitems1.tab file and the first field isn't importing. I created a new text field called test, and tried the import again and it didn't work.

PS The file size it to big to be posted, sorry

[Aaron Briggs]

i have tried to post this file, but it is to large to be posted here because of the limits.

The mapping is correct as I have gone over it several times. I will experiment with a smaller file, but I will try and get a smaller file uploaded with just a few records. Hopefully (perversely ) it won't work...if you know what I mean.

[comment]

I suggest you make a copy of the Filemaker file, delete everything that's not relevant to this problem (including data, of course), and save it again as a compacted copy. As for the text file, 2 - 3 records should be quite sufficient.

I trust you know files have to be zipped before you can attach them.

[Aaron Briggs]

cheers

I'm just doing that at the moment as we type. Hopefully it should clear things up. Thanks for the interest

[Raybaudi]

As for the text file, 2 - 3 records should be quite sufficient.

Didn't you export only a record ?

Back to the export script...

Can you explain these:

Show All Records <-- why ?

Sort Records

Go to Record [ First ] <--why ?

[Aaron Briggs]

Here is a clone of the main stock items file. Some of the relationships are not here, but that shouldn't matter. There is also one stockitems1.tab file. The import and export file scripts should work although you will have to point it to the correct location. I have tried this and when it imports the file it doesn't import the stock ref number. However if you manually import just this field afterwards it works.

There is a lot of redundant info, but the jist of the script is there.

Enjoy

[comment]

Password?

[Aaron Briggs]

I use show all records to make sure I export all the records and not a found set, go to the first record is just a thing I like to do.

I export all records. The quote you mention is from somebody else.

[Raybaudi]

Sort records is the lonely step that you really need before exporting.

[Aaron Briggs]

Sorry all

magic words are

Pat Admin (account)

patadmin (pw)

[comment]

I believe you are wrong about that, Daniele. In any case, I don't see that there is a problem with the export itself, so I don't see what's the point in zooming in on that.

[Aaron Briggs]

Sort records is the lonely step that you really need before exporting.

sorry what do you mean.

[comment]

The import and export file scripts should work

Which scripts are those?

[Aaron Briggs]

In the FMP file under scripts there is an Import Stock Item Records, it should work but you will have to show it the .tab file. Normally it would be hardwired in. Also I have disabled a couple of steps for ease of use. I did get it not to work (?) so they don't seem to effect the overall outcome. Anyway, you may see something different.

Cheers

[Raybaudi]

I believe you are wrong about that, Daniele.

I need a cup of coffee or ... I need to sleep

[comment]

That's rather useless, since if I point the script at a .tab file, the import map is not going to be valid anymore.

In any case, I have managed to reproduce the problem by just importing the tab file and mapping the first "field" to 'stock ref'. The records were imported, but the field remained empty.

I then closed the file, performed a recover, and tried again - and this time it worked. The same thing happened when I copied the 'stock items' table to a new file - again, the import went OK.

I suspect your file is corrupted.

[Aaron Briggs]

Cheers

I will do the same and see what happens.

I have managed to recover the file, but unfortunately the import is still not taking in the first field.

I would point out that if you import the file with the "perform auto enter and lookups" switched on, it will import, however it adds a new stock ref (which is an auto enter field on creation) and not the ref that is associated with the existing record.

I tend to think that this is where the problem lays. The stock ref is designated as unique, auto enter, serial number etc. However with the import it needs to keep the existing contents and somehow I think the formating of the field is effecting it, maybe?

Edited September 20, 2008 by Guest

[comment]

The stock ref is designated as unique, auto enter, serial number etc. However with the import it needs to keep the existing contents and somehow I think the formating of the field is effecting it, maybe?

No, I don't think so. If it were validated for unique, AND if the validation was set to validate always, AND if there were duplicate values, only then it would have an effect - and the result would have been that RECORDS with duplicate values would be skipped. The serial number kicks in ONLY if auto-enter is ON during import.

You can check this easily by dropping the tab file on Filemaker, thus creating a new file. Then define the first field as unique, auto enter, serial number etc. and import again. It will work flawlessly.

[Aaron Briggs]

Hi

Just tried that and your right. I create a new file, defined the field and imported. All OK. However, having spent the last 12 hours going over and over this, I'm not sure what it tells me about the original file at the moment.

I will give it a rest for a bit and come back to it tomorrow. Thanks a lot for your help and input and I will post anything I discover later...

one quick thought, you mention that duplicate records would be skipped. However, part of the import is to delete all the existing records first. Therefore all of the records will be new. I don't know anymore, its late here...

Edited September 20, 2008 by Guest
A final thought for today

[Raybaudi]

I have managed to recover the file, but unfortunately the import is still not taking in the first field.

Yes, only recovering makes that field able to import.

Be sure to have opened the recovered file before a new importing.

[Aaron Briggs]

Yes, only recovering makes that field able to import.

Be sure to have opened the recovered file before a new importing.

Yes I did that. But to no avail

[Raybaudi]

this is your file recovered and with only Stock ref imported