| Convert Text Based Date To Date Format For Import Into GM | |
Posted: Tue Oct 15, 2013 10:12 am |
Points: 0 |
|
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? |
|
|
|
|
| | |
Posted: Sat Oct 19, 2013 7:14 pm |
Points: 0 |
|
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. |
|
|
|
| | |
Posted: Thu Oct 24, 2013 7:18 am |
Points: 0 |
|
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. |
|
|
|
|
| | |
Posted: Thu Oct 24, 2013 8:26 am |
Points: 0 |
|
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. |
|
|
|
| | |
Posted: Fri Oct 25, 2013 5:41 am |
Points: 0 |
|
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. |
|
|
|
|
| | |
Posted: Fri Oct 25, 2013 7:24 am |
Points: 0 |
|
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. |
|
|
|
| | |
Posted: Thu Jul 10, 2014 3:36 am |
Points: 0 |
|
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 |
|
|
|
| | |
Posted: Thu Jul 10, 2014 3:39 am |
Points: 0 |
|
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 |
|
|
|
Posted: Fri Jul 18, 2014 11:36 am |
Points: 0 |
|
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. |
|
|
|
|
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
|
|
|
Brought to you by Castell Computers, Doug Castell, Admin
Powered by phpBB © 2001-2004 phpBB Group Theme created by Vjacheslav Trushkin
|