Newbie - CSV Import challenge

[Liahona]

Sorry, I have tried to follow all the posts that matched my challenge and still am no further forward. It is so easy in every other DB I have used and I am getting very frustrated.
Perhaps someone could explain it to me simply.
My challenge is I am importing a CSV File with the following format:
Date: YYYY-MM-DD
Description: 72 Characters
Amount: 9.2 Numeric

I use the import wizard and it creates a table with date and description as character fields and Amount correctly as Numeric 9.2.

Now I want to update the table or append to another table and have the date in a date format. How do I do that simply?

[Doug Page]

Re: Newbie - CSV Import challenge

For me the easy way would be to create a new field in the table as a date type. then we need to convert the character date to a real date. As you are manipulating a character field, open the help file and click the second tab "Index" and search for "character". Items under character will reveal "functions". We will need some kind of function to do the conversion. Now double click on "functions" and in the right hand side of the help you will find "Character Conversion Functions". Double click on that and you should find CTOD() and open it to read all about it. CTOD() needs the data in a particular format MM/DD/YY so your original date need to be altered to do this. To make a long story short:

CTOD(mid(yourdate,6,2)+"/"+mid(yourdate,9,2)+"/"+mid(yourdate,3,2))

There is your equation to convert your dates. You can open the interactive window to test all this out. To convert all your dates in the table, you will need to create an operation to UPDATE the new field that you created above.

HTH

[Tim Kiebert]

Re: Newbie - CSV Import challenge

A slightly shorter expression is

stod(remspecial(datestring))

Consider the following from the Interactive window

dim datestring as C
datestring = "2008-11-14"

datestring = remspecial(datestring)
?datestring
= "20081114"

dim newdate as D
newdate = stod(datestring)
?newdate
= {14/11/2008}

?stod(remspecial(datestring))
= {14/11/2008}

If you type in the index 'date' (of all things :)) you get lots of entries on the subject including an entry called functions where you can find the stod().
The remspecial you can find in the section Doug pointed you to.

[Liahona]

Re: Newbie - CSV Import challenge

Thanks guys,

After two evenings of reading and getting nowhere I have managed to accomplish the simple transfer of 111 records.

I bet my clients are glad they aren't paying for my learning curve.

Since transferring and updating records is such a common function in DB development I am astonished it isn't covered more clearly in any of the training I have seen.

Now that I know a couple of ways to do it, it shouldn't hold a challenge to me in the future.

[Zradene]

Re: Newbie - CSV Import challenge

I am trying to do something similar, but with a few added problems...
Firstly, I have no control over the format of the date string contained in the csv file, which makes this conversion more difficult.

The format is:- DayMonthYear, but could be any of the following:-
112008 (1st Jan 2008)
1112008 (1st Nov 2008)
11112008 (11th Nov 2008)

Anybody got a formula to convert this to a valid date ?

Thanks

[Keith Hubert]

Re: Newbie - CSV Import challenge

Hi Fred,

This would have to be done by a logical process.

My method which may not be the best but it can be seen to work is as follows. After importing, modify the structure with 4 new fields.

Day, Month, Year, New_Date

First of all you know that the last 4 digits will always be the year. So this field can be filled using Operation, Record Update with the expression:

right(alltrim(old_date),4)

By knowing the length of the old_date string using the function Len() with Alltrim() as in len(alltrim(old_date)) you can now test what month it is. If the Len is 7 or 8 then you will know that the month must be 10, 11, or 12.

To extract the month from the old_date string you will need to use the Substr() function. The starting position will depend on the length. If the length is 7 or 8 then the starting position will be Len()-5 or Len()-6

To get the Day you will need the Left() function, again this will depend on the length, if the string is 6 then it will be Left(old_date,1), string length of 7 will be Left(old_date,2)

Lastly update the New_Date field with value {Day/Month/Year}

All this can can all be written into a script and variables used instead of having the Day, Month and Year fields.

I suggest you have a play in the interactive window with the above functions and the IF() function. It will be a good experience for you.

Dont pull you hair out, take it step by step, it will work.

[Zradene]

Re: Newbie - CSV Import challenge

Hi Keith, thanks for that.
I still think there is a problem here Keith...

Regarding using the function Len() with Alltrim() as in len(alltrim(old_date)), there still could be 2 differant dates with a Len of 7.
1112008 could be either 1st Nov 2008 or 11th Jan 2008.
Same problem with working out the Length of Day.

Fred

[MikeC]

Re: Newbie - CSV Import challenge

Fred,
Whenever there is a problem in which a Person cannot tell what is going to be the end result, there will be no way to tell a database what to do about it---you are going to have to determine this one way or another manually. A perfect example too of why strict control in an application for data entry is so necessary.

[Keith Hubert]

Re: Newbie - CSV Import challenge

Hi Fred,

I accept

1112008 could be either 1st Nov 2008 or 11th Jan 2008.

could be a problem. So could 211208. Without going through the whole calendar, how many other instances could there be like this?

Filter out the 1112008 and do them manually. If this is an ongoing situation then as Mike says, consider data entry control.

[Zradene]

Re: Newbie - CSV Import challenge

Hi Mike, yep your correct in your assumption. There is no way of correctly calculating the date from a date string formated like that.
I am extracting this csv file directly from a POS terminal itself, so all I can do is create the additional fields as Keith suggested, and then create a formula to compare the results of these fields with today's date. If the dates are within lets say 7 days, I think I should be able to get an accurate result. Not ideal, but it's the best I can do on this.

Thanks for your input.

[Doug Page]

Re: Newbie - CSV Import challenge

If you are truly getting data with the zeros missing from the day and month, then you may be hooped.

1112008 -> Jan. 11 2008
1112008 -> Nov. 1 2008

If your data is coming to you in chronological order, then you could create a parsing routine that could logically attempt to determine the dates from the previous ones and the later ones. Tough logic but not impossible. Otherwise, when there can be duplicate dates like above, there is no routine that can automatically correct your data.

[Zradene]

Re: Newbie - CSV Import challenge

Hi Keith, I looked at the calendar regarding these "dual" formatted dates occurances, and I think this idea will work better than my last date comparison idea.
I will give this ago.

Thanks again

[Zradene]

Re: Newbie - CSV Import challenge

Hi Doug, yeah I was thinking along those lines.

I blame the guys who write the software for these touch screen terminals.
No consideration for us 3rd party develops eh...haha

Cheers mate.

[rleunis]

Re: Newbie - CSV Import challenge

Just a wild shoot maybe:

1) load the csv file into excel using delimited field by character option
2) Check the date format with the original source and correct where needed (assuming these are manageable numbers)
3) load this into Alpha

[Zradene]

Re: Newbie - CSV Import challenge

Hi Ron. Unfortuately I have to import the file silently, so editing it in Excel would not be an option.

Thanks for your suggestion.