Log in Register FAQ Memberlist Search ContactReview Forum Index
A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldBox » Convert Text Based Date To Date Format For Import Into GM
Post new topic  Reply to topic View previous topic :: View next topic 
Convert Text Based Date To Date Format For Import Into GM
PostPosted: Tue Oct 15, 2013 10:12 am Points: 0 Reply with quote
tt
GoldMine Apprentice
Joined: 18 Jan 2007
Posts: 23




I have a file that I need to import into GM. The file to be imported has a field in it that is formatted as a text field but it only contains date values in it which are formatted as 1/1/2010.

I need to convert the character based values into a date format so that they can be imported into a date formatted field in GM. I tried to use the following expressions in the Field Mapping for the import setup:

ctod(alltrim(CharacterField))
ctod(CharacterField)

Unfortunately neither of them seem to work. When I run the Import Setup (in Parked Mode) the field values in the gxi.dbf have no data at all. They show up as " / / "

Any idea how I can convert the character based field into a date based field for import into GM?
View user's profile Send private message

PostPosted: Sat Oct 19, 2013 7:14 pm Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




Assuming latest versions of GM and GX, you simply attempt to map the Character field to your Date Type field. When you do, GX will put up a window list of the possible formats used in your Character field to represent the date. Just select the correct one and GoldBox does the rest.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Thu Oct 24, 2013 7:18 am Points: 0 Reply with quote
tt
GoldMine Apprentice
Joined: 18 Jan 2007
Posts: 23




Bob, thanks for your response. I tried your suggestion of using the date mask. My character field is in the mm/dd/yyyy format. However, if the month or date only has 1 number then the are formatted as m/d/yyyy. For example, 1/1/2013 and not 01/01/2013.

I applied the mm/dd/yyyy mask but when I test it, by parking the data, the field in the gxi.dbf file does not show that it was converted. The field in the gxi.dbf file just contains " / / " This is true whether or not the character field is in m/d/yyyy or mm/dd/yyyy format.

Am I wrong in my assumption that when parking the data the conversion would be done and you would see the correct values in the gxi.dbf field? If not, do you have any idea why the conversion is not happening.

Thanks again for your help.
View user's profile Send private message

PostPosted: Thu Oct 24, 2013 8:26 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




There were some things that were unclear in your original post, and I tried to navigate around them. Now we'll have to deal with them.

Biggest thing is that you seem to be inserting an unnecessary step, when you say you want to convert your existing (Character) data into Date format. If it's like 1/1/2013, then it IS in (the most common kind of) date format. Using GX and the steps I described to you, it's ready go into GM.

I guess what you're saying is that you want 01/01/2013; but that's not necessary for your import Source. If you have some special reason for wanting that, please explain. I just did this exact thing, stopping to see how it went into the Date Type field in GXI. It was 01/01/2013.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Fri Oct 25, 2013 5:41 am Points: 0 Reply with quote
tt
GoldMine Apprentice
Joined: 18 Jan 2007
Posts: 23




Sorry for the confusion. I'll try to clear things up.

Originally when I first posted, I was not aware that the date mask was there to specify what format the source data was in. I though it was there to allow you to specify how the target data was to appear when the target data was imported into GM. That's why I was using the CtoD function as an expression for the source character field to convert it to a date. I thought I would have to first convert the source character field to a date field, which would then be processed by the date mask, which would then display the target date field in the format of the date mask.

Once I read your first reply I saw that I was obviously wrong. I went back to the import setup and deleted the Expression Result where I was using the CtoD function to convert the source character data. I then applied the mm/dd/yyyy date mask to the source character field. I reran the import with Park/Stop enabled and the result in the gxi.dbf file is that the target date field shows up as " / / " for all records in the source. None of the source character field values appear in the gxi.dbf file.

