External table error

Hi All, I have received a 16 column .csv file that I wanted to create an external table to access the data. I am having issues with an -26168 error. I did some testing by creating a single column and single row data file. It is in unix format and I can see the "end of line" character. I then created a single column external table CREATE EXTERNAL TABLE jh_test ( ISSUERID varchar(25) ) USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl")); I get the error when I select from the table select * from jh_test If I edit the test.unl file by putting a pipe at the end of the line the data loads. this works IID000000002157905| this does not work IID000000002157905 can anyone help? Will I have to change the comma separated fields to pipe separated fields when I add all the columns back? And will I have to allows have a pipe as the end of field/row delimitor

You can specify the delimiter in the definition. A simple sed script will add a trailing delimiter Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JOHN HENRY Sent: Thursday, August 24, 2017 3:24 PM To: ids@iiug.org Subject: External table error [39747] Hi All, I have received a 16 column .csv file that I wanted to create an external table to access the data. I am having issues with an -26168 error. I did some testing by creating a single column and single row data file. It is in unix format and I can see the "end of line" character. I then created a single column external table CREATE EXTERNAL TABLE jh_test ( ISSUERID varchar(25) ) USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl")); I get the error when I select from the table select * from jh_test If I edit the test.unl file by putting a pipe at the end of the line the data loads. this works IID000000002157905| this does not work IID000000002157905 can anyone help? Will I have to change the comma separated fields to pipe separated fields when I add all the columns back? And will I have to allows have a pipe as the end of field/row delimitor **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

You CAN use commas, however, for some unfathomable reason Informix has always required a trailing delimiter after the last field on the record before the record delimiter. So, this file should work for example: IID000000002157905, With this external table definition: CREATE EXTERNAL TABLE jh_test ( ISSUERID varchar(25) ) USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl"), DELIMITER ',' ); Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 24, 2017 at 4:24 PM, JOHN HENRY <jhenry@gwkinvest.com> wrote: > Hi All, I have received a 16 column .csv file that I wanted to create an > external table to access the data. I am having issues with an -26168 error. > > I did some testing by creating a single column and single row data file. > It is > in unix format and I can see the "end of line" character. I then created a > single column external table > > CREATE EXTERNAL TABLE jh_test > ( > ISSUERID varchar(25) > ) > USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl")); > > I get the error when I select from the table > > select * > from jh_test> > If I edit the test.unl file by putting a pipe at the end of the line the > data > loads. > > this works > IID000000002157905| > > this does not work > IID000000002157905 > > can anyone help? Will I have to change the comma separated fields to pipe > separated fields when I add all the columns back? And will I have to allows > have a pipe as the end of field/row delimitor > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

External tables have a recordend option - it might be possible to set that to EOL. Not something I have tried though Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 Oninit® is a Registered Trademark of Oninit LLC > On Aug 24, 2017, at 16:35, Art Kagel <art.kagel@gmail.com> wrote: > > You CAN use commas, however, for some unfathomable reason Informix has > always required a trailing delimiter after the last field on the record > before the record delimiter. So, this file should work for example: > > IID000000002157905, > > With this external table definition: > > CREATE EXTERNAL TABLE jh_test > ( > ISSUERID varchar(25) > ) > USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl"), > > DELIMITER ',' ); > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > >> On Thu, Aug 24, 2017 at 4:24 PM, JOHN HENRY <jhenry@gwkinvest.com> wrote: >> >> Hi All, I have received a 16 column .csv file that I wanted to create an >> external table to access the data. I am having issues with an -26168 error. >> >> I did some testing by creating a single column and single row data file. >> It is >> in unix format and I can see the "end of line" character. I then created a >> single column external table >> >> CREATE EXTERNAL TABLE jh_test >> ( >> ISSUERID varchar(25) >> ) >> USING (DATAFILES("DISK:/opt/gim2usr/log/feed/test.unl")); >> >> I get the error when I select from the table >> >> select * >> from jh_test>> >> If I edit the test.unl file by putting a pipe at the end of the line the >> data >> loads. >> >> this works >> IID000000002157905| >> >> this does not work >> IID000000002157905 >> >> can anyone help? Will I have to change the comma separated fields to pipe >> separated fields when I add all the columns back? And will I have to allows >> have a pipe as the end of field/row delimitor >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.