Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Bulk load issues with quotes

    I'm having an issue trying to load some old CVS files created in the year 2005. I'm using SQL server 2008 express edition for now. I've never seen files in this format. For reason, i can't go into, i can't user SSIS and need to load them using the bulkinsert comand. the files contain about 20 fields with a mix of numerics and text. the text fields are delimited by 2 sets of quotes, and a , and the numeric fields by just a , with each line contained in double quotes.
    ex """abc"",12,198504,""tom"",""Smith"",49,3322,""mai n st."""
    does anyone know how to define the format file so that i can remove the "" quotes before and after each text field and how to remove the set of quotes that each record is enclosed with.

    I appreciate any help.

    I'm not a programmer and would prefer not to write a program to reformat the files befor i try to load them.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is the original CSV file format. Open the file with Excel, then save it any way that makes you happy.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    I can't open it in excel. the file is too large. It is over 2gb with over a million rows of data.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Split the file into managable pieces, import/export the smaller files thru Excel, and then combine them after reformatting with Excel.

    Or write code to parse and reformat the single file.
    Last edited by papadi; 06-02-12 at 23:04.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've got a couple of choices then:

    1) Install a 64 bit version of Excel and use it to convert the file.
    2) Spend $50 on a Developer Edition of SQL to get the tools to do the conversion
    3) Write a trivial Powershell script to do the conversion
    4) Other?

    -{atP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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