I've got a problem,

I've tryed several way's to load a comma delimited file into our table. Whe are using SQL ASE 12-5 with the default bcp installed by the installer.

After using the search I got to 2 different format files

An example of the string :
"000000000000001","00002","001","1111111",""

Format 1:

10.0
5
1 SYBCHAR 1 15 "\"," 1 import_id
2 SYBCHAR 1 5 "\"," 2 prod_code
3 SYBCHAR 1 3 "\"," 3 best_numr
4 SYBCHAR 1 7 "\"," 4 ec_nummer
5 SYBCHAR 1 1 "\"\n" 5 afdr_switc

And Format 2:

10.0
6
1 SYBCHAR 0 1 "" 0
2 SYBCHAR 0 15 "","" 1 import_id
3 SYBCHAR 0 5 "","" 2 prod_code
4 SYBCHAR 0 3 "","" 3 best_numr
5 SYBCHAR 0 7 "","" 4 ec_nummer
6 SYBCHAR 0 1 "\n" 5 afdr_switc

For both format files I have the same problem.

The problem is that bcp encountered unaspected EOF.
When I use \r as row termiator bcp inserts 2 rows with succes and after those 2 records bcp goes into error with the message bcp encountered unaspected EOF.

I know that my CR/LF is okay hex(0D 0A) for all the records.

I allready tryed serveral things to let bcp live but I still got the problem. And I need to find out because most of the data that I need to import are comma delimited file's (cvs).

Also I have some sdf files (ascii fixed length) after I build the format file for that and runs bcp on that. BCP runs fine but all of the columns that are null able when the value supose to be NULL bcp puts in there a space character. After searching op de sybase site I have te option fur using -Q but this does not work. Does sombody have any experience with this kind of problems? Can somebody help me?

Try this format:
10.0
5
1 SYBCHAR 0 15 "," 1 import_id
2 SYBCHAR 0 5 "," 2 prod_code
3 SYBCHAR 0 3 "," 3 best_numr
4 SYBCHAR 0 7 "," 4 ec_nummer
5 SYBCHAR 0 1 "\n" 5 afdr_switc

All right this works but te problem is the double quote's when I do this then all te columns starts with " and end with " I forgot to tell this. Sorry bout that. So I need to find a way to read the data with bcp wichout copying the double quote's.

Remove the double quotes in the input file.

On 192.000.000 Records in 24 files??? I don't think so. I know that BCP can handle it I need to find the procedure to manage that.

Well just dump it without the quotes then.

But here is a thread which indicates that your original option should have worked:
http://dbforums.com/showthread.php?threadid=675342

So show the first four lines of your data file, maybe there is something messed up in there?

Mattr,

Yes I know the first solution I got it from dbforums.

Here are the first three lines of the file:


"","03001","002","0000001","J","NLD","13","","0100 000010","001","0","0","0","","BREMAN","","FUITESTR AA","GENEMUIDEN","00","","02","L BREMAN EN ZN BV","00","","04","0000181","05","Klaas Fuitestraat 41","00","","00","","08","GENEMUIDEN","00","","00" ,"","99","Netherlands","12","ARiS00009749984","13" ,"**********","14","NNNY01","","","","","4","Kl aas Fuitestraat","41","","8281BX","","","","","","","" ,"",""

"","03001","002","0000002","J","NLD","13","","0100 000010","001","0","0","0","","NASSAU","","ESDOORNL AA","BEILEN","00","","02","DR NASSAU COLLEGE MIDDEN-DRENTHE","00","","04","0000928","05","Esdoornlaan 2","00","","00","","08","BEILEN","00","","00",""," 99","Netherlands","12","ARiS00009973835","13","*** *******","14","NNNY01","","","","","4","Esdoornlaa n","2","","9411AV","","","","","","","","",""

"","03001","002","0000003","J","NLD","13","","0100 000010","001","0","0","0","","GERRITSEN","","GAST" ,"ZUIDHORN","00","","02","MR J W GERRITSEN-DOORNBOS BEHEER BV","00","","04","0000994","05","De Gast 13","00","","00","","08","ZUIDHORN","00","","00"," ","99","Netherlands","12","ARiS00009986966","13"," ********** *","14","NNNY01","","","","","4","De Gast","13","","9801AA","","","","","","","","",""

Note that the example that I give is not the same as the real situation.

Here is some more information.

Statement:
bcp nrf_import..NRF_IMPORT_test in "S:\Projects\Nrf\Data\
KPN\TEST2.TXT" -fS:\Projects\Nrf\Prog\scripts\bcpformatfile\kpn2.f mt -U marco -S
SQL_RDMS_NRF -b1 -ed:\err.txt
Password:

Result:
Starting copy...
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Unexpected EOF encountered in BCP data-file.
bcp copy in partially failed

2 rows copied.

Because the limited width of the newgroup the records have extra CR/LF.

Records inserted succesfuly
"","03001","002","0000001","J","NLD","13","","0100 000010","001","0","0","0","",
"BREMAN","","FUITESTRAA","GENEMUIDEN","00","","02" ,"L BREMAN EN ZN BV","00","","04","0000181","05",
"Klaas Fuitestraat 41","00","","00","","08","GENEMUIDEN","00","","00" ,"","99","Netherlands","12",
"ARiS00009749984","13","**********","14","NNNY01", "","","","","4","Klaas Fuitestraat",
"41","","8281BX","","","","","","","","",""

"","03001","002","0000002","J","NLD","13","","0100 000010","001","0","0","0","",
"NASSAU","","ESDOORNLAA","BEILEN","00","","02" ,"DR NASSAU COLLEGE MIDDEN-DRENTHE","00","","04",
"0000928","05","Esdoornlaan 2","00","","00","","08","BEILEN","00","","00",""," 99","Netherlands","12",
"ARiS00009973835","13","**********","14","NNNY01", "","","","","4","Esdoornlaan","2","","9411AV",""," ",
"","","","","","",""

From here it's going in to the error
"","03001","002","0000003","J","NLD","13","","0100 000010","001","0","0","0","",
"GERRITSEN","","GAST","ZUIDHORN","00","","02", "MR J W GERRITSEN-DOORNBOS BEHEER BV","00","",
"04","0000994","05","De Gast 13","00","","00","","08","ZUIDHORN","00","","00"," ","99","Netherlands","12",
"ARiS00009986966","13","********** *","14","NNNY01","","","","","4","De Gast","13","","9801AA","","",
"","","","","","",""

Error message:

#@ Row 3, Column 64: @#
#@ Row 3: Not transferred @#
 E ","3001",002",000",
J",
N",13",

This is strange because the problem that the error file says that there are CR/LF in the fields.
But there are none only at the end of the line.

just a try,

is there any special character in the table for bcp out. once i got the same type of error.because a special character (enter(CR)) was there in the table column , in row where bcp failed.so if u find any such character the remove it and then do bcp out.

hope this will help,
Pooja

This is a good tip. Thnks. But I have to bcp in and the data is a csv file (comma delimited). I took a look at the data anyway and did'nt find any special character. What I have tryed is to convert the text filt to unix format but this give the same error at line 46 instead of line 3??

did u checked in table??...as the csv file u got must be bcp out from some table..check in that table...as u wont see anything in csv file.

the error which is there is because of file format error, as far as i know

Pooja

Yep I checked that. When we recieve the data from our supplier the data go into the proces of structuring, parsing, validation and ddupe. It generate a sdf file because we have to add some additional information to the file we import the data in to dbf, and export it as a CSV File. We checked it all and there is nothing wrong with the data. Even de diacriten (é,ä etc) are ok. The cr/lf (hex) = 0D 0A. I can not find anything wrong with the data. I realy think that bcp have a problem maby format file problem or something else I don't know. That's why I need help