Importing Questions

[nualac1365]

Hello,

 

My issue is that when I import, blank records are being imported along with all the other records.

 

When I import an excel worksheet that contains, for example, 30 records, for some reason, when I import that page, I also get hundreds of other blank records. I then have to manually delete each blank record from the filemaker database.

 

How can I avoid this?

 

Thank you.

[Techphan]

The same thing has happened to me with Excel files.

 

Rather than try to "beat my head against the wall" figuring out what went wrong and how to fix it, I just created a set of buttons (Delete One Record, Delete 5 Records, Delete 10 Records) to delete the blank records after I sorted on a populate field.

[HBMarlowe]

When I import an excel worksheet that contains, for example, 30 records, for some reason, when I import that page, I also get hundreds of other blank records. I then have to manually delete each blank record from the filemaker database.

 

I am no expert with Excel, but I have seen this a time or two. I solved it by copying and pasting only the desired rows into a new Excel file, saving it, and importing from there.

[nualac1365]

Thanks to you both, I'll give it a try....

[MikeAz]

Hi,

 

Not sure if this helps, but I had a similar problem that I solved by using a Find and Delete Script step. I found it easier to solve the problem in FileMaker rather than in Excel.

 

I was importing data from an Excel file into two fields SchedItem and SchedDescription, and usually the Excel file had irrelevant information in the SchedDescription column. So what I did was create a script that Imported this file then ran a search to find the records with an empty SchedItem field (when creating the search parameters for SchedItem just add an = sign and this will find all empty records) and then Delete All Records. This finds all records with nothing in the SchedItem column and deletes all the records it finds.

 

If I wanted to find only records that were completely empty I would search for SchedItem[=] and SchedDesc[=]. Unfortunately though, if someone had entered a space or several spaces in the SchedItem cell in Excel the search would not recognise this as an empty field (because obviously there is data entered in it). I'm trying to fix this by adding many other search parameters, such as delete if SchedItem [= ] (equals one space, two spaces, three spaces, etc. up to twenty spaces). Therefore my search will look for 21 different entries in the SchedItem field and delete all the returned results.

 

This might give you some ideas about how to remove empty records in your import by using a script. Hopefully it helps. I'm no expert, but if you have any further queries please feel free to post again and I'll be happy to help.

[Josh Ormond]

Just for anyone else reading this...the problem almost always relates to what Excel recognizes as the "Last Cell".

 

You can paste into a new xls or delete all the rows below the last row in the xls. You can see what Excel views as the last row by moving the Vertical Scroll bar indicator all the way to the bottom.

 

Because it resizes based on the content, sliding it all the way to the bottom should show you the last row of data. If it goes beyond your last row of data, delete the rows (the entire row, not just the data) between that point and the row after your last row.

 

FileMaker should then only import the relevant rows.