Rick,
When I set text qualifier to double quote the CSV load is failing.
Project XML:
Code: Select all<project name="CSV to DB " mainModule="Main" version="2.0" logLevel="verbose">
<description>CSV to DB </description>
<module name="Main">
<readCSV label="Read CSV" inputFile="resource:smb://filedrop/Test/Test.csv" outputRowSetVariable="CSVFile" fieldDelimiter="comma" skipInvalidRecords="false" skipFirstRow="true" recordDelimiter="CRLF" textQualifier="double quotes" version="1.0" />
<sql label="Connect to DB2" resourceId="DB" version="1.0">
<query label="Insert Data" inputRowSetVariable="${CSVFile}">
<statement>INSERT INTO gaTestCSV( ID, Name)
Values(?,?)
</statement>
</query>
</sql>
</module>
</project>
Test.csv:
ID, Name
1, Sai Kiran
2, "Vijay, Balusu"
Job Log:
12/21/16 2:40:40 AM INFO Executing module 'Main'
12/21/16 2:40:40 AM INFO Executing task 'readCSV 1.0 (Read CSV)'
12/21/16 2:40:41 AM INFO Data parsed successfully and the rowset variable 'CSVFile' was created
12/21/16 2:40:41 AM INFO Finished task 'readCSV 1.0 (Read CSV)'
12/21/16 2:40:41 AM INFO Executing task 'sql 1.0 (Connect to DB2)'
12/21/16 2:40:42 AM INFO Executing sub-task 'query'
12/21/16 2:40:42 AM INFO Executing statement - INSERT INTO gaTestCSV( ID, Name)
Values(?,?)
12/21/16 2:40:42 AM INFO Opening file 'resource:smb://filedrop/Test/Test.csv'
12/21/16 2:40:42 AM ERROR [9013 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found with boundaries '3, 0, 3, 18'. Invalid character '"' at line '3' and column '4. The field must be enclosed with in '"' characters.
12/21/16 2:40:42 AM ERROR [8044 - Connect to DB2] Last known record near boundaries (start line: 3, start column: 0, end line: 3, end column: 18) of file 'resource:smb://filedrop/Test/Test.csv'
12/21/16 2:40:42 AM ERROR [8098 - Connect to DB2] [9007 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found. Full stack trace written to '1000000000634_error_1.log'
12/21/16 2:40:42 AM INFO Finished project 'CSV to DB '
12/21/16 2:40:42 AM ERROR [8098 - Connect to DB2] [9007 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found
12/21/16 2:40:42 AM INFO End Date and Time: 12/21/16 2:40:42 AM
Thanks,
Kiran