Load table problem - Non-space text found after ending quote character for an enclosed field

[Former Member]

Hi folks,

I am getting the error "Non-space text found after ending quote character for an enclosed field" when I attempt to load data into a table using the statement below. Any ideas how to resolve this issue?

load table FACTS_29_4d29401c_b16e_4914_adfd_2f5ca94bbe7b(id_1 null(ZEROS), id_2 null(ZEROS), id_3 null(ZEROS), id_4 null(ZEROS), id_5 null(ZEROS), id_6 null(ZEROS), id_7 null(ZEROS), id_8 null(ZEROS), id_9 null(ZEROS), id_10 null(ZEROS), id_11 null(ZEROS), id_12 null(ZEROS), id_13 null(ZEROS), id_14 null(ZEROS), id_15 null(ZEROS), id_16 null(ZEROS), id_17 null(ZEROS), id_52 null(ZEROS))

    from 'C:\\test2.csv' escapes off

The file I am loading is attached (test2.csv). It contains the following data:

'1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','52'

Note, there is a new line after the '52' (so the file contains two lines - the one above and a blank line). The load works when there is no new line after the '52'.

The schema of the table I am loading it into is:

Column,Type,Nullable,Primary Key

'id_1','integer',1,0

'id_2','varchar(14)',1,0

'id_3','varchar(10)',1,0

'id_4','varchar(11)',1,0

'id_5','varchar(5)',1,0

'id_6','integer',1,0

'id_7','varchar(5)',1,0

'id_8','varchar(5)',1,0

'id_9','varchar(11)',1,0

'id_10','integer',1,0

'id_11','varchar(5)',1,0

'id_12','integer',1,0

'id_13','integer',1,0

'id_14','integer',1,0

'id_15','integer',1,0

'id_16','varchar(9)',1,0

'id_17','varchar(5)',1,0

'id_52','integer',1,0

Cheers,

Máirtín

test2.csv.zip

[tayeb_hadjou · Accepted solution]

Hi Mairtin,

Add a comma character after the last value 52 in csv file :  ...., '16','17','52',

and test again.

Regards,

Tayeb.

[markmumy · Accepted solution]

I would also add DELIMITED BY and ROW DELIMITED BY.  You are missing a bunch of options after the FROM clause that we usually recommend.  \

If you will be omitting the comma after the last column of data, use the FORMAT BCP option.  If you will be adding a comma after the last column, use the FORMAT ASCII option.

Also, ROW DELIMITED BY is a mandatory option even though we aren't quite clear about that in the docs.  You can try using '\n' though I have found this to be hit or miss based on the data.  For windows formatted files, I would use '\x0a\x0d' instead.

from 'C:\\test2.csv' escapes off

format bcp -- or format ascii

quotes off

delimited by ','

row delimited by '\x0a\x0d'

[Former Member · Accepted solution]

Hi Tayeb,

That sort of worked. It works for one record. But when I add a second record to the file, the first column of the second record ends up as null in the table.

So the the file contains:

'1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','52',

'1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','52',

And the table contains:

id_1,id_2,id_3,id_4,id_5,id_6,id_7,id_8,id_9,id_10,id_11,id_12,id_13,id_14,id_15,id_16,id_17,id_52

1,'2','3','4','5',6,'7','8','9',10,'11',12,13,14,15,'16','17',52

NULL,'2','3','4','5',6,'7','8','9',10,'11',12,13,14,15,'16','17',52

This was just a dummy example I created as I thought it narrowed down the problem. The real command I am running is:

load table FACTS_29_4d29401c_b16e_4914_adfd_2f5ca94bbe7b(id_1 null(ZEROS), id_2 null(ZEROS), id_3 null(ZEROS), id_4 null(ZEROS), id_5 null(ZEROS), id_6 null(ZEROS), id_7 null(ZEROS), id_8 null(ZEROS), id_9 null(ZEROS), id_10 null(ZEROS), id_11 null(ZEROS), id_12 null(ZEROS), id_13 null(ZEROS), id_14 null(ZEROS), id_15 null(ZEROS), id_16 null(ZEROS), id_17 null(ZEROS)) from 'C:\\SVID2108034087875431268.csv' skip 1 strip off delimited by ',' row delimited by '\n' quotes on escapes off format bcp defaults on

And the CSV file I am attempting to load attached (SVID2669529579482539795.txt)

The real command has the "format bcp" set, so there should be no need for a delimiter at the end of each line in the CSV right? But I am still getting the error I reported above i.e. "Non-space text found after ending quote character for an enclosed field"

Any ideas?

Máirtín

SVID2669529579482539795.txt.zip

[tayeb_hadjou · Accepted solution]

Hi Mairtin,

If you decided using bcp format, the column delimiter must be specified when it is not the default (tab) and do NOT add a column delimiter after last column :

• For FORMAT BCP, the default column delimiter for the LOAD TABLE statement is <tab>
  and the default row terminator is <newline>.

• For FORMAT BCP, the last column in a row must be terminated by the row terminator, not by the column delimiter. If the column delimiter is present before the row terminator,then the column delimiter is treated as a part of the data.

Also QUOTES must be ON (default) when values in the data file are placed between quotes.

LOAD TABLE Statement

As Mark suggested, always specify explicitly column and row delimters.

[Former Member · Accepted solution]

Hi Tayeb,

I think my real statement matches all the criteria you mention yet I still get that error about "Non-space text" when loading, any ideas?

• "If you decided using bcp format, the column delimiter must be specified "....this is specified the LOAD statement.

• "Also QUOTES must be ON (default) when values in the data file are placed between quotes"....this is specified in the LOAD statement.

My real SQL statement and real data file (SVID2669529579482539795.txt) are in my previous post. But here is the real SQL statement again:

load table FACTS_29_4d29401c_b16e_4914_adfd_2f5ca94bbe7b(id_1 null(ZEROS), id_2 null(ZEROS), id_3 null(ZEROS), id_4 null(ZEROS), id_5 null(ZEROS), id_6 null(ZEROS), id_7 null(ZEROS), id_8 null(ZEROS), id_9 null(ZEROS), id_10 null(ZEROS), id_11 null(ZEROS), id_12 null(ZEROS), id_13 null(ZEROS), id_14 null(ZEROS), id_15 null(ZEROS), id_16 null(ZEROS), id_17 null(ZEROS)) from 'C:\\SVID2108034087875431268.csv' skip 1 strip off delimited by ',' row delimited by '\n' quotes on escapes off format bcp defaults on

Cheers,

Máirtín

[tayeb_hadjou · Accepted solution]

Please post the create table statement and IQ version you are using.

[Former Member · Accepted solution]

The IQ version info is (from running select @@version😞

Sybase IQ/15.4.0.6567/111107/P/GA/MS/Windows 2003/64bit/2011-11-07 01:34:35

The create table statement I don't have readily to hand (as another application creates the table). But here is the schema of the table:

Column,Type,Nullable,Primary Key

'id_1','integer',1,0

'id_2','varchar(14)',1,0

'id_3','varchar(10)',1,0

'id_4','varchar(11)',1,0

'id_5','varchar(5)',1,0

'id_6','integer',1,0

'id_7','varchar(5)',1,0

'id_8','varchar(5)',1,0

'id_9','varchar(11)',1,0

'id_10','integer',1,0

'id_11','varchar(5)',1,0

'id_12','integer',1,0

'id_13','integer',1,0

'id_14','integer',1,0

'id_15','integer',1,0

'id_16','varchar(9)',1,0

'id_17','varchar(5)',1,0

'id_52','integer',1,0

[c_baker · Accepted solution]

Based on the mix of character and numeric data in your DDL, the 'NULL (ZEROS)' will only work with numeric data.

Also, your incoming data encloses integer values within quotes.  Quotes can only enclose character data not numeric.

Try removing the quotes from all your incoming data and all the null specifiers to see if that works.

Chris

[tayeb_hadjou · Accepted solution]

I did a test in my IQ15.4 env.

It works with one of 2 modifcations below

- Remove quotes from all numeric values only in data file

- Remove quotes from all values

With bcp format (compatible with data exported from ASE via bcp utility), IQ expects values without quotes.

From load table statement :

"Adaptive Server Enterprise BCP does not support the QUOTES option. All field data
is copied in or out equivalent to the QUOTES OFF setting. As QUOTES ON is the default
setting for the Sybase IQ LOAD TABLE statement, you must specify QUOTES OFF when
importing ASE data from BCP output to a Sybase IQ table."

Regards,Tayeb."

[Former Member · Accepted solution]

Cheers for that! Appreciate the help.