CSV Date Import Issue

[posey]

I hope that this question has not been already posted, but suspect that the question is right out there and I'm missing it.

I'm using fmpro 6 to keep track of information requests. I download information from the website into a .csv file and then import it into the fmpro database. The date format in the csv file is

x/xx/xxxx x:xx:xx PM (or AM)

I'm trying to format the field so that it is only

xx/xx/xxxx.

Can anyone help me come up with a script so that it is reformated every every time that I import?

I've tried all sorts of things to no avail - including modifying the csv file before importing it. That corrupted my database beyond belief. Thanks in advance.

[John Mark Osborne]

This is actually a timestamp which is a combination of date and time separated by a space. You can import the data into a date field but you will need an auto-enter calculation to clean up the data.

Date(Month(MyDateField); Day(MyDateField); Year(MyDateField))

This formula will extract just the date information and strip off the time information. Attach it to your date field as an auto-enter calculation. When you import, make sure to check the option to "perform auto-enter operations" on the last dialog.

[posey]

I'm sorry to bother you, but I'm getting a "?" in the field when I enter that equation. I accidentally put in FMPro 5 instead of FMPro 6 (sorry about that) - could that be the issue?

The name of the field is "Date Received" and it's in a text format. I created a second field called "Date Received_ac" and did this

Date(Month(Date Received), Day(Date Received), Year(Date Received))

as the auto calculation. I tried to make the auto calculation in "Date Received" but it said that it was circular and wouldn't permit it.

Then I changed Date Received to date, there was nothing in the field at all. Lastly, I changed it to a number and it went back to being a "?"

Is there something that I am missing? Thanks again.

[Lee Smith]

You are going to need to identify the parts a little more:

If your filed date data is consistent as 01/01/0001 in format, then try

Date(Left(YourField, 2), Middle(YourField, 4, 2), Middle(YourField, 7, 4))

If this pattern isn't consistent, let me know and I'll provide a different calculation.

HTH

Lee

Edited July 7, 2006 by Guest

[Lee Smith]

Update, I worked out this calculation that seems to work for any data from

1/1/06 to 01/01/2006

Date(Case(Length( LeftWords(YourField, 1)) = 2, LeftWords(YourField, 1), 0 & LeftWords(YourField, 1)), Case(Length( MiddleWords(YourField, 2, 1)) = 2, MiddleWords(YourField, 2, 1), 0 & MiddleWords(YourField, 2, 1)), Case(Length( MiddleWords(YourField, 3, 1)) = 4, MiddleWords(YourField, 3, 1), 20 & MiddleWords(YourField, 3, 1)))

I'm not sure if this can be streamlined for version 6, but if it can, i'm betting on comment.

HTH

Lee

Edited July 7, 2006 by Guest

[John Mark Osborne]

My apologies. I thought you were importing into FileMaker 8. I didn't check Lee's formula but I'm sure it will work.

[posey]

Thank you! Thank you! Thank you! Worked like a charm! That problem was making me absolutely batty and making responding to requests impossible.

[Lee Smith]

U R Welcome.

Keep in mind though, if you decide to convert this file to v8 down the road, this is one of those calculations that you will need to be changed, so use the one John Mark provide when the time comes.

Lee

[posey]

Hi ... Sorry to bug you again. I'm having a hard time finding by the date ranges now. I was able to plug in the simplified version of equation. Now when I try to do a search by range it's not pulling up all of the records. I know that there are 33, yet I'll get 2 or 18.

So then I'll try to do a sort by date range and the sort is all mixed up.

I know that this is vague and confusing since you cannot see what is there and what isn't so I'm attaching a copy of the file to this on the off chance that you'll see what I need to change. Thanks for any help/guidance you can give me.

Conversion.zip

[Lee Smith]

what is the password

Edited July 15, 2006 by Guest

[posey]

Duh! Sorry about that. It's

Admin2006

[Lee Smith]

I'm not sure what you are trying to find?? The script "findrange" found 22 records? BTW, the found set had a year of [color:red]0006

You need to use the second calculation I provided , the longer one, because the data is NOT consistent as xx/xx/xxxx and some had the time and some didn't

This is that same calculation using your field [color:blue]Date Received

Date(Case(Length( LeftWords(Date Received, 1)) = 2, LeftWords(Date Received, 1), 0 & LeftWords(Date Received, 1)), Case(Length( MiddleWords(Date Received, 2, 1)) = 2, MiddleWords(Date Received, 2, 1), 0 & MiddleWords(Date Received, 2, 1)), Case(Length( MiddleWords(Date Received, 3, 1)) = 4, MiddleWords(Date Received, 3, 1), 20 & MiddleWords(Date Received, 3, 1)))

HTH

Lee

Edited July 15, 2006 by Guest

[posey]

Thank you. It worked. On to the next question ???