trying to import data from foxpro database

Question

Post reply

trying to import data from foxpro database

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Records that can not be imported are re-directed to the log file.

In SSIS (DTS in 2005) you can actually direct these to a database table.

--------------------
Colt 45 - the original point and click interface

Blair Dee SSCommitted Points: 1648 More actions · Answer 2

ok i guess there is something i am not understanding here. my eventual goal is to have a table consistign of all the good records with their dates in a field of type datetime.

now if i am importing the data from foxpro into a SQL table converting dates to varchar then runnign a DTS to copy the valid records to a new table maintainign the varchar type for the dates, how am i supposed to get the varchar dates back to datetime?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

when you copy the valid records, this is where you convert back... and is also where you drop the invalid dates.

Teague Byrd Right there with Babe Points: 752 More actions · Answer 4

Ok, that officially mad me mad.

For the second time in two days, I had typed a pretty long reply and when I hit post, it went into another world. That emoticon with the unhappy face is just not mad enough for my needs.

I did want to mention one or two things.

One gotcha when moving the foxpro files into a char column is that blank foxpro dates get converted to the date 1899-12-30. In my case, I was converting the foxpro date so that it ended up looking like YYYYMMDD.

Here is the conversion for a date of birth column. Note I stuck in leading zeros for months 1-9:

if DTSSource("cl_dob") <> CDate("12/30/1899") then

if len(month(DTSSource("cl_dob"))) <>2 AND len(day(DTSSource("cl_dob"))) <>2 then

DTSDestination("cl_dob") = CStr(year(DTSSource("cl_dob"))) & "0" & CStr(month(DTSSource("cl_dob"))) & "0" & CStr(day(DTSSource("cl_dob")))

elseif len(month(DTSSource("cl_dob"))) <>2 AND len(day(DTSSource("cl_dob"))) = 2 then

DTSDestination("cl_dob") = CStr(year(DTSSource("cl_dob"))) & "0" & CStr(month(DTSSource("cl_dob"))) & CStr(day(DTSSource("cl_dob")))

elseif len(month(DTSSource("cl_dob"))) = 2 AND len(day(DTSSource("cl_dob"))) <> 2 then

DTSDestination("cl_dob") = CStr(year(DTSSource("cl_dob"))) & CStr(month(DTSSource("cl_dob"))) & "0" & CStr(day(DTSSource("cl_dob")))

else

DTSDestination("cl_dob") = year(DTSSource("cl_dob")) & month(DTSSource("cl_dob")) & day(DTSSource("cl_dob"))

end if

else

DTSDestination("cl_dob") = " "

end if

Altermative to above. This one is for Datetime fields and includes minutes and seconds…

DTSDestination("date_value") = _

string(4 - Len(CStr(Year(DTSSource("date_value")))), "0") & CStr(Year(DTSSource("date_value"))) & _

string(2 - Len(CStr(Month(DTSSource("date_value")))), "0") & CStr(Month(DTSSource("date_value"))) & _

string(2 - Len(CStr(Day(DTSSource("date_value")))), "0") & CStr(Day(DTSSource("date_value"))) & _

string(2 - Len(CStr(Hour(DTSSource("date_value")))), "0") & CStr(Hour(DTSSource("date_value"))) & _

string(2 - Len(CStr(Minute(DTSSource("date_value")))), "0") & CStr(Minute(DTSSource("date_value"))) & _

string(2 - Len(CStr(Second(DTSSource("date_value")))), "0") & CStr(Second(DTSSource("date_value")))

Once in character form you can use that isdate() function.

Also, I used char instead of varchar for my staging table.

Hope this makes it through.

teague

Blair Dee SSCommitted Points: 1648 More actions · Answer 5

ok - i think i got all my data imported - thank you everyone for all your help - there is a lot of great info in this thread.

what i ended up doing is i created a DTS package to create the table then i went into the table (with no data) and changed the date fields to char. then i ran a DTS to import the data from foxpro.

then i went to the table and right click -> Open Table -> Return Top. then i opened the SQL view and changed the query to be SELECT TOP 100 * FROM myTable ORDER BY myDateField.

this displayed showed me the problem records (which were generally dates with a year of 0219 or something crazy like that)

then after speaking with my boss i found that the valid records should start with 1996 so i just deleted all records where myDateField < 1996-01-01

then i went back to the table list and into design view for the table and switched the date field (which is now in char) back to datetime and since the garbage dates were gone it worked.

oh and also in the SQL view i also did a query to replace all records where date ='1899-12-30' with an empty string. - thanks teague for that tip.

and while some of the other posted scripts i did not end up using this time - i am sure i will be able to use them in the future. thanks again to everyone who helped me out with this!