trying to import data from foxpro database
-
i am in the process of trying to import data from foxpro 5 dbf files into sql server 2000 tables. i just right click in enterprise manager and go to All Tasks, Import Data to run the DTS Import wizard.
i set up a system DSN in windows to the fox pro files and i hit it using that.
most of the tables came through with no problem but i am having trouble with one of them. this particular dbf has about 2.5 million records and it keeps getting partially through it and then gives an error saying that it could not convert a date field in one row.
so i go and look at the date field in question (from the dbf) and it is usually something like 2/2/1199 instead of 2/2/1999. so i make the change and run the DTS import again and it gets further and then gives the same error on another record.
i even went as far as to sort the dbf and delete all the records with bad dates (using DBFViewer 2000) and the DTS import will still choke on those records. it seems like when you delete records in a DBF they dont really go away.
it just seems weird to me that if they are in the DBF, the field type must be set to accommodate those funky dates, why would the DTS wizard create the table every time with a datatype for the field that wont accommodate all its data?
i just want to get the data from point A to point B.
Any suggestions would be very helpful.
-
Maybe you could convert the date to varchar for the import then toy with sql server to sort out the wrong dates...
-
You could also save the package the wizard creates. Then edit the saved package. On the Options tab of the datapump properties, setup the logging of source and destination error rows, and set a figure for the max error rows.
--------------------
Colt 45 - the original point and click interface
-
so doing this would log all the errors but what woudl it do when it encounters a row with errors? skip it?
-
Is there somone there FoxLiterate who can help? You are right about deleting those foxpro rows. Once you delete, you have to issue a "pack" command. That really honest to goodness deletes them.
I have a production process that pulls from foxpro dates. I create a staging table that is all char. I copy the foxpro table there first. Then, I check things like dates to make sure I am going to be able to shove them into SQLServer datetime and smalldatetime columns.
Once you get the foxpro table into a sqlserver table, you can insert sql tasks to check things like:
where( (isdate(xxxxxx) <> 1 OR
xxxxxx > '20790606' OR
xxxxxx < '19000101' )
That last check for range is to make sure your date will go into a sqlserver smalldatetime.
Also, make sure you do this:
On the transformation task going from your ODBC connection to the SQLServer connection do the following:
Right click on the transformation line
Click on Workflow Properties
Click on the options tab
Check the box "Close connection on completion"
I had a lot of intermittent trouble with files remaining open when I first started dealing with these foxpro files till I found this little box.
Bye,
Teague
-
unfortunately no - we don't have a fox pro person available. the person who created our fox pro apps left us long ago and now it is up to me to convert them into Coldfusion/SQL Server apps.
I was able to get the foxpro data into a SQL table by changing 3 datefields to varchar and several numeric fields to varchar.
forgive my "newbieness" when it comes to DTS packages but i have some questions about your post.
now that I have the staging table, i created a destination table with the proper field types and i started putting together a DTS package. I added 2 Microsoft OLE DB Provider for SQL Server" Connections both pointing to my database and then added a Transform Data Task specifying the proper source and destination table.
now, in your post, when you say:
"Once you get the foxpro table into a sqlserver table, you can insert sql tasks to check things like:" I assume you mean to add a Execute SQL Task right?
and for the SQL statement do i use an INSERT INTO or a SELECT * FROM with your WHERE Clause?
and also is there something i can specify in a WHERE clause to pick out the "deleted" records?
again - i know this may be basic stuff but i am learning as i go with this DTS stuff - its all new to me. any help is definitely appreciated.
-
Yes for the Execute SQL Task right part.
The syntaxe is :
Insert into dbo.tableName (col1, col2, coln) Select col1, col2, coln from dbo.StagingTable --where condition that flushes the deleted records... can't help you on that one without more info.
-
what other information do you need to help me skip the "deleted" records?
-
How about a sample of a current record, with any of its deleted versions?
-
Hi,
I took that little code snippit out of my dts package.
What to do with those records with bad data? That's the question.
I assume you don't want to just delete them. If you set up a records_in/records out audit at some point, that will not look very good.
I created yet ANOTHER table xxxxx_errors. If I detect errors in that holding table, I insert that record into the errors table and delete from the data stream. I also check things like data headed to numerically typed fields in my final destination table. I insert those errors into my errors table and delete them too. As you get more familiar with your data, you will start to learn which columns are forever getting dirty data in them.
So basically something like
insert into xxxx_errors
where isdate(DateInQuestion) <>1;
Then,
delete from holding_table
where isdate(DateInQuestion)<>1.
Then, if this is to be a oft repeated production type dts, you will probably need to get with the powers that be to see who is going to reconcile those bad records in the xxxx_errors table.
teague
-
tague - i got my DTS package setup with the MS OLE DB connection and the Execute SQL Task. but when i run it i get the followign message:
The Statement has been terminated.
THe conversion of a char data type to a datetime data type resulted in an out-of-range datetime.
my query looks like this:
insert into dbo.indetl(rec_type,pat_no,dos,cost_ctr,service,qty,descript,amt,fsc,dx,md_admit,md_refer,classif_1,aud_date,error,fsc_ipams,update_by,update_dt,servicecd)
select rec_type,pat_no,dos,cost_ctr,service,qty,descript,amt,fsc,dx,md_admit,md_refer,classif_1,aud_date,error,fsc_ipams,update_by,update_dt,servicecd from dbo.indetl_VChar
where (isdate(dos) <> 1 OR
dos > '20790606' OR
dos < '19000101' )
indetl_VChar is the table with the dates stored as varchar
indetl is the empty destination table
what am i doing wrong here?
-
This will fail : isdate(dos) 1
You're asking the server to convert from char to datetime where the conversion is not possible... You'll have to use a char column in the destination table for this to work.
-
ok so what if i just delete the data i have imported to the staging table and create a new DTS package from the import wizard which uses a SQL statement that says grab all the records
where isdate(dos)<>1 OR
dos > '20790606' OR
dos < '19000101'
that way there is no staging table it woudl just import the records right from the foxpro DB to SQL (but only the records with a valid date)
problem is - what woudl my sql statement look like in the DTS import wizard?
i tried the following and it didnt work:
insert into dbo.indetl(rec_type,pat_no,dos,cost_ctr,service,qty,descript,amt,fsc,dx,md_admit,md_refer,classif_1,aud_date,error,fsc_ipams,update_by,update_dt,servicecd)
select rec_type,pat_no,dos,cost_ctr,service,qty,descript,amt,fsc,dx,md_admit,md_refer,classif_1,aud_date,error,fsc_ipams,update_by,update_dt,servicecd from dbo.indetl_VChar
where (isdate(dos) <> 1 OR
dos > '20790606' OR
dos < '19000101' )
where dbo.indetl is the sql table and indetl is the foxpro db.
any ideas?
-
Remi is exactly right.
Make that dos column a char column.
You will probably have to fiddle with the dates to get them to work just right. In my examples, my dates were coming in formatted like YYYYMMDDHHMMSS. If your dates come in one of the ZILLION other formats, you will definitely have to make some changes. Did I mention I hate dates?
One of these days when I become great and powerful, I will declare to all that THESE are the date formats you may use. THESE AND NONE OTHER! Just picture me coming down from a mountain carrying tablets and you will get the idea.
Teague
-
Amen
.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply