You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Patrick Meyer <me...@itemanalysis.com> on 2008/05/29 17:33:37 UTC

Importing delimited files with header

I have a comma delimited file that contains a header in the first row of 
the file. The header contains column names. If I manually create a new 
file without the header, I can successfully import the new file with

CALL 
SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'STAFF','myfile.del',null,null,null,0);

Creating the new file in my Java application is too time consuming for 
it to be a good solution. Is there a way of importing a delimited file 
with a header in the first row? Or, a way to import and ignore the first 
row of the file being imported?

Thanks


Re: Importing delimited files with header

Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Patrick,

Under the hood, the SYSCS_IMPORT_TABLE procedure prepares and executes 
an INSERT INTO ... SELECT * FROM statement using its own custom table 
function. So you should get the same good performance from your own 
table function as you get from the IMPORT procedure.

Hope this helps,
-Rick

Patrick Meyer wrote:
> Thanks for your response. Do you know if extending FlatFileVTI will 
> work as fast as the built-in IMPORT_TABLE function? Before using CALL 
> SYSCS_UTIL.SYSCS_IMPORT_TABLE, I had written my own class that would 
> read a delimited file and use the INSERT INTO statement to populate my 
> Derby db. However, it took my class 1 hour to import a file but it 
> only took one minute when I used SYSCS_UTIL.SYSCS_IMPORT_TABLE. Will 
> extending FlatFileVTI work a quickly as using 
> SYSCS_UTIL.SYSCS_IMPORT_TABLE?
>
> Thanks
>
> Rick Hillegas wrote:
>> Hi Patrick,
>>
>> You can write a table function to help you out here. Your table 
>> function will open your file, throw away the header, and then loop 
>> through the lines in the file. I would recommend extending something 
>> like FlatFileVTI (in Derby's demo directory). You can look at 
>> PropertyFileVTI (in the same directory subtree), which will show you 
>> how to extend FlatFileVTI. Once you declare your table function 
>> (let's call it MyFileReader), you can then use it to populate your 
>> table as follows:
>>
>> insert into STAFF
>> select * from table( MyFileReader( 'myfile.del' ) ) s
>>
>> Hope this helps,
>> -Rick
>>
>> Patrick Meyer wrote:
>>> I have a comma delimited file that contains a header in the first 
>>> row of the file. The header contains column names. If I manually 
>>> create a new file without the header, I can successfully import the 
>>> new file with
>>>
>>> CALL 
>>> SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'STAFF','myfile.del',null,null,null,0); 
>>>
>>>
>>> Creating the new file in my Java application is too time consuming 
>>> for it to be a good solution. Is there a way of importing a 
>>> delimited file with a header in the first row? Or, a way to import 
>>> and ignore the first row of the file being imported?
>>>
>>> Thanks
>>>
>>
>>
>>


Re: Importing delimited files with header

Posted by Patrick Meyer <me...@itemanalysis.com>.
Thanks for your response. Do you know if extending FlatFileVTI will work 
as fast as the built-in IMPORT_TABLE function? Before using CALL 
SYSCS_UTIL.SYSCS_IMPORT_TABLE, I had written my own class that would 
read a delimited file and use the INSERT INTO statement to populate my 
Derby db. However, it took my class 1 hour to import a file but it only 
took one minute when I used SYSCS_UTIL.SYSCS_IMPORT_TABLE. Will 
extending FlatFileVTI work a quickly as using SYSCS_UTIL.SYSCS_IMPORT_TABLE?

Thanks

Rick Hillegas wrote:
> Hi Patrick,
>
> You can write a table function to help you out here. Your table 
> function will open your file, throw away the header, and then loop 
> through the lines in the file. I would recommend extending something 
> like FlatFileVTI (in Derby's demo directory). You can look at 
> PropertyFileVTI (in the same directory subtree), which will show you 
> how to extend FlatFileVTI. Once you declare your table function (let's 
> call it MyFileReader), you can then use it to populate your table as 
> follows:
>
> insert into STAFF
> select * from table( MyFileReader( 'myfile.del' ) ) s
>
> Hope this helps,
> -Rick
>
> Patrick Meyer wrote:
>> I have a comma delimited file that contains a header in the first row 
>> of the file. The header contains column names. If I manually create a 
>> new file without the header, I can successfully import the new file with
>>
>> CALL 
>> SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'STAFF','myfile.del',null,null,null,0); 
>>
>>
>> Creating the new file in my Java application is too time consuming 
>> for it to be a good solution. Is there a way of importing a delimited 
>> file with a header in the first row? Or, a way to import and ignore 
>> the first row of the file being imported?
>>
>> Thanks
>>
>
>
>

Re: Importing delimited files with header

Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Patrick,

You can write a table function to help you out here. Your table function 
will open your file, throw away the header, and then loop through the 
lines in the file. I would recommend extending something like 
FlatFileVTI (in Derby's demo directory). You can look at PropertyFileVTI 
(in the same directory subtree), which will show you how to extend 
FlatFileVTI. Once you declare your table function (let's call it 
MyFileReader), you can then use it to populate your table as follows:

insert into STAFF
select * from table( MyFileReader( 'myfile.del' ) ) s

Hope this helps,
-Rick

Patrick Meyer wrote:
> I have a comma delimited file that contains a header in the first row 
> of the file. The header contains column names. If I manually create a 
> new file without the header, I can successfully import the new file with
>
> CALL 
> SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'STAFF','myfile.del',null,null,null,0); 
>
>
> Creating the new file in my Java application is too time consuming for 
> it to be a good solution. Is there a way of importing a delimited file 
> with a header in the first row? Or, a way to import and ignore the 
> first row of the file being imported?
>
> Thanks
>