What I was trying to get across in my last post is that I applied the mm/dd/yyyy date mask in the field mapping for the source character field. However, the month and day data in my source character field is not formatted to where they are left padded to 2 characters. So, for months 1 through 9, and day 1 through 9, there is no leading zero in the source character field, therefor they appear as m/d/yyyy or mm/d/yyyy or m/dd/yyyy. I was trying to explain this in my last post because I was concerned that the mm/dd/yyyy mask would not be able to convert the source character values that were entered as m/d/yyyy or mm/d/yyyy or m/dd/yyyy because they were not in the mm/dd/yyyy format of the date mask.

Regardless, none of the dates in the source character field, whether entered as mm/dd/yyyy, m/dd/yyyy, mm/d/yyyy or m/d/yyyy, are being converted. None of them appear in the gxi.dbf file. The only thing that shows up in the gxi.dbf file is " / / ".

You made reference in you first post to the version of GM and GX. We are using GM 2013.1.0.249 and Goldbox 9 Professional either the 3/16/2012 or 4/24/2012 version.
View user's profile Send private message

PostPosted: Fri Oct 25, 2013 7:24 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




Well, not much help, I'm afraid. You have a newer version of GM than I; but that shouldn't affect what GXI gets. And we have about the same version of GX.

Padding Source with non-significant zeroes should absolutely not be necessary, and has proved not to be when i test it. At the moment, I can't think of anything better than to suggest checking with Ticomix. Sorry, if anything else occurs to me, I'll post it.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Thu Jul 10, 2014 3:36 am Points: 0 Reply with quote
GoldTechs
n00b
Joined: 26 Apr 2012
Posts: 4




Give this a try:

Step 1- Use GoldBox Expression Option called Long Expression->Add a Reference
Enter a reference like "Birthdate"

Step 2- Enter the following long expressions:
CTOD(IIF(LEN(ALLTRIM(For_gm->Birth_date))="7", "0"+LEFT(For_gm->Birth_date, 1)+"/"+SUBSTR(For_gm->Birth_date, 2, 2)+"/"+SUBSTR(For_gm->Birth_date, 4, 4), LEFT(For_gm->Birth_date, 2)+"/"+SUBSTR(For_gm->Birth_date, 3, 2)+"/"+SUBSTR(For_gm->Birth_date, 5, 4)))

_________________
Marty Carver
Lead Technician
mcarver@goldtechs.com

GoldTechs, Inc.
PO Box 3912
Bartlesville, OK 74006
Toll Free: 888-240-6020, Ext. 320
Direct: 918-856-9059
View user's profile Send private message Send e-mail

PostPosted: Thu Jul 10, 2014 3:39 am Points: 0 Reply with quote
GoldTechs
n00b
Joined: 26 Apr 2012
Posts: 4




You may need to adjust your len since you data also contains /

_________________
Marty Carver
Lead Technician
mcarver@goldtechs.com

GoldTechs, Inc.
PO Box 3912
Bartlesville, OK 74006
Toll Free: 888-240-6020, Ext. 320
Direct: 918-856-9059
View user's profile Send private message Send e-mail

PostPosted: Fri Jul 18, 2014 11:36 am Points: 0 Reply with quote
Paul Laufer
GoldBox Guru
Joined: 27 Oct 2008
Posts: 125




tt wrote:
I reran the import with Park/Stop enabled and the result in the gxi.dbf file is that the target date field shows up as " / / " for all records in the source. None of the source character field values appear in the gxi.dbf file.


If NONE of the source "CHARACTER" field values appear then presumably whatever is causing that is causing the dates to not show.

IN the setup screen, right click and choose Save, LIFE RING Document. Paste the content of that document into a message here. That should tell us what we need to know.

_________________
Paul Laufer
Telesage Solutions
www.tsage.com
View user's profile Send private message Visit poster's website

Convert Text Based Date To Date Format For Import Into GM
  ContactReview Forum Index » GoldBox
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
All times are GMT - 7 Hours  
Page 1 of 1  

  
  
 Post new topic  Reply to topic  


Brought to you by Castell Computers, Doug Castell, Admin
RSS Feed
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin