Importing an Excel Date

[The Missing Man]

I have a data base where I import an Excel file, but lately I am experiencing a date issue.

It has worked perfect for the last 5 months now all of a sudden the date has gone all screwy on me, some of the time. Some days it imports fine then all of a sudden it gets screwed up. Then it goes back to being fine again.

Do i have some kind of virus or something? Any thoughts??

I import as day month year but sometimes it reads it as year month day.

Any help appreciated.

Thanks in advance.

[Vaughan]

What OS and FMP versions?

[The Missing Man]

My OS is Mac 10.6.8

FMP is PRO Advanced 10

[The Missing Man]

Ok the date thing is happening again.

I have included an excel doc from which I get the date and then a simple data base with the date info.

What is going on. Sometimes it works and other times no. The original data comes from a doc on Yahoo.

Any help appreciated, see stuffed file below.

Thanks in advance,

DateIssue.zip

[Vaughan]

Works ok for me.

One thing: don't open a data file in Excel, it has the tendency to convert data -- especially things that it THINKS are dates -- and saves without prompting.

[The Missing Man]

On my machine it reads it as 2010, Nov,11

when it should read, Oct 11,2011. Does it read Nov or Oct for you?

How can I add additional info to the CVS doc if I don't open it in Excel?

Thanks for your input.

[Vaughan]

It imported as 10 Nov 2011 -- except for one record that is 10 Oct 2010.

The challenge is that "11/10/11" could be interpreted as 11 October or 10 November depending on the computer's date settings. Obviously this is not reliable. Ideal situation is to break the data into day, month and year fields.

Excel has a tendency to change data like "11-0223" to a date. Use a text editor to add data or even better convert the file to FMP and do it in there.

[The Missing Man]

But if all date content is coming from the same place shouldn't they all read the same way?

For one record to be right and the rest wrong is weird. They were all the same data.

I will break day month year into separate fields. I guess that is all I can do.

I still don't understand why sometimes it works and other times not. The data source and process is always the same. To work for 3 months and then for a few days to not work and then start working again just doesn't make sense.

Thanks for your help.

[comment]

I still don't understand why sometimes it works and other times not. The data source and process is always the same.

I don't think that's possible. Something must be different - either in the process of generating the source file, or in the target file (including the system that runs the target file).

Your original post mentions an Excel file, but your attachment contains a .csv file - that's hardly the same thing. In general, Excel is not a good choice for an exchange format.

For one record to be right and the rest wrong is weird. They were all the same data.

They are NOT all the same in your source file.

[LaRetta]

For one record to be right and the rest wrong is weird. They were all the same data.

Actually two records were wrong - 2/11/11 (record 13) which imports as 11/11/2002 and 10/10/11 (record 17) which imports as 10/11/10. If you change the file extension to txt it imports into a date field just fine as Oct and not Nov and properly imports those two other dates.

As Michael mentions, something has changed.

[comment]

2/11/11 (record 13) which imports as 11/11/2002

Note that the attached target file is set to 'Always use current system setting' - so " 2/11/11" could be imported as any one of three different dates, depending on how your system is set up.

[The Missing Man]

Thanks for all of your responses.

I figured that it must be source code but why would Yahoo change there code around sometimes, and as noted above sometimes only on some records?

Also sorry for my terminology. Yes the file is a csv file. I import it into excel so just called it excel, even though I still saved it as a csv file after making my excel changes.

I am thinking that the best direction to take is to place a month day and year field in my csv file and then I know it is always right. I was just trying to speed things up with less data entry on my part if I am already importing info that should have the correct date one would assume.

As for the use current system settings, it is good to note if I was to ever take my file to the masses but since this is just for my personal use, my system settings never change so this would always be a constant and therefore if it worked one time it should work all the time.

Thanks again everyone,

[comment]

I figured that it must be source code but why would Yahoo change there code around sometimes, and as noted above sometimes only on some records?

I am still puzzled regarding the exact nature of the problem:

In the CSV file you have attached, two of the dates are different from all the others. We don't know why, and perhaps they are supposed to be different - after all, they are still valid dates.

Regarding what you said earlier (in post #6):

On my machine it reads it as 2010, Nov,11

when it should read, Oct 11,2011.

The text in the CVS file reads: "10/11/11" (for most records). If your Filemaker file is set to use mm/dd/yyyy as its date format, there is no way it will import the dates in this CSV file as dates in November. It is equally impossible that two imports, using the same source file, the same target file and the same OS settings, will produce two different results.

I import it into excel so just called it excel, even though I still saved it as a csv file after making my excel changes.

That's something worth investigating - even if only to exclude it as a possibility. But we'd need to see the 'before' and 'after' for that.

[The Missing Man]

The files are downloaded from Yahoo. Is it possible that Yahoo changes up there structure for the CVS files every now and then and reformats the way they provide date information?

The next time it goes screwy I will post the bad CVS file and a good CVS file, but FYI i did not see any difference.