Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 DBTYPE_DBTIMESTAMP error converting legacy .dbf

Author  Topic 

bulgur
Starting Member

7 Posts

Posted - 2008-04-28 : 19:00:20
I am trying to import a legacy dBase III file (.dbf format) into SQL server. The file contains timestamp fields which, as implemented in the dBase data file format, are actually eight-byte character strings. I am using this command:

SELECT * INTO LegacyData FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:\Files\','SELECT * FROM data.dbf')

The command fails with this error:

Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

This is happening because some of the datetime fields contain strings that can't be parsed by SQL as valid dates and times. The legacy application which created the data file apparently indicated a missing timestamp by storing "- - " as the character string.

If I change the select statement to say "select top 2 *" to only import the first two records (neither of which happen to have any invalid datetime values), the records are imported successfully. What I would like to do is to import all records and either skip those records that have a bad datetime value or, better yet, import all records converting invalid dates to null values.

I tried changing the select statement to include various types of casts but it seems that because the .dbf file indicates that the data field is of time timestamp, SQL will always try to read it as a datetime field regardless of how the select statement is written. I don't currently have any way of modifying the dBase III file or I would attempt to search for and remove the offending records.

Does anyone know of a workaround for such a situation? Is there a way I can import the data using SQL server or will I need to find a dabasebase conversion utility that can handle unparseable date strings?

Any suggestions would be appreciated. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 19:16:52
Import your data into a staging table. The staging table would have the same layout as your regular table, except you'll use varchar for this particular column instead of datetime. Then move your data from the staging table into your regular table using INSERT INTO/SELECT. With the T-SQL query, you can filter out the bad data via a WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-04-28 : 19:22:55
>Import your data into a staging table.

Thanks for the suggestion but it doesn't address the issue. How do I import the data, into a staging table or otherwise, without hitting the DBTYPE_DBTIMESTAMP error?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 19:25:08
Yes it does address the issue. You won't get that error with a varchar data type on the problematic column. You use varchar only in the staging table. See my last post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-04-28 : 19:55:25
I reproduced the error using a staging table with all DateTime columns replaced with varchar data types. Here are the steps I took. I truncated the table that I had created using the "TOP 2" constraint as described in my original post. I then modified that empty table and changed all columns of type datetime to varchar(8).

I could no longer use the SELECT INTO statement because it requires that the table not already exists so instead I used this query:

INSERT INTO LegacyData SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:\Files\','SELECT * FROM data.dbf')

This is what you had in mind, right? Unfortunately, the modified query using the staging table returned:

Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

I double-checked to be sure that there were no remaining columns of type DateTime in the staging table.

It was worth a try, though. Any other ideas?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 19:58:20
Post the DDL for LegacyData.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-04-28 : 22:04:50
The table is a .dbf file and I have minimal tools that can work with it. I was able to dump the list of data fields using a program called dbu.exe:

SUPPLIER ¦ Character ¦ 14
PONO ¦ Character ¦ 6
ORDDATE ¦ Date ¦ 8
CTRLNO ¦ Character ¦ 6
COMPLETED ¦ Character ¦ 1
TAKER ¦ Character ¦ 10
PICKUP ¦ Character ¦ 10
DW ¦ Character ¦ 1
BRANCH ¦ Character ¦ 30
ADDRESS1 ¦ Character ¦ 30
ADDRESS2 ¦ Character ¦ 30
SUPPINV ¦ Character ¦ 10
RCVD ¦ Character ¦ 1
RCVDDATE ¦ Date ¦ 8
COMPDATE ¦ Date ¦ 8
SUPPORD ¦ Character ¦ 10
SUPPCONT ¦ Character ¦ 10
FAX ¦ Character ¦ 12
SUPPST ¦ Character ¦ 10
SELECT ¦ Character ¦ 1
TOTAL ¦ Numeric ¦ 12
PAID ¦ Character ¦ 1
XTYPE ¦ Character ¦ 1
EXPDATE ¦ Date ¦ 8
WTYPE ¦ Character ¦ 1
LOCATION ¦ Character ¦ 10
CANCELLED ¦ Character ¦ 1
CANCELDT ¦ Date ¦ 8
CANCELBY ¦ Character ¦ 8
RETURNORD ¦ Character ¦ 1
PHONE ¦ Character ¦ 12
ALLOPO ¦ Character ¦ 1
POTYPE ¦ Numeric ¦ 2
WAITPART ¦ Character ¦ 1
ORIGPONO ¦ Character ¦ 6
BOPONO ¦ Character ¦ 6
DATEPAID ¦ Date ¦ 8
RCVDBY ¦ Character ¦ 6


