Importing problem - extra spaces in some fields

[sbutler]

Have an issue with importing a .CSV file to FM7. After the import, some of the fields have blank spaces at the beginning and/or end of the data. The spaces are causing a problem with lookups because a lookup for value "ABC" will not match "ABC ".

I can remove the spaces after the fact with the TRIM function, but I would rather not have to introduce this additional step. This .CSV file is over 200,000 records and will be imported on a daily basis - so automation is critical. Any thoughts on how to stop the spaces from coming in, or automatically remove them, or have the lookups ignore them? I have attached a small sample of the data. Thanks!!!

IngData.txt

[Lee Smith]

Hi sbutler,

If you have any of these text editors; TextPad, Crimson Editor or HomeSite, you should be able to do Grep Pattern "Finds" and "Replaces". Once you have established a Grep Patterns that works for your file, you can save it for use when ever you need it. In BBEdit I run a Grep on multiple files at the same time.

If you need some help with your find and replace, let me know.

If you leave it to FileMaker, you are stuck with running a script or using separate fields and calculations, as discussed in this recent thread. Click here to see it.

HTH

Lee

[sbutler]

Thanks Lee. The data file is currently being imported to a SQL table and being used by an Access database. I noticed that the spaces are also present in this file, but for some reason when you do a lookup in Access it will find the values even with the extra spaces. It would be great if there was a way to tell FM to just ignore the spaces without having to do a calc or script. FYI, I am creating a new Quote System for my company in FM7 so we can ditch the one we are using in Access. I have another data file that needs to be imported daily, but this file can only be downloaded with a "|"(vertical line) as a delimiter. I don't see a way in FM7 to specify a delimiter other than the standard comma or tab... so I suppose this file needs to be converted before import. Both of these data files are currently imported into SQL tables - do you think I should be looking at using the SQL tables with FM as opposed to importing the raw text?

-Scott

[-Queue-]

Or you could make each imported data field an auto-enter calculation, with 'Do not replace existing value for field' deselected, of Trim(thisfield).

[sbutler]

Such a simple solution. I'm happy it works, but wish I thought of it!

Thanks!