如何从CSV中批量插入一些字段有新的行字符？ [英] How to bulk insert from CSV when some fields have new line character?

问题描述

我有另一个DB的CSV转储，看起来像这样（id，name，notes）：

I have a CSV dump from another DB that looks like this (id, name, notes):

1001，John Smith， 15主街

1002，简史，2010 Rockliffe博士

普莱森维尔，IL

美国

1003，Bill Karr ，2820 West Ave.

1001,John Smith,15 Main Street
1002,Jane Smith,"2010 Rockliffe Dr.
Pleasantville, IL
USA"
1003,Bill Karr,2820 West Ave.

最后一个字段可能包含回车符和逗号，在这种情况下它被双引号括起来。我需要保留这些回报和逗号。

The last field may contain carriage returns and commas, in which case it is surrounded by double quotes. And I need to preserve those returns and commas.

我使用此代码将CSV导入到我的表格中：

I use this code to import CSV into my table:

BULK INSERT CSVTest
FROM 'c:\csvfile.csv'
WITH
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)

SQL Server 2005 批量插入无法弄清楚引号内的回车不是行终止符。

如何克服？

SQL Server 2005 bulk insert cannot figure out that carriage returns inside quotes are not row terminators.
How to overcome?

UPDATE ：

看起来像在字段中保留换行符的唯一方法是使用不同的行分隔符。所以，我想通过在它们前面放一个管道来标记所有行分隔换行符。如何更改我的CSV看起来像这样？

UPDATE:
Looks like the only way to keep line breaks inside a field is to use different row separator. So, I want to mark all row separating line breaks by putting a pipe in front of them. How can I change my CSV to look like this?

1001，John Smith，15 Main Street |

1002，Jane Smith，2010 Rockliffe Dr.

Pleasantville，IL

美国|

1003，Bill Karr，2820 West Ave. |

1001,John Smith,15 Main Street|
1002,Jane Smith,"2010 Rockliffe Dr.
Pleasantville, IL
USA"|
1003,Bill Karr,2820 West Ave.|

推荐答案

好的，这是一个小Java程序，我最后写了解决问题。

欢迎评论，更正和优化。

OK, here's a small Java program that I end up writing to solve the problem.
Comments, corrections and optimizations are welcome.

import java.io.*;

public class PreBulkInsert
{
    public static void main(String[] args)
    {
        if (args.length < 3)
        {
            System.out.println ("Usage:");
            System.out.println ("  java PreBulkInsert input_file output_file separator_character");
            System.exit(0);
        }

        try
        {
            boolean firstQuoteFound = false;
            int fromIndex;
            int lineCounter = 0;
            String str;

            BufferedReader in = new BufferedReader(new FileReader(args[0]));
            BufferedWriter out = new BufferedWriter(new FileWriter(args[1])); 
            String newRowSeparator = args[2];

            while ((str = in.readLine()) != null)
            {
                fromIndex = -1;
                do
                {
                    fromIndex = str.indexOf('"', fromIndex + 1);
                    if (fromIndex > -1)
                        firstQuoteFound = !firstQuoteFound;
                } while (fromIndex > -1);

                if (!firstQuoteFound)
                    out.write(str + newRowSeparator + "\r\n");
                else
                    out.write(str + "\r\n");
                lineCounter++;
            }
            out.close();
            in.close();
            System.out.println("Done! Total of " + lineCounter + " lines were processed.");
        }
        catch (IOException e)
        {
            System.out.println(e.getMessage());
            System.exit(1);
        }       
    }
}

这篇关于如何从CSV中批量插入一些字段有新的行字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！