There are six fields of type Date. According to this page

[url]http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm[/url]

the dBase III Date format is "8 bytes - date stored as a string in the format YYYYMMDD". The import into SQL is failing because of cases where the data is garbled instead of being a valid string of the form YYYYMMDD.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 00:09:47
We need the DDL for the table in SQL Server not in the dbase file. Please generate the script in Management Studio, do not manually write it out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-04-29 : 13:05:26
Here is the table definition as generated by the Script Table As command:

CREATE TABLE [dbo].[LegacyData](
[SUPPLIER] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORDDATE] [datetime] NULL,
[CTRLNO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMPLETED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TAKER] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PICKUP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DW] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPINV] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RCVD] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RCVDDATE] [datetime] NULL,
[COMPDATE] [datetime] NULL,
[SUPPORD] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPCONT] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FAX] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPST] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SELECT] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOTAL] [float] NULL,
[PAID] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EXPDATE] [datetime] NULL,
[WTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCATION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CANCELLED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CANCELDT] [datetime] NULL,
[CANCELBY] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RETURNORD] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ALLOPO] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[POTYPE] [float] NULL,
[WAITPART] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORIGPONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BOPONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATEPAID] [datetime] NULL,
[RCVDBY] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 13:08:01
Import your data into this table:


CREATE TABLE [dbo].[LegacyData_Stage](
[SUPPLIER] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORDDATE] varchar(50) NULL,
[CTRLNO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMPLETED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TAKER] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PICKUP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DW] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPINV] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RCVD] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RCVDDATE] varchar(50) NULL,
[COMPDATE] varchar(50) NULL,
[SUPPORD] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPCONT] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FAX] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPST] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SELECT] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOTAL] [float] NULL,
[PAID] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EXPDATE] varchar(50) NULL,
[WTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCATION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CANCELLED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CANCELDT] varchar(50) NULL,
[CANCELBY] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RETURNORD] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ALLOPO] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[POTYPE] [float] NULL,
[WAITPART] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORIGPONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BOPONO] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATEPAID] varchar(50) NULL,
[RCVDBY] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Let us know if you get an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-04-29 : 15:02:47
This LegacyData_stage table is the same as the one I had used to reproduce the error as described in my third post except that I had replaced the datetime fields with varchar(8) instead of varchar(50). This table also hit the same data type conversion error.

The problem occurs independent of the format of the target table. It seems that when SQL Server looks at the source .dbf file and sees a field listed as type DBTYPE_DBTIMESTAMP it attempts to perform a conversion into a DateTime data type even though the data will ultimately be stored in a varchar data field. If there were some way to get SQL server to read the eight-byte character field as character data and not perform a conversion that would probably prevent the error.
Go to Top of Page

jrwhetse
Starting Member

1 Post

Posted - 2008-10-07 : 10:47:21
I'm having this same issue. Did you ever find a solution? I've attempted the staging table solution as well with not luck. This one is driving my nuts
Go to Top of Page

bulgur
Starting Member

7 Posts

Posted - 2008-10-08 : 16:47:21
I was not able to import the data using a SQL command. I ended up buying a third-party database conversion tool.
Go to Top of Page
   

- Advertisement -