Advice needed on csv import into a database.

[AndyLee100]

Hi all,

I am importing records into a database from a csv file and everything is working fine apart from one thing.

At the moment the code works by adding a record from the csv file into the database if it doesn't exist. The table it is importing into has a primary key on the Product Code and will fail if the record exists(Primary Key violation error). However this is ok as I am using On Error Resume Next to skip to the next record in the csv file.

What we need to do though is find if the record exists in the database and if it does check if the product description has changed or not. If it has changed then we need to edit the record and update it.

What I would like someone to advise me on is the most efficient way of finding if the record exists.

I can maybe think of a couple of ways. Like using the find method of the recordset object or having a separate function outside of the import loop that returns false if a select statement on the database with the product code returns no records.

I am sorry if I have rambled on a bit but I would really appreciate someone who has done this kind of thing before to advise me on the most efficient way the dealt with it.

Thanks in advance.

Andy

 

[AndyLee100]

Sorry, I should say that I am using ADO with a SQL Server 2000 database.

 

[mattKnight]

You could use

Select Count(ProductID) as IDCount from table where ProductID = ID

then test IDCount for = 0 or 1. Obviously count = 0 is equivalent to no record.

As you are using SQL, I'd investigate using a stored procedure, to which you pass a product ID and description. You can then encapsulate all your SQL and logic in 1 ADO call, rather than using VB to to handle updates.

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[AndyLee100]

Wont this be very slow though, as for each record it will need to open a recordset, run the query and then close it.

 

[mattKnight]

Wont this be very slow though

That's why I suggested you consider a Stored procedure.



Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[fredericofonseca]

you are doing it the harder way.

First use a DTS package for the whole process.
With this you can perform several steps, each one dependent on the previous one (or not as you wish), and you will be able to get very fast processing times.

First step will be to import the CSV file into a temporary table. This table will then be used to either insert or update the main table with the correct values/records.

One way would be with the following sequence of SQL´s



Assume that the CSV file has already been loaded into table tmp1
also assume that a second temporary table is created just for speed purposes. you can do without it, but it will probably be slower. test at will to get the best option.

tmp1, tmp2 are have the same format as the CSV file.
DB1 is the final destination table.

sql 1 - select all new records into table tmp2.
insert into tmp2
select * from tmp1 where not exists
(select 1 from DB1 where
db1.prodID = tmp1.prodID)

sql 2 - delete all records added to tmp2 from tmp1
delete tmp1 where exists
(select 1 from tmp2
where tmp1.prodID = tmp2.prodID)



At this stage you have two tables, tmp1 with records that exist on DB1, and tmp2 with new records.
Simply add the ones from tmp2 into DB1.

sql 3 - add new records from tmp2 into DB1
insert into DB1 select * from tmp2


sql 4 - update existing records from tmp1 if description has changed

update DB1
set DB1.description = tmp1.description
from DB1
inner join tmp1
on db1.prodID = tmp1.prodID
where db1.description <> tmp1.description

you will need to adapt the above SQL´s to your needs, and you will need to test it very well, but it should give you enough hints to do the work.


Using recordsets to do this work is only feasible if you have very few records.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[AndyLee100]

Hi Fred,

It needs to be done using ADO and recordsets as this is not a one time operation. It will be used again and again from within an application. I know that DTS can be programmed but at this stage I am not about to start to learn a new object model for this.

However your post has given me some ideas so I thank you for that.

Thanks

Andy

 

[johnwm]

Programming DTS may not be as hard as you fear. SQL server Enterprise manager, Tools menu, DTS and follow the wizard. At the end you can save the whole DTS as VB for programmatic re-use.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[petermeachem]

have you tried importing into a temp table and then do an update and insert into the real table. Should be fairly quick?

 

[fredericofonseca]

all the above can be done with plain SQL.

If not using DTS, which can be done also many times as long as there is no intervention from a user required, e.g. need to select file to import, then you should use stored procedures.

All the steps I mentioned can be does within the same SP, and you can then pass all required parameters to it.


but main thing is... DO NOT USE THE RECORDSET OPTION, as it will be a lot slower if you have more than a few hundred records per file.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com