Help converting DBF file to Access Database table... 1

[Vachaun22]

I am writing an application that will need to convert the data from a DBF file into an Access database table.

The file has over 200,000 records in it.

The application looks at a table in the Access database, and the DBF file and fills 2 listboxes to allow the user to map the fields to each other. When the user clicks the continue button, the application then creates the SQL statements necessary to insert the values into the table.

Also, in the listboxes, you have the option to delete fields from the DBF file and insert Null values if you can't match a field.

During the SQL generation, I am checking for this Null value in the Listbox, which sends the app into a conditional that then finds out what datatype the Access table field is, and creates a default values, otherwise, just uses the value from the DBF file.

However, my code is apparently **EXTREMELY** inefficient because I'm not even executing the SQL statement yet, and it would take probably 10 or 15 minutes to to work through the recordset. (only a 700 MHz CPU)

I'm thinking this should be faster, however, I can't seem to come up with a better way to do this.

Also, this routine needs to be generic as it will be handling multiple DBF files (not at the same time).

Any suggestions on how I might improve the performance?

 

[gmmastros]

Are you opening the DBF file and inserting the data in to Access record by record? If so... then you can certainly improve this process.

Suppose you had a DBF file named C:\Folder\Data\MyDatabase.dbf

You can import this data very quickly in to Access by issueing this query.

Select *
Into   TempTable
From   MyDatabase in "" [dBASE IV; Database=C:\Folder\Data]

This will create a new table called TempTable with all the data from the MyDatabase.dbf file.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vachaun22]

I can't do this because of the table structure.

The DBF file was very poorly set up when it was created before my time. There is no PK in any of the tables.

In order for me to use these tables in the application I'm writing, I need the primary key fields there. SELECT INTO will not let me create a PK autonumber field when the data is copied over (as far as I know).

So, I'm thinking I don't have any choice but to do a record by record copy.

 

[MakeItSo]

You still have an SQLServer running?
You can import at least table per table from Access and should be able to have Access create a PK during import.

Other than that, you could also set up the tables in Access first, WITH PK, and then "import into existing table"
(Or export from SQLServer)


I doubt you really have to do it all manually.

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[gmmastros]

I guess I should have given you more of my idea. Sorry.

Here's what I am thinking...

1. Import the data in to a temporary table.
2. Analyze the fields and eventually move the data in to a real table.
3. Remove the temporary table.

By doing things this way, you should be able to speed up the data loading process. Since the data will already be within the Access database, when you are ready to move the data in to the real table, it will be something like...

[tt][blue]
Insert Into RealTable(Field1, Field2, etc...)
Select Field1, Field2, etc...
From TemporaryTable
[/blue][/tt]
Without a where clause, this will insert all of the records from the temporary table in to the real table all within 1 query, so I guarantee the performance will be many times faster.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vachaun22]

Well, there in lies the problem. It's not a SQL Server file. The old program was written in FoxPro back in the early 90's I think.

The access database tables are set up with the File->New function in the application. I just need to move the data from the DBF files into the Access tables.

 

[Vachaun22]

gmmastros, I can't get the SELECT INTO statement to work for some reason.

I'm working with STATES.DBF so my statement looked like this:

SELECT * INTO TEMP FROM States IN "" [dBASE IV; [path-to-file]

Am I way off on this?

 

[gmmastros]

The path-to-file part should ONLY include the folder where the dbf file is located.

In my previous example...

Select *
Into   TempTable
From   MyDatabase in "" [dBASE IV; Database=[!]C:\Folder\Data[/!]]

The part in red is a folder, not the file.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Suppose you had a DBF file named [!]C:\Folder\Data[/!]\[blue]MyDatabase[/blue].dbf

Select *
Into   TempTable
From   [blue][b]MyDatabase[/b][/blue] in "" [dBASE IV; Database=[!]C:\Folder\Data[/!]]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vachaun22]

gmmastros,

I didn't include the file name in the path (I was going to add in there that the file name was not in the path, but I didn't). It didn't work for me. It doesn't through any errors, but it doesn't create any tables either.

 

[gmmastros]

I tested the syntax before I posted, so I'm surprised it doesn't work.

I used Access 2003, but I think this would work with Access 2000 (and maybe even earler version) also.

I also tested where there was a space in the folder name. You may have propblems if there is a space in the file name.

Try this...

Open Access. Open a new query window. Click the SQL button, and then this...

[tt][blue]
Select *
From MyDatabase in "" [dBASE IV; Database=C:\Folder\Data]
[/blue][/tt]

Instead of inserting the data in to a table, the data should be shown in the grid. If you can get it to work in Access, then you should also be able to get it to work from VB.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vachaun22]

Thanks gmmastro,

I realized now after looking at it for the 15th time, I was missing the Database= part. It worked now.

I have one more question.

If I do a select into an existing table, with that overwrite the table structure, or just import the data in?

I'm gonna give it a shot to see what happens. Thanks!

 

[gmmastros]

If you select into a table that already exists, you will get an error because select into is used to create a new table. If you want to add data to an existing table, you will want to use the insert into format

[tt][blue]
Insert
Into TableName(Field1, Field2, etc...)
Select Field1, Field2, etc...
From MyDatabase in "" [dBASE IV; Database=C:\Folder\Data]
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom