Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    6

    Unanswered: Upload data from commo sepearetd csv file

    Hi All,

    good Morning, I just join this forum. I need one help.

    I have one csv file commo sepearted.

    Myfile.csv:Contains value like

    "210","JAG001","2","2008-09-09 08:39:24","Refused"
    "200","JAG002","1","2008-05-10 08:39:24","in progress"

    I am using BCP utility to upload the data.
    ************************************************** *

    SET @filename= 'Permit_Daily1'
    SET @strSQL= DB_NAME() + '..Test_Table' --'SELECT * FROM ' + DB_NAME() + '..MFDataIn ORDER BY WebRefNo'
    SET @strInputFilePath = 'D:\Others\Example\Permit_Daily1.csv' --'E:\FTProot\MFProcess\Lit_Request\literature.forma t.csv'
    SET @strFormatfilePath = 'D:\Others\Example\Example.fmt' -- 'E:\FTProot\MFProcess\ExportMF.fmt'
    SET @strErrorfilePath = 'D:\Others\Example\error.txt' --'E:\FTProot\MFProcess\ErrorFiles\Lit_Error.txt'


    BEGIN


    SET @vLoadString = 'bcp "' + @strSQL
    + '" in '+ @strInputFilePath
    + ' -t"," ' + ' -q'
    + ' -f' + @strFormatfilePath
    + ' -e' + @strErrorfilePath
    + ' -q' + ' -b 10000 -m 1000 -T '+ ' ';

    Print 'My Query :- ' + @vLoadString

    Exec @iResult = master..xp_cmdshell @vLoadString
    END
    ************************************************** *

    My format file is :

    8.0
    5
    1 SQLCHAR 0 2 "," 1 Prefix Latin1_General_CI_AS
    2 SQLCHAR 0 10 "," 2 Work_ref Latin1_General_CI_AS
    3 SQLCHAR 0 2 "," 3 Permit_Ref Latin1_General_CI_AS
    4 SQLCHAR 0 8 "," 4 Permit_issued_date Latin1_General_CI_AS
    5 SQLCHAR 0 20 "\r\n" 5 Permit_App_Status Latin1_General_CI_AS

    ************************************************** *****

    When i running this utility it is giving me an error like :

    #@ Row 1, Column 1: Invalid character value for cast specification @#
    "210" "JAG001" "2" "2008-09-09 08:39:24" "Refused"
    #@ Row 2, Column 1: Invalid character value for cast specification @#
    "200" "JAG002" "1" "2008-09-09 08:39:25" "Granted"


    It is not able to recognise values under quotes.
    Can any one help to correct the sytax so that it can pich up values under quotes correctly.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the results of your "Print" line please?
    I'm guessing the error is here:
    Code:
    + ' -t"," ' + ' -q'
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    6
    Hi Georgev,

    Please find the message:

    My Query :- bcp "Permit_Invoice_System..Test_Table" in D:\Others\Example\Permit_Daily1.csv -t"," -q -fD:\Others\Example\Example.fmt -eD:\Others\Example\error.txt -q -b 10000 -m 1000 -T

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    bcp "Permit_Invoice_System..Test_Table" in D:\Others\Example\Permit_Daily1.csv -t"," -q -fD:\Others\Example\Example.fmt -eD:\Others\Example\error.txt -q -b 10000 -m 1000 -T
    errors highlighted
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by diwa
    I am using BCP utility to upload the data.
    thread moved from MySQL to SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might also consider using BULK INSERT to make the parameters a bit easier to manage.

    -PatP

Posting Permissions

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