Post reply

Bulk Insert - (0 row(s) affected)

  • February 23, 2012 at 12:20 am

    #253497

    Hello,

    I have a .csv file which contains 4 million records. I am using Bulk Insert command to import the file into SQL Server table.

    The file contains following information.

    "Service Area Code","Phone Numbers","Preferences","Opstype","Phone Type"

    "17","9861445262","0","A","2"

    "17","9861395056","0","A","2"

    "12","9883501787","0","D","2"

    "13","9827558308","0","D","2"

    "13","9827397456","0","D","2"

    "13","9827477119","0","D","2"

    "17","9861037312","0","A","2"

    "17","9861144351","0","A","2"

    "17","9861308927","0","A","2"

    "17","9861012494","1","A","2"

    The Bulk insert command I am using to import the data

    bulk insert dbname.dbo.table

    from 'Drive:\folder_name\2122012.csv'

    with

    (

    keepnulls,

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '',

    DATAFILETYPE = 'char',

    FIRSTROW = 2,

    ERRORFILE ='Drive:\folder_name\err.txt'

    )

    I am getting

    0 row(s) affected as a result. There is no error reported.

    Please help.

    Thanks

    Rohit

  • February 23, 2012 at 12:44 am

    #1450668

    could you explain why you have set this

    SQLRO (2/23/2012)

    ROWTERMINATOR = '',

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 23, 2012 at 12:59 am

    #1450670

    bulk insert madworks.dbo.[table4]

    from 'D:\New Text document.csv'

    with

    (

    keepnulls,

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '',

    DATAFILETYPE = 'char',

    FIRSTROW = 2

    --ERRORFILE ='Drive:\folder_name\err.txt'

    )

    Jayanth Kurup[/url]

  • February 23, 2012 at 1:08 am

    #1450672

    It is actually a new line character which is not getting printed on the forum.

  • February 23, 2012 at 4:13 am

    #1450747

    For a new line character you should use "\ n" without the space.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 23, 2012 at 6:53 am

    #1450814

    also, it depends on the file format;

    if the original source text file was created in unix, you might need to use \r ( vbCr = CHAR(13)/ Return/Carriage Return) instead of \n (vbCrLf = CHAR(13) + CHAR(10) Carriage Return + line Feed )

    I'm in the elite club that can print \n on the forums!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 23, 2012 at 8:48 am

    #1450916

    Does this help?

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

    Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. Note that the field terminator of a CSV file does not have to be a comma. To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

    Data fields never contain the field terminator.

    Either none or all of the values in a data field are enclosed in quotation marks ("").

    To bulk import data from a Microsoft FoxPro or Visual FoxPro table (.dbf) file or a Microsoft Excel worksheet (.xls) file, you would need to convert the data into a CSV file that complies to the preceding restrictions. The file extension will typically be .csv. You can then use the .csv file as a data file in a SQL Server bulk-import operation.

    On 32-bit systems, it is possible to import CSV data into a SQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet. Jet treats text files as tables, with the schema defined by a schema.ini file that is located in the same directory as the data source. For a CSV data, one of the parameters in the schema.ini file would be "FORMAT=CSVDelimited". To use this solution, you would need to understand how the Jet Test IISAMm operations—its connection string syntax, schema.ini usage, registry setting options, and so on). The best sources of this information are Microsoft Access Help and Knowledge Base (KB) articles. For more information, see Initializing the Text Data Source Driver, How To Use a SQL Server 7.0 Distributed Query with a Linked Server to Secured Access Databases, HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases, and How To Open Delimited Text Files Using the Jet Provider's Text IIsam.

    Jared
    CE - Microsoft

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply