Text-date conversion must not break!

[LaRetta]

I am importing data from a payment center (checks, not our credit cards which works perfectly). The csv format provides a header row record which has information I need to capture (there are no field labels). I will import all lines as records, write the header (first record) to variables then delete the first record. So far so good. But I have two problems:

1) No year:

The remaining rows are PAYMENTS and I must be sure this doesn't break (this date is used for Aging and assessing Late Fees). Payment lines have the date as:

18-OCT

6-NOV

I decided to convert this garbage to standard date and I figured I could just use the ProcessDate's year (it only exists in the header - first record). But that method will break at year-end when the ProcessDate is January and the records are November and December so I needed a boolean test comparing months. Does anyone see any problems with this calc that I'm not planning for?

Let ( [

m = 1 + Div ( Position ( "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; RightWords ( textDate ; 1 ) ; 1 ; 1 ) - 1 ; 3 ) ;

d = LeftWords ( textDate ; 1 ) ;

y = Year ( ProcessDate ) - ( Month ( ProcessDate ) < m )

] ;

Date ( m ; d ; y )

)

Note: There is no ProcessDate field. It will have to be variable or (see Problem #2)?

2) Global, variable or loop:

I can't use an auto-enter calc to get this true year (ProcessDate) because the year will only exist in the first record (header). Subsequent rows (in that ProcessDate column) contain the Invoice numbers (text field). No matter how I approach it, it seems I'll have to loop through the imported records, setting the proper date (using the variable). Other items in the header are TotalRecords, TotalDollars. I planned to write that to variable then (after deleting the header record), compare the counts and dollars to verify it worked correctly. Neither can I write just one record (the header) to a global to hold this information during the import (to then use global as part of my auto-enter) because subsequent records aren't global and they share the same fields! Luckily, I have no data mis-matches between the header row and the detail except for the date which can write to text field (invoice numbers) okay. That is, unless I change my solution to International, right?

If anyone spots any gotchas on this calc or ideas on handling that header record, I'd appreciate knowing as this is a critcal import process. I considered importing the header to another table (as a 1:n to the detail) but ALL the csv will import; either way, it's clunky. And I would like to fire the programmers who came up with this horrific export (it is the only format available).

LaRetta

[comment]

Why can't you use the year of Get(CurrentDate) to perform the same test?

[LaRetta]

I considered that but balked for two reasons: 1) The Header record also contains our Account Number (and other important items) which I wanted to write to each record; just in case we end up getting two accounts and we want to keep them straight and 2) I envisioned some day a need to 'go back in time' and import for some reason (such as catching up on imports from another account number). If I use the current date, it will surely break but if I use the ProcessDate from the csv itself, I will be safe. The ProcessDate is one of several items written at random in the first record that I think I should capture in case (per batch) something changes that we need) and I figured if I understood what to do with ONE field, I could work through the rest.

I can fuss a bit too much sometimes, worrying about What-Ifs but I guess it's better to over-plan than under-plan.

UPDATE: The ProcessDate is NOT the date of download; it's the date their processing center deposited those checks. The textDates are the dates the checks were GIVEN to the processing center - the date we start tracking aging. There is a 3-4 week lag, it appears.

Edited November 26, 2006 by Guest

[LaRetta]

It just hit me ... what about a cartesian join? It'll only see the first record - that header record, right? Will import auto-enter from related table based upon cartesian if the first record is part of same import? I will get to work on that idea ...

[LaRetta]

Nope. Cartesian doesn't appear to work. I'm unsure why. The auto-enter calc, even after changing this line: y = [color:green]GetAsDate ( Year ( cartesian::InvoiceNumber ) [color:green]) - ( [color:green]GetAsDate ( Month ( cartesian::InvoiceNumber ) [color:green]) < mo ), still produces ? in all records except the Header which it evaluates incorrectly - rightfully so, since the first record's textDate field holds our company NAME.

I guess I'll just loop again. It was probably silly that I worked so hard on eliminating the loop but it would have been so much simpler to let auto-enter handle it. I'll write everything to variables then unless someone comes up with better method. :)

Edited November 26, 2006 by Guest

[comment]

the ProcessDate is NOT the date of download

I don't think that matters. As long as the month of the check cannot legitimately be ahead of today's month, it MUST be from last year. But since you plan on going back in time, the assumption doesn't hold.

To me, a script AND a temp table AND a relationship seems overkill. I would settle for a script, and make sure I have an auto-entered creation date/timestamp so that individual imports can be isolated in case of something going wrong.

[LaRetta]

Yeppers, all tables have auto-enter creation and modify timestamps. Okay, script it is. I am curious why cartesian wouldn't work here on setting the date correctly. Any theories on that?

[comment]

I don't know, it works for me.

[LaRetta]

Interesting. I would like to understand why my test file breaks. Were you doing it like this example (attached) or were you approaching it differently? I've included sample text from an identical csv. Regardless, I'll script it; but I dislike not understanding something particularly when it's working for you. :wink2:

ImportChecks.zip

[comment]

I can't find the exact break point. I can get the year alone. I can get a PART of the calc, but when I add more to it, it starts acting weird. I think it has something to do with the relation being a self-join and the records not yet committed.

I'll return to this if I can think of a simpler test that would pinpoint the limitation.

[LaRetta]

I figured commit as well because a regular join doesn't appear to work either (at least not in my test of it); either with cartesian or join on = using a global (to another table). I will add this to my 'must figure out' list.

Thank you for taking a look. :wink2:

[LaRetta]

I can't even use a variable and set the date field. Help! Data viewer shows it's grabbing the date correctly! This doesn't address why the cartesian wouldn't work but it might be the file type. Here's my current problem (file attached). I'm scripting setting a variable. I have 3 scripts (with various configurations using GetAsDate). None of them work.

I suspect the file type because: With the original csv file I attached, I had opened it (it auto opened in Excel) to modify the data (for confidentiality). Then I saved it as csv again. It works on the import and setting the date using the variable. However, that is NOT how the original files come to me. The files (before they are modified) break and set an incorrect date. So this time, I modified the csv using notepad, removed sensitive info and they all break.

I've attached my script and the original csv (along with it's modified-in-Notepad version) as well as another date. Nothing works! What is different about Excel that would make it work when it will NOT work as straight csv? I don't want to have to open every single csv in Excel first, save it again as csv then import.

I don't think I like csv format.

LaRetta

ImportChecksMOD.zip

[LaRetta]

There are funny square brackets at the end of the original file, which are (for some reason) removed by Excel. I missed them originally.

This will be a daily import and this is the only format available. I tried searching and deleting the squares (in FM) but couldn't locate them. Did they import in and that's why it breaks because of those EOL things? Lord help me if a User must open Excel and save them again just to import. I think I'll tell those programmers what I think of their work. Or maybe I should contact Bill Gates directly. :)

[LaRetta]

Uncheck 'Do Not Replace' and the cartesian method works. Amazing how we can spend hours and hours on something when one little checkbox fixes it.

Setting the variable will not work with these raw files. Setting the variable only works if I've opened it in Excel and saved as csv again - it removes those little squares. You can bet I won't get tripped on THIS again. Thank you Fenton!

One little checkbox . It isn't the first time a checkbox has tripped me up. But I knew auto-enter must be checked but I didn't think 'Do not Replace' needed to be.

[LaRetta]

Nope. This only works with cartesian if I am using the MODIFIED Excel csv format. It still won't work with the csv with squares at the end - the raw downloads I receive (even if I uncheck 'Do Not Replace.' So I'm stuck - can't use variable and can't use cartesian. I will have to open every file file every day and save as Excel csv just to make it work (so Excel can remove the squares).

Major bummed. This needed to be VERY user friendly.

If anyone comes up with something, I would be most appreciative. And my other computer just blew up. On the bright side, it means I MUST get my Mac this week. It's wonderful how things can balance, isn't it? AND!!! It wasn't because I didn't click a checkbox! YAY!

[LaRetta]

I feel like I'm chatting away to myself. Excel did more than remove the squares. It changed the format to 17-Oct. The RAW format in the csv files is 10/17. Of course my calc was breaking.

Many things learned tonight; mostly that I need a 30" monitor to see the text. I didn't expect Excel to CHANGE a format of a csv just by OPENING IT! But it does. Even when I click into a cel it still displays 17-Oct in the bar above (which shows the 'actual' data). Joke. If you look at the raw files, they all are 10/17. Of course, I used xWords to split the month from day and ... now you know the rest of the story.

I think I'll add some Bailey's Irish Cream in this coffee. It'll be a long night. While working, I'll think about my new Mac with a 66" monitor. :)

[LaRetta]

Oh, one more thing: Comment was right ... it STILL will not consistently work with the cartesian join. It only works sometimes so I've reverted to using variables which work consistently.

[comment]

Excel is not a good tool to view raw data. In any case, I did my testing with my own data, and I believe there's a kind of bug here. I CAN get auto-enter to work up to a point where the calc gets too complex (or so it seems). I wouldn't trust this method anyway - I have seen too many problems with lookups on uncommitted records.

Why don't you simply import the records, go to the first record, do a replace and delete it?

[LaRetta]

That sounds like a good idea but it doesn't put that first record's data in the same fields. It places the TransDate in the Invoice field, the AccountID in the

I just got it. Well, I have finished the entire scripting to variables. Just think what I've (hopefully) learned from all of this. Now I'll learn how quickly I can tear everything back out and make it simple. And I won't ever use Excel again for looking at raw data because it changes the data to what it thinks it should be even when you re-save as the same file type! I'm removing that file association right now.

Replace Contents, huh? God, Michael, you always like to do things the easy way. :wink2: