Post reply

Import CSV File Data

  • June 5, 2007 at 7:33 pm

    #124172

    How can I import CSV file data into SQL Server table? I need to insert data from CSV file to table twice a day. Table has more then 10 fields but I only need to insert value into two fields.

    Any suggestion?

    Thanks In Advance

  • June 5, 2007 at 7:41 pm

    #710987

    Lots of options. I'd personally look into bcp or DTS. DTS would probably be my first choice, since every time I do design an import method of some sort, we invariably are asked to have the functionality increased, such as error checking, e-mailing rowcounts, performing some command line operation if it fails, etc., at which point the ease of doing it with bcp is eliminated.

    If you know the requirements will never change, and if you want a fairly easy solution, then bcp might work for you as well. It's certainly a useful little program at times.

  • June 5, 2007 at 7:50 pm

    #710991

    Thanks For the Respose

    DTS is not an option. 

    I can use BCP insert but it will insert data into all columns.  I don't see anyway to insert data for specific field in table.

    Can you provide me with some example ?

    Thanks

     

  • June 5, 2007 at 8:22 pm

    #710999

    I haven't done it in a while, but doesn't putting a zero into the "column sort order" column in the format file skip over that field in the source file?

  • June 6, 2007 at 11:05 am

    #711195

    Try using this from QA.

    Declare @vchrBulkCmd varchar(500)

    SET @vchrBulkCmd = 'BULK INSERT TblCsv

    FROM ''c:\Testcsv.txt''

    WITH ( DATAFILETYPE= ''char'', ROWTERMINATOR = '''+CHAR(10)+'''

    )'

    exec(@vchrBulkCmd)

  • June 7, 2007 at 7:30 am

    #711407

    Prasad,

    Thanks for the response.

    I actully need to update my table with CSV data...twice a day. 

    I don't think there is a Bulk Update....any other way to work around this ?

     

  • June 7, 2007 at 9:29 am

    #711447

    Sohil, did setting the column sort order to 0 not work for you?

  • June 7, 2007 at 10:14 am

    #711456

    David,

    I did not try seeting the column sort order.  Since the requirment change.

    Now i need to update value in table from CSV file data...insted of inserting data as new value.

     

     

  • June 7, 2007 at 10:19 am

    #711457

    In that case, I'd recommend a staging table. Use that to perform the update.

  • June 7, 2007 at 10:21 am

    #711458

    David,

    What is staging table ? Can you give me an example ?

     

    Thanks

  • June 7, 2007 at 10:25 am

    #711460

    A staging table is typically used as a temporary placeholder for data, often from some source other than SQL Server.

    The idea is that you truncate the staging table and reload it (or drop and recreate it if that works better for you) every time you have new data to handle. Once it's in the staging table, you either clean it up, insert various portions of it into their final tables, create a new table from it, etc. Basically, pretend that your data feed comes in the form of a SQL Server table, instead of a CSV file, and you have the concept of a staging table.

    If you post more details, we can help you with the actual coding, but the above should get you started down the path.

  • June 7, 2007 at 11:04 am

    #711471

    David,

    I have table "tblCSVDataUpdate" which needs to be update twice a day from "C:\CSV\CSVUpdate.txt" file

    tblCSVDataUpdate has following fields:

    OrderNo

    Prize

    Quantity

    CustNo

    ProductID

    From CSV file i only need to update:

    Prize and Quantity

    This is what i have so far:

    Bulk Insert tblCSVDataUpdate 

     From 'C:\CSV\CSVUpdate.txt'

     With

         (

      FIELDDTERMINATOR = ',',

                    ROWTERMINATOR = '\N'

                 )

    How can i only update two fields data from CSV file?

    Do i have to run this as stored procedure ?

    How can i schedule this to run twice a day ? I'm using SQL 2005

     

    Thanks for all your help

     

     

  • June 7, 2007 at 11:17 am

    #711475

    Now that we have more details, a few more questions.

    What data in the text file joins to the data in the table so that you can update appropriately? Alternatively, you could just list the columns that are in the text file and we could probably make a good guess.

    If the data in the text file doesn't have a corresponding row in the table, do you insert it or just ignore it. In other words, can the table grow based on data found in the text file, or only change?

    As for running it twice a day, a stored proc would be fine, as would SSIS (or even command line if you really wanted for some reason). Put whichever you choose into a job, and schedule that job to run whenever you want.

  • June 7, 2007 at 11:25 am

    #711478

    1-OrderNo from the text file will join with OrderNo field in table

    2-If the text file does't have a corresponding row in table, we will just ignore it.  Text file will only have OrderNo, Prize, Quantity). 

    3-How can i go about adding coomand line to run the job ?

     

    Thanks

     

  • June 7, 2007 at 11:43 am

    #711483

    Okay, I'd create a staging table to hold the data from the text file, and for safekeeping, I'd also make a copy of the table you're working with, in case something goes wrong in the process. Then, create a stored procedure that does the following:

    1. Truncate the staging table. Be careful that you don't truncate your main table.

    2. Does the Bulk insert that you have now

    3. Update your main table, joined to the staging table on OrderNo.

    Once that procedure is done, create a job with a single step, and have that step call the stored procedure. Schedule the job to run twice a day, at whatever times you need it to.

Viewing 15 posts - 1 through 15 (of 24 total)

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