Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Unanswered: import/ export from command line

    Hi

    Im using SQL server 2000.

    I have to write a batch file to export/import database tables from/to csv files.
    BCP is an option, but it does not work if the data has a comma.
    Is there some other utility which i can use in batch file? It must support comma as data and export/import to csv file.

    Romshan

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your data is comma-delimited and you have strings with commas that are not quoted, then you are going to have a tough time. Otherwise, the BCP utility has a variety of settings that will allow you to specify how to interpret the data, including "-t" which defines the field terminator.

    Check books online for "bcp Utility" and expand the Arguments section.

    The BCP utility would never have lasted as long as it has if it couldn't handle commas in strings!

    blindman

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You may also want to checkout DTS. This is BCP on steroids.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jul 2003
    Posts
    9
    DTS works wonderfully and identifies embedded commas , but,
    I need to write a command line utility.

    Also bcp can handle commas if writing to a text file, using lets say ~ as the delimiter. but when writing to a .csv file is when the problem arises.
    Is there some other utility like bcp for this purpose?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    BCP should be able to handle commas it they are in quoted strings:

    Field1, Field2, "Field3A, Field3b", Field4

    but no system is going to handle CSV files if the commas aren't in quoted strings:

    Field1, Field2, Field3A, Field3b, Field4

    Can you supply an example of a typical data row?

    blindman

  6. #6
    Join Date
    Jul 2003
    Posts
    9
    Field1, Field2, Field3A, Field3b, Field4 is typically how data is going to be

    123, 4, john,doe,CA

    Is it possible to add " " to all char, varchar fields from sql so that while exporting and importing, the embedded space is taken care of.
    whatever the option it should be possible to run from comand line

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you have control over the export, then use a different delimiter, such as a tab or pipe.

    You can create a view that formats your data with quotes and then export the view using BCP:
    select '"' + CharField + '"" from YourTable

    You may need to create a BCP format file to specify delimiters for individual columns. For instance, the field prior to a quoted string would use this delimiter character combination
    ,"
    and the quoted string field would use this delimiter combination
    ",
    while all others would use just the "," character.

    blindman

Posting Permissions

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