Question Re: Uploading multiple CSV files using sybase in Technology Q&A

Uploading multiple CSV files using sybase

Former Member — Thu, 22 Jan 2015 10:43:08 GMT

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;

Re: Uploading multiple CSV files using sybase

Former Member — Thu, 22 Jan 2015 11:25:43 GMT

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

Re: Uploading multiple CSV files using sybase

Former Member — Thu, 22 Jan 2015 12:22:25 GMT

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

Re: Uploading multiple CSV files using sybase

markmumy — Thu, 22 Jan 2015 14:41:07 GMT

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 } ]