none
Import csv with field size > 255 RRS feed

  • 問題

  • Could somebody, please help!

     

    How can I import data from a csv file with a field that is greater than 255 characters, to say SQL Server?

     

    Code that cannot view 255 characters field.

     

    A. select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\data;',
        'select * from abc.csv')

     

    B. select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
        'Data Source=E:\data;Extended Properties="Text;HDR=Yes;FMT=Delimited"')...abc#csv

     

    Setting Note: SQL Query Analyzer>Tools>Options>Result>Maximum characters per column=8192.

    2008年5月27日 下午 03:17

解答

  • Hi Jack

    Maybe you can try BULK INSERT

    Please have a look on the link below.

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    E.G. I have created a dummy table
    CREATE TABLE [dbo].[temp](
        [test] [varchar](500) NULL,
        [test1] [varchar](500) NULL
    ) ON [PRIMARY]

    GO

    and a text csv file like

    "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

    which has more than 255 char

    then I run the following SQL Query to import that data inti database

    BULK INSERT Temp FROM 'c:\text.txt' WITH (FIELDTERMINATOR = '","')


    Hope it can help.
    2008年5月27日 下午 03:42

所有回覆

  • Hi Jack

    Maybe you can try BULK INSERT

    Please have a look on the link below.

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    E.G. I have created a dummy table
    CREATE TABLE [dbo].[temp](
        [test] [varchar](500) NULL,
        [test1] [varchar](500) NULL
    ) ON [PRIMARY]

    GO

    and a text csv file like

    "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

    which has more than 255 char

    then I run the following SQL Query to import that data inti database

    BULK INSERT Temp FROM 'c:\text.txt' WITH (FIELDTERMINATOR = '","')


    Hope it can help.
    2008年5月27日 下午 03:42

  • Create Schema File (schema.ini into same dir as sourse) add section as importing file name, and Set Filed Type as MEMO for looking field

     schema.ini

    [abc.csv]
    ColNameHeader = False
    MaxScanRows = 0
    CharacterSet = ANSI
    Format = CSVDelimited
    Col1 = Field_N1 Text
    Col2 = Field_N2 Memo

    2008年9月11日 下午 04:31