1

I am generating a CSV file that will be read into a database using sqlldr and was wondering if there are compatible comments that I can put in the file?

# Currently I am using comments of this format
# and I want to add some information about where the file came from

However, sqlldr reports these as rows not loaded due to data errors, which could be confusing for any user attempting to debug the import process.

I have taken a look at the sqlldr documentation and it does not mention anything about comments. The answer to this question also outlines that CSV files do not have comments of their own standard, but that the standard is defined by the application reading the file (in this case sqlldr).

Is there a compatible comment type that can be used with sqlldr?

Community
  • 1
  • 1
Rossiar
  • 2,416
  • 2
  • 23
  • 32

1 Answers1

2

I don't believe sqlldr allows for comments in the data per-say, but you may be able to get around it by specifying a with clause in the control file that only loads rows where the first character does not equal '#'. Note that the '#' would always have to be in the same position, not anywhere on the line.

EDIT: I just tried it and it works like a charm.

Data file:

# This is a comment in the data file
1|\a\ab\|do not "clean" needles|@
# This is a comment in the data file
2|\b\bg\|wall "69" side to end|@
# This is a comment in the data file

Control file:

load data
infile 'x_test.dat'
into table X_TEST
when (1:1) <> '#'
fields terminated by "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  col1,
  col2,
  col3
)
Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Thanks for the answer! I'm seeing that the records are discarded in the log file. But while it is good that they are ignored, this could still be confusing for the user. Are you seeing `Record 1: Discarded - failed all WHEN clauses` as well? Or do I need to fiddle with my CTL file some more? – Rossiar Jul 17 '15 at 14:21
  • 1
    I agree but I think that's how it is. I would advise not putting comments in the data anyway, but maybe create a log file or log table entry that goes with the file that would contain the info about that file. You could always write your own program to read the file and load the data. Please post back if you find a better way! – Gary_W Jul 17 '15 at 14:27
  • You're right, it's more important that the data is processed cleanly, and if I have to sacrifice some metadata, then that's fine. – Rossiar Jul 17 '15 at 14:29
  • You don't have to sacrifice metadata, just come up with a different way to handle it to do what you need. i.e. We use a table-driven wrapper program that builds a sqlldr commandline based on a file's entry, runs it while keeping stats and then logs info about the load into a load_log table. Time elapsed, records in the file, records inserted, etc. Each line in the data table is stamped with the load_log table entry ID for fast troubleshooting too. It's really a poor man's ETL tool. You have to decide if it's worth the trouble for your needs. – Gary_W Jul 17 '15 at 14:36
  • I can appreciate that, but I wanted something that was bound to the file. I.e. even if the user drags it way out of context, they still might have a chance of realising what it is for / where it came from. – Rossiar Jul 17 '15 at 14:39
  • 1
    Create a naming convention for the data file that would help identify it's source. – Gary_W Jul 17 '15 at 14:41