importing into a date field

[pigslow]

I have a CSV file that I import into one of my filemaker tables. One of the fields is a date field. All fields except for that one imports fine. What am I missing that is not allowing this one field to come in?

[-Queue-]

How is the date formatted in the CSV?

[pigslow]

date *3/14/2001

If I change the field type in my filemaker table to TEXT it comes in just fine

[Lee Smith]

including the * ?

[pigslow]

I am assuming so. The file was created running a query against a MySQL database using MySQL Query Browser. The results are exported as a CSV. From there I don't see any settings changes that can be made. When I open the file in Excel 2003 and go to format, it shows the first type on the list which is *3/14/2001. Since it is first on the list it could also be that there is actually no formatting what so ever. I am no expert on this.

[pigslow]

After changing the field to a text field and importing the data it came in with a different format. year/month/day

When I open this CSV file in excel it show as month/day/year.

I can see how this could create a problem. Is there a way I can fix this without having to do it to the file. There are too many records to import them all into one excel spreadsheet.

[Lee Smith]

You are probably going to have to isolate them by patterns, and correct them using scripts. There are some examples of how to use the Substitute Function in both a script, Menu Replace and calculations.

Before we can do much, it would be nice to know what version you are using.

I prefer to do this in a Text Editor, because I can use Grep Patterns to find and replace.

AND, I would diffidently be figuring out a way to uniform this file for future imports.

Lee

[-Queue-]

I assume that if you open the CSV file with Notepad, Wordpad, or the equivalent on a Mac, you would see the data in year/month/day format. If so, use a text field for importing, then use a calculated date field of

Date( MiddleWords( Substitute( textfield, "/", " " ), 2, 1 ), RightWords( Substitute( textfield, "/", " " ), 1 ), LeftWords( Substitute( textfield, "/", " " ), 1 ) )

Or, if you're using version 7, a date field with an auto-enter calculation of

Let( T = Substitute( textfield, "/", " " );

Date( MiddleWords( T; 2; 1 ); RightWords( T; 1 ); LeftWords( T; 1 ) ) )

[Lee Smith]

For both calculations, I get

This *3/14/2001

[-Queue-]

That is merely one of Excel's date formats. I believe it is converting 2001/3/14 to it.

[Lee Smith]

Hi JT,

I guess I missed read this, because I thought he said that the date showed as *3/14/2001 in his FileMaker File already.

Lee :woohoo:

[pigslow]

The *3/14/2001 was one of the excel formats. Not how the dates were showing in filemaker. I will give the calculation a try.

I am using v7.03 developer in conjuction with a Server 7 Advanced.

Thanks for the help.

Edited July 20, 2005 by Guest

[pigslow]

I tried it but I must be doing something wrong.

I copied and pasted from your post changing the "textfield" to the name of this field. When I do the import this field comes up blank.

[Lee Smith]

The date format has to be

2001/3/14

For it to work.

Lee

[Lee Smith]

The attached shows how to set up the date field calculation.

pigslow_.zip

[pigslow]

I downloaded the file and unzipped it but when I try to look at the field or database I get an error message stating that it is unmodifiable.

I take that back...I was opening it before I unzipped it. It looks the same as what I have. Other than some validation that was added by someone else.

Edited July 20, 2005 by Guest

[pigslow]

I see a difference in the format that I did not notice.

Instead of having "/" it has "-"

I changed the character in the substitute but I just get a ? in the field after the import.

[pigslow]

Thanks for all of your help. I was stupid but finally bumped into the problem.

I went to layout mode, date format, numeric seperator. Changed that from "/" to "-" and it comes in just fine.

Thanks again for your attention. I need all the help I can get at times.

[pigslow]

Ok I take that back. It worked on a small test import of only 5 records.

When I tried to do it for the entire file which was over 80,000 records just that one field did not work again.

Is there a limitation or would it do this if for some reason there is one of the 80k records that is formatted differently?

[pigslow]

I went through all of the records and noticed there are about 20 records that have no entry in this field.

Would that make this not work as a date field since it will bring them in as a text field?

[Lee Smith]

When you say it didn't work, what did it do? What did it not Do? Are all of the dates the same format as 2001/3/14?

I thought these records already existed?

Lee

:cool:

[pigslow]

When you say it didn't work, what did it do? What did it not Do? Are all of the dates the same format as 2001/3/14?

I thought these records already existed?

The calculation does work. The import of the 80,000 records does work. What does not work is this date field while bringing in these 80,000 records. I think that is because there are about 20 records that have nothing in this date field.

Would that stop the entire column from coming in?

[Lee Smith]

Would that stop the entire column from coming in?

No. It just would have skipped the date for those records.

Can you attach a sample of the Text File and Your Database?

Lee

:cool:

[pigslow]

This database is 1.8 GB. It is too big to try and attach. A cloned copy is 4MB.

Even that seems to be a bit large to post here.

[Lee Smith]

Clone "No Records" is 4 mgs?

Then just send a page or two of the Import Text File.

Lee

[pigslow]

This Sample file is a slice if the records being imported into this Filemaker table.

I find that if I change the field type to text.

Do the import (which does allow the data to fill that column).

Run a script to change the dates from yyyy-mm-dd to mm-dd-yyyy.

Then change the field type back to date.

Everything is fine...until next time I have to do this.

Sample.txt

[Lee Smith]

The dates all show as year-month-day "2004-07-06" which is easier to separate out to be "07/06/2004" than when it is a variable of two and single digits in these fields.

So, if you use this calculation in the Auto Enter Calculation box, it will convert these upon import.

Date(Middle(Date; 6; 2); Right(Date; 2); Left(Date; 4))

HTH

Lee

[pigslow]

That worked great. Thank you very much for your help.