none
OLEDB read read very big CSV file RRS feed

  • Question

  • hi All

    i use SqlBulkCopy to insert into a table in my DB(SQL server2005),
    with data source is a very large CSV file,
    i use oledb to read this CSV file. 
    csv is very big (1Gb) ,in window notepad can't open this file.
    also oldb can't read this file too.
     how can i resolve this proplem?
    split this file?

    please tell me good way.
    THANKS ALL!
    Wednesday, January 6, 2010 7:37 AM

Answers

  • Hi,

    As I know, Bulk Insert or SQlBulkCopy should be the most efficient way when insert a single file with large amount of data.
    You could follow below documents as the best practice

    http://www.4guysfromrolla.com/articles/102109-1.aspx
    http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx

    Thanks.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Friday, January 8, 2010 9:22 AM
  • Hi,

    I think you can create procedure in sql server and pass a file name into procedure,

    your procedure may like this :

    BULK INSERT TableName -- with same columns
    FROM 'c:\samplecvsfile.txt' -- File Name From Parameter
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
    

    Soroush Sarabi Microsoft MCP , MCTS
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Wednesday, January 6, 2010 8:42 AM
  • Put your OleDb query in a loop and break the file into chunks. Either a set number like 4, 5, X times or have an algorithm so calculate how many lines to query each time. Load each partial result into your datatable and call SqlBulkCopy.
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Wednesday, January 6, 2010 3:00 PM

All replies

  • Hi,

    I think you can create procedure in sql server and pass a file name into procedure,

    your procedure may like this :

    BULK INSERT TableName -- with same columns
    FROM 'c:\samplecvsfile.txt' -- File Name From Parameter
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
    

    Soroush Sarabi Microsoft MCP , MCTS
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Wednesday, January 6, 2010 8:42 AM
  • thanks Soroush Sarabi !

    but i want to check filter this file,
    in the  case of large data, update DB by SqlBulkCopy  or DataSet , it's very slow.
    server timeout error occur.
    Wednesday, January 6, 2010 11:22 AM
  • r u kidding that you want to update 1GB data in few minutes ???? i don't think its easy way... Definitely it will take a lotz of time, i have 10,000 rows in my Excel sheet Size of 288KB , it took nearly 1.32 Minute.

    And you say u want to update 1GB of file ????


    Narayanan Dayalan -------- Please "Mark As Answer", if my answer works well with ur Query
    Wednesday, January 6, 2010 1:04 PM
  • Hi,

    I think FileHelper Library (Free and Open Source) will be best suited for your task since it involves large file to manipulate. I have been using this library to load the large files into sqlserver as it's easy and fast.

    You can create your own custom class based on your file structure. Here is the small example prepared for you.

    using FileHelpers;
    ..........
    FileHelperEngine engine = new FileHelperEngine(typeof(YourInterfaceFileClass));
    YourInterfaceFileClass[] objArrYourInterfaceFile = engine.ReadFile(strYourFilePathAndName) as YourInterfaceFileClass[];


    Once you get the array of your class objects, you can loop through and insert into data storage like MS Access, Excel or Sql Server.

    You can find the complete example from filehelpers.com. Hope it helps you.
    Hi, I am Kyi.
    Wednesday, January 6, 2010 1:43 PM
  • Put your OleDb query in a loop and break the file into chunks. Either a set number like 4, 5, X times or have an algorithm so calculate how many lines to query each time. Load each partial result into your datatable and call SqlBulkCopy.
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Wednesday, January 6, 2010 3:00 PM
  • thanks all!

    when process a large data to server, i have an ideal, transfer time is shorter:
    - client side : make (*.csv,*.txt)file to (*.cab or *.zip) file
    use ftp or http post to server.
    - server side: use webservice extract (*.cab or *.zip) file , import to DB
     import to DB by "BULK INSERT" . has any way other of "BULK INSERT"?

    is this way good ?
    Have any best way for process a large data to server?
    Thursday, January 7, 2010 2:48 AM
  • Hi,

    As I know, Bulk Insert or SQlBulkCopy should be the most efficient way when insert a single file with large amount of data.
    You could follow below documents as the best practice

    http://www.4guysfromrolla.com/articles/102109-1.aspx
    http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx

    Thanks.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Tuesday, January 12, 2010 6:47 AM
    Friday, January 8, 2010 9:22 AM
  • thanks Tim Li !
    Wednesday, January 13, 2010 12:44 AM