Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2002
    Posts
    13

    Question Unanswered: BCP Problem with CSV files

    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?

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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
    Thanks,

    Matt

  3. #3
    Join Date
    Aug 2002
    Posts
    13
    Originally posted by MattR
    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.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Remove the double quotes in the input file.
    Thanks,

    Matt

  5. #5
    Join Date
    Aug 2002
    Posts
    13
    Originally posted by MattR
    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.

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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?
    Thanks,

    Matt

  7. #7
    Join Date
    Aug 2002
    Posts
    13
    Originally posted by MattR
    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.

  8. #8
    Join Date
    Aug 2002
    Posts
    13

    Re: BCP Problem with CSV files

    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.

  9. #9
    Join Date
    Dec 2002
    Posts
    104
    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
    Last edited by pooja; 07-23-03 at 06:38.

  10. #10
    Join Date
    Aug 2002
    Posts
    13
    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??


    Originally posted by pooja
    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

  11. #11
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by natas
    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

  12. #12
    Join Date
    Aug 2002
    Posts
    13
    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

    Originally posted by pooja
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •