.dbf file import (duplicate Field Names)

I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.

Can anyone make recommendations here that might help?

Oh, this is SQL200 in case that impacts what you might advise!!!!

Anyone out there from MSFT want to make some suggestions?|||

Hi Ellen,

I find it surprising that the DBF file can have duplicate field names. What application created it? Is it a FoxPro DBF or a DbaseIV or Paradox DBF?

I assume you are able to read the table structure with something other than SQL Server, is that correct?

Are you using ODBC or OLE DB? The latest FoxPro and Visual FoxPro OLE DB data provider is downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .

(urgent)help needed with this error message

Hi i am trying to transfer the contents of a dbf file to sql server 2005 and this is my error message

'C:\Documents and Settings\Karen\My Documents\Visual Studio 2005\WebSites\ASC1\pdf\client\AS.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

This is my code

If (FlAS.PostedFile.FileName.ToLower.EndsWith(".dbf"))Then FlAS.PostedFile.SaveAs(location) Try'Connection string to a dbase file Dim dbfConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={0};Extended Properties= dBase IV", location) 'create connectionto the DBFfile Using connectionAs Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(dbfConnectionString) Dim commandAs Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select *from AS.DBF", connection) connection.Open()'Create a dbDatareader to the dbf file Using dr As Data.Common.DbDataReader = command.ExecuteReader() Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION) Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)) Dim query As String = "Truncate table ASDBF" myConnection.Open() Dim cmd As New SqlCommand(query, myConnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myConnection.Close() 'bulk copyof sql server Using BulkCopyAs SqlBulkCopy = New SqlBulkCopy(sqlConnectionString) BulkCopy.DestinationTableName = "ASDBF" BulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using Catch exAs Exception Throw exEnd TryEnd If

Any help will be appreciated.

Regards

Karen

What is the "location" variable? What is it's value? Where is it set?

|||

location is set before the if Statement and its a string

location = Server.MapPath("pdf/client/" + Session("ClientName") + strAS)

|||

Debug your code and verify that the string "location" looks as you would expect

|||

yeah it does... but how would i know if i have to use a foxpro connection string or a dbase connection string and which version?

Regards

Karen

|||

Where is your code failing? On file.saveas(...)?

|||no its failing at

connection.Open() ... cause i can see the file name in the location that i am saving it to.

|||

Using 'Tools' --> 'Connect to Database', try to create a connection to the .DBF file. I think that there is a problem with the connection string. Once you successfully connect from Visual Studio, you should be able to copy that connection string into your code.

|||

I found this article.. Hopefully this will help you. Looks like you may need to leave the filename off of the Data Source (only include the folder name).. Take a look

http://www.msdner.com/forum/thread51916.html

|||

thanks should i use this provider?

Microsoft Jet 4.0 OLE DB Provider or which one?

|||

Just a quick glance through the article, it looked like your connection string was ok, except for the filename on the end of the datasource.

|||

i used the same connection string to a excel file but specified the extended properties as excel 8.0 and it works fine... but not sure why isnt it working for a dbf file

|||

Thanks SSWanner for your answer... I got it working... I downloaded the fox pro oledb provider... and it kinda worked good it failed at the sql insertion (sql exception) and i am familiar with that error. The error i got is

Received an invalid column length from the bcp client for colid 1

I just need to change the column length in sql server..

Again Thanks a lot.

Regards

Karen

(urgent) Help needed for this insert statement

Hi,

I am trying to insert the Source name and clientId to a table called clientSource...

The User will send in some Dbf File... So in a particular file called PlanDbf.. I have the following fields

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

And in another table called SourceDBF i have the following fields with data

PlanNumber PARTID SOURCE_NUM etc...

170234 123456789 1

170234 123456789 3

170234 451231334 1

So how do i match the Source_NUM with SRCnames when i insert it into the table..

INSERT INTO Statement..ClientSource
(
@.ClientId,
SourceName
)
SELECT
s.SOURCE_NUM

 FROM SourceDBF ..

but i am stuck..

any help will be appreciated.

Regards

Karen

I cant see a connection between them. How are they related?

|||

The 2 tables are related by the plan number...

and will know by taking a look at the PlanDBF if its 1 then to pull ... SRC1Name...

|||

Sorry I still cant get it.. can you just post few rows from each tables and expected output so its easier to understand (at least for me)..

|||

this is how the table that i need to insert is ...

SourceId (int identity) ClientID SourceName

26 1 DEFERRAL27 1 EMPLOYER28 1 MATCHING29 1 ROLLUnre30 1 UNCASHED31 1 ROLL 32 5 DEFERRAL33 5 ROLL 34 5 EMPLOYER35 5 MATCHING

And i will be getting the Data the source name from the Table...

PlanDBF (which has one row of data)

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

and then once i insert the data to the above table called source.. I am inserting the data from the Source Summary which is as follows

PlanNumber PartId Source_NUM begbal dep withdrawels etc..

170925 1296027 1 6211.63 325.00 0.00 211.03 0.00 6747.66 100.06747.66 True170925 1296027 3 2887.36 0.00 0.00 108.38 0.00 2995.74 80.002396.59 True170925 1486037 1 174.64 0.00 0.00 11.13 0.00 185.77 100.0185.77 True170925 1733690 1 2396.84 1625.00 0.00 181.37 0.00 4203.21 100.04203.21 True170925 1733690 3 770.72 0.00 0.00 49.89 0.00 820.61 0.000.00 True170925 2294760 1 4870.82 650.00 0.00 282.52 0.00 5803.34 100.05803.34 True170925 2294760 3 2972.46 0.00 0.00 148.26 0.00 3120.72 20.00624.15 True170925 2443743 1 1421.74 650.00 0.00 42.43 0.00 2114.17 100.02114.17 True170925 2443743 3 611.00 0.00 0.00 18.16 0.00 629.16 20.00125.83 True170925 2662592 1 2857.15 1300.00 0.00 131.71 0.00 4288.86 100.04288.86 True

to another table ParticipantSourceSummary which has the has the follow fields

Plan Number PartId SourceId (which will be got from the clientSource table) and the remaining fields from the SourceSummary table

So at the end while i generate a report i want Source_NUm 1 to be whats in SRCName and so on

Hope this is clear.

Regards

Karen

|||

CREATE VIEW vw_Sources AS

SELECT PlanNumber,Src1Name AS SourceName,1 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src2Name AS SourceName,2 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src3Name AS SourceName,3 AS Source_Num

FROM PlanDBF

{repeat 17 more times}

--

Not sure of all your relations, but then you can do something like this:

SELECT s.PlanNumber,s.PartID,s.Source_Num,vw_Sources.SourceName

FROM SourceDBF s

JOIN vw_Sources ON s.PlanNumber=vw_Sources.PlanNumber AND s.SOURCE_NUM=vw_Sources.SOURCE_NUM

|||

Alternatively, you can do this:

SELECT s.PlanNUmber,s.PartID,s.Source_Num,

(SELECT CASE s.Source_Num WHEN 1 THEN Src1Name WHEN 2 THEN Src2Name WHEN 3 THEN Src3Name ... END FROM PlanDBF p WHERE p.PlanNumber=s.PlanNumber) AS SourceName

FROM SourceDBF

|||

Motley,

Thanks a lot.. the solution works for me

Regards,

Karen

(Urgent) Help need with in selecting too many columns.

Hi,

I have a dbf file and that file has around 154 columns and in that i want to pull just 88 columns to my sql server database... I am using a OLedb connection and a data reader to read the data from the DBF file and then using a sqlbulcopy to insert the data into SQL server 2005 database. I have created a destination table for the 88 columns. This is my select statement for the dbf file. I have also used a Rownumber which is Int identity so i am using a 0 in the first column.

Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select 0,* from FUND.DBF", connection)

Now my question is Since i want to pull 88 columns instead of 154 column, I was thinking to give a select statement like

("Select, 0,Column1,....Column88 From Fund.DBF", connection)

SO instead of doing this is there is a way that i can specify in the sql statement that will tell it not to pull the rows after the 88th column.

Any help will be appreciated.

Regards,

Karen

I know of a way to do it but it isn't elegant

SQL stores metadata about its data in system tables. You could check the column meta-data table for all columns of a certain table, then write a cursor to build a dynamic sql select for the first 88 rows and execute the statement

If you are using SQL Server 2005, here is the select statement to pull all columns for a specific table

select *from sys.columnsjoin sys.tableson sys.Columns.object_id = sys.tables.object_idand sys.tables.[name] ='TableName'

|||

But i need to ignore the data after Column 88 in a dbf file... is it possible to do it? if not i can write a select statement to just select the fields i want.

|||

Yes, if you did what I said above you wouldn't have to write out all 88 columns. You would write a cursor that would loop through all the columns in the sys.Columns table while the count is less than or equal to 88

|||

Unfortunately, the only ways I can think of doing so requires naming all the columns that you do want. Of course that depends on the system you are pulling the data from, but with standard sql, there is no such way at least not one that is universal across all SQL implementations.

You can however, make an initial request to the datasource, pulling back 0 rows (SELECT * FROM ... WHERE 1=0) into a dataset. Then you iterate through the first 88 columns of the empty dataset to get the column names, and build up a select command. Of course this is slower, and has more overhead. It is probably easier, faster, and more efficient just to name all 88 columns.