Importing Data from Excel

[nfregistrar]

Using FP5 I am importing text data from Excel. I am having a problem with 2 pieces of data. The 16 digit credit card number is being rounded at the 15th digit. i.e. 6011222233334444 from Excel comes out as 6011222233334440. Also the expiration data (mm/yy) gets messed up. i.e. 06/06 (June 2006) becomes 6/6/2004. Data on both ends is specified as text. :?

[Lee Smith]

What file format are you using for your save in Excel?

I just tested what you want to do and got it to work.

The fields in Excel are Text.

Save the file as "Worksheet 4"

Select Excel as the file format when opening it in FileMaker 6. I couldn't test in v5, as I'm don't have it available at the moment. However, if this doesn't work for you, let me know and I'll check into it further ths p.m. for you.

HTH

Lee

[Fitch]

Are you sure the fields you're importing into are text fields? A number field has only a precision of 15 digits (prioe to FM7), so that's clearly what you're experiencing. A text field cannot round a number or evaluate a date.

[nfregistrar]

Hi Lee:

Thanks for your reply. I was using Excel workbook as save. Data is text in Excel. I tried saving it in worksheet 4. Same result. Excel is 7.0.

Data displays OK in Excel. Fields in Pro 5 are text.

If there is a way to select Excel as file format in v5, I'm not aware of it.

Dave

[LiveOak]

I'm with Fitch, "something you know, ain't so". If you are truly importing from a text field to a text field, the problems wouldn't occur. With a number and date field someplace in the import chain, such problems are altogether possible. Attach example excel and FM files and we can have a look.

-bd

[Bekah]

i've had it happen that if I drop my excel file onto shortcut and FM creates the file for me, it takes excel text fields and makes them dates or numbers if they appear to FM to be that. FM is pretty smart. But I've really had it do this. It did it with date text Excel fields and defined the new field in FM as date. Maybe he's not importing a standard way with the FM fields alreadY existing as text. Aren't there several ways to get that stuff into FM? What exact way is being used?

Uh, sorry, I don't know. Nevermind.

[nfregistrar]

Here are my Excel test file and the FP5 file.

testfiles.ZIP

[nfregistrar]

As a follow up, the script used to import is called data. Camp.fp5 is password protected. Email me if you need the password.

[LiveOak]

The "data" script didn't do anything, probably no import privlege without a password. You can either send a password or an unlocked version of the file.

-bd

[Lee Smith]

The excel file you attached to your post has the field formatted as NUMBER.

Yes, FileMaker 5 does have the ability to either Open or Import an Excel file. It is an option of File Type you can select in the pull down menu at the bottom of each.

HTH

Lee

[nfregistrar]

You guys were right. It was supposed text data. My script was referencing the wrong folder which had an older version of the Excel data in which it was a number. Thanks to all for your time on my boondoggle.

[nfregistrar]

hi bd

You were right "what i knew wasn't so". Wrong reference in my script to older version of Excel file. Thanks for your help.

Could you please remove my attached files?

Dave