Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Load csv data into table

    Hello all,

    I am writing a program to load all rows from a csv file into a mssql table using bsp command the command i gave is as follows
    bcp "DBNAME.DB_USER.tableName in fileName.csv -m 50000 -f fileName.fmt -e Bad.log -C RAW -S DBSERVR -U DB_USER -P DB_PASSWORD -k
    say there are three records in the csv file, data is getting imported into the table but only first two records are inserted in the table the bad file is empty not sure which option i missed in the bcp command. I want to load all three records in the table the records are seperated by new link char, also
    This was first now the second,
    what if i want to skip the first record of the csv file which option should i be using for this. Plz let me know

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you post your exact bcp command (edit the password and username though of course)? That would not insert anything.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For the second question, check out the first row parameter (-F).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2007
    Posts
    56
    there is only one f parameter i.e -f fileName.fmt and no -F parameter should i add this one if yes then please let me know the parameter value which i should give so that all the rows are selected.

    here is the bcp command, i have given this in a bat file, so on running this bat file the process runs.
    bcp "%DBNAME%.%DB_USER%.SCT_tablename" in filename.csv -m %MAX_ERRORS% -f filename.fmt -e filenameBad.log -C RAW -S %DBSERVR% -U %DB_USER% -P %DB_PASSWORD% -k

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Default is to insert ALL rows so only use -F (and -L for last row) if you don't want all rows (e.g. if there is a header).
    Does your last row in the CSV have a row terminator at the end? (default is newline).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2007
    Posts
    56
    Thanks pootle flump

    The newline for the last record did the trick, now all the records and getting import in the table

    Thanks again

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good stuff. Experiment with -F and you'll see you can configure SQL Server to skip rows at the top and you'll be set.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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