Uploading multiple CSV files using sybase

[Former Member]

Hi

I am trying to load data from multiple CSV files into a table, I used the load table statement but my problem is that it works if the path and filename are hard-coded but I nee to use a variable because the number of files might vary from week to week, so I need to check how many files then upload all without getting an error.  This is what I used which does not work

create or replace variable @filecap varchar(100);

Set @filecap = '*.csv';
commit;

load table "pmoss".umt_tariff_rule_upd (key_tariffrule, scheme_code, scheme_name, tariff_code, tariff_desc, rule, t_rule, rule_desc, medtype1, medtype1_desc,
                                medtype1_desc_new, medtype2, medtype2_desc, medtype2_desc_new, source, date_load, date_closed, is_current '\x0D')
from 'C:/publish/adhoc/Medicine Classification Test/'&@filecap,  
'C:/publish/adhoc/Medicine Classification Test/'&@filecap,
'C:/publish/adhoc/Medicine Classification Test/'&@filecap escapes off;

[Former Member · Accepted solution]

Hi,

I'm not sure such 'dynamic' syntax is supported, but there are workarounds:

1) If the file names are fixed, but only now and then some of them are missing, then you may list all of them and use the ON FILE ERROR CONTINUE option; this way, if a files is missing IQ will continue to the next one without giving an error.

If the file names are also dynamic, there are also ways to do this:

2) create the LOAD statement outside IQ and execute it using dbisql

- or

3) create the LOAD statement dynamically inside and use execute immediate. To get the list of files you may use a REMOTE SERVER of class directory:

(from online documentation)

1. Create a remote server by using the CREATE SERVER statement.

CREATE SERVER my_dir_server CLASS 'DIRECTORY' USING 'ROOT=c:\Program Files;READONLY=yes;

2. Create an external login by using the CREATE EXTERNLOGIN statement

CREATE EXTERNLOGIN DBA TO my_dir_server;

3. Create a proxy table for the directory by using the CREATE EXISTING TABLE statement.

CREATE EXISTING TABLE my_program_files AT 'my_dir_server;;;.';

In this example, my_program_files is the name of the proxy table, and my_dir_server is the name of the directory access server.

4. Display rows in the proxy table.

SELECT * FROM my_program_files ORDER BY file_name;

Use a cursor on a select on this remote table and build the FROM part of the LOAD statement. Concatenate everything and use execute immediate.

Dan

[Former Member · Accepted solution]

Hi Dan

Thank you for your response.  I used the ON FILE ERROR CONTINUE option and it worked fine.

I thought that there was a way of maybe counting the files in the folder as the TotalFiles variable then have a loop loading data into the table acording to the total number of files.

Regards

Phelisa

[markmumy · Accepted solution]

The LOAD TABLE command takes variables for the file name.  The alternate syntax was release in the IQ 15 days, if memory serves.

Check out the syntax.  Notice the "filename-variable" in the FROM/USING clause.

Syntax

LOAD [ INTO ] TABLE [ owner.]table-name ... ( load-specification [, …] ) ...  
{ FROM | USING [ CLIENT ] FILE }  { 'filename-string' | filename-variable } [, …] ... [ CHECK CONSTRAINTS { ON | OFF } ]