Bulk Import Text Files 1

[johnny45]

I need to import Text files into a Pervasive DB
We are Using Pervasive v9.6. I have Created a table with 2 fields :
ItemNo CHAR 50
ExtDesc LONGVARCHAR (As data will be more that 255 CHARS)

What would be the best way to populate this table.

Can I somehow get a stored procedure to read a dir and import all teh txt files in it ?

 

[mirtheil]

Stored Procedures can't access external files.
You'll need to write (or have written) a program to import the data. There's a number of interfaces and methods but you'll need to pick it.
There is another option. You could use BDU (Bulk Data Utility). It's BDU.EXE and it was included with v9 and later. It's a command line tool, you might look at the options to see if it'll work for you.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[johnny45]

Thank you for Idea,
I think I will use VBA from Excel to read the txt file into a string and then insert, HOWEVER is it posssible to UPDATE SINERT in one SQL command.....?
Would someting like this work :

INSERT INTO users (username)
SELECT 'Jo' 
WHERE 'Jo' 
  NOT IN (SELECT username FROM users) 

UPDATE users 
SET email = 'jo@email.com'
WHERE username = 'Jo'

or even this:

IF (EXISTS (SELECT * FROM AA_TestTable AS t1
  WHERE t1.ord_num = 'FFF'))
begin
  UPDATE AA_TestTable
  SET ord_qty = 999
  WHERE ord_num = 'FFF'
end
else
begin
  INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty)
  VALUES('GGG', 'XYZ', 567)
end

 

[mirtheil]

Your code would need to issue an INSERT or UPDATE. You can't pass both to the engine at the same time. You might be abel t do something similar to your second code segment but it would have to be inside a stored procedure.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com