Include Load File Infile in sql [CORE2585]

[firebird-automations]

Submitted by: Ivan (patuljak)

Votes: 3

I have request for new features and is about included a "Load file" in sql.

Example

LOAD DATA INFILE 'c:\data.txt' INTO TABLE my_table FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

[firebird-automations]

Commented by: @mrotteveel

I think this might be something to add to ISQL, maybe as an extension to input. It should not be part of the normal SQL dialect since it does not make a lot of sense for remote clients to execute (not to mention potential security issues).

You can already achieve this by creating an external table (although you need a fixed width record format) and do an insert (...) select <record> from <external table>, or using external tools to load the content of a csv or other format and insert it.

[firebird-automations]

Commented by: Ivan (patuljak)

but "Load" function is nice and very helpfull.

[firebird-automations]

Commented by: Volker Rehn (vr2_s18)

@Mark, external tables have a number of issues, but the fixed length format makes them unusable for just about anything, be it csv or an ini-file.

Bulk data import is the topic, *built-in*, without programming interfaces/plugins or the usage of external tools, And it is usually no problem that the file resides on the server. This can be arranged for.

Couldn't vote this feature high enough.

... and its OUTFILE counterpart.

[krilbe]

I strongly agree. The external table format is really bad for any real world usage, at least for any data that's difficult to encode into fixed-width char fields. The external table feature would be extended to support CSV format. It would solve a lot of problems and would also not require to add extra SQL statements, just add features to the existing external table feature.

[asfernandes]

Read of CSV file may be easily done with external procedure in current days.