Post reply

csv file upload

  • February 24, 2009 at 1:02 pm

    #200218

    Hello All,

    I want to upload a csv file to sql server in a new table each time i get this csv file. And drop the table after use.

    can I setup a process to create a table dynamically, and upload this csv file records?

    The reason to do this is, very often I get the csv file with some additional fields.

    or is there a way to get specific fields from csv file without uploading the file? ...any way in SSIS?

    Thank you in anticipation.

  • February 24, 2009 at 3:56 pm

    #949689

    Learning_Phase

    The answer is Yes there is. The sample code below will do just that.

    Each column in the table created will be VARCHAR(250) with the column names as given in the SELECT statement. The fact that the SQL table name and the name of the csv file are identical is coincidental. Basically it is a SELECT INTO statement which creates the table without regard to the actuall data source.

    Review the SELECT INTO, and OPENROWSET subjects in Books On Line for more detailed information.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ImportAllowed]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ImportAllowed]

    /*

    Imports from a CSV file

    into a SQL Server 2005 database table "Allowed" which is created when the statement executes

    */

    AS

    SELECT cuic , ccb , citem , ccage , cat ,

    callow_qty , canc , canc_qty , csite_updt

    INTO dbo.Allowed

    FROM OPENROWSET(''MSDASQL'',''Driver={Microsoft Text Driver (*.txt; *.csv)};

    DefaultDir=F:\;'',

    ''SELECT * from F:\DataFiles\Allowed.csv'')

    --[dbo].[ImportAllowed]'

    END

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 25, 2009 at 8:41 am

    #950032

    Thanks for the reply....i tried the code but i get this error...

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".

    Msg 7303, Level 16, State 1, Line 5

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    is something not enabled on my server?

  • February 25, 2009 at 8:43 am

    #950036

    also i am using 32bit standard edition sql server 2005

  • February 25, 2009 at 5:22 pm

    #950343

    Learning_Phase

    My fault, please accept my apologies

    forgot to tell you that

    DefaultDir=F:\;'',

    in place of the F:\; should be the path to your .csv for example

    DefaultDir=C:\Test Data\myfile.csv;' ,

    and that the file should be on the server and of course you must have permission to access that file.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 26, 2009 at 12:10 am

    #950435

    I believe you can use a UNC there so the file can be on another machine other than the server. But, whatever login the server uses, it must be able to see whatever that "share" is.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 26, 2009 at 7:29 am

    #950583

    It still does not work, giving the same error that I posted before.

    Also do I have to use double quotes? it is giving me syntax error if i do that.

    Thanks for being so helping

  • February 26, 2009 at 7:56 am

    #950608

    I've done generic CSV file uploads using SSIS and am in the process of about writing an article on it (along with some related file stuff in SSIS)

    PM me if you want a copy of the package

  • February 26, 2009 at 11:50 am

    #950747

    Thanks Samuel,

    I am really interested in uploading the fields that I want from csv file into the table rathar than doing a mass upload. Is that what the SSIS package does?

  • February 26, 2009 at 12:01 pm

    #950756

    It loads all fields from all files, just give it an initial directory path and it will find all the csv files in that directory and it's subfolders and load them in to SQL Server using the file name as the table name.

    However, the way it's set up to work means that it could be interupted after it's done it's initial investigation of all the files and the automatically defined settings could be modified to only load certain columns.

  • March 3, 2009 at 10:11 am

    #952556

    I did look online, some suggest making changes to surface area configuration like enable OLE Automation...is that required?...tried that as well but no luck

    I still have problems running the query....can anyone give me an example of using linked server?...or suggest any other approach...

    Thanks!!

  • March 3, 2009 at 11:50 am

    #952633

    Using the Surface Area Configuration.

    On the first screen select

    Surface Area Configuration for features - it is close to the bottom of the screen.

    In the next form - left frame Click on Ad hoc remote queries .... in the right pane is there a check to the left of "Enable Openrowset ...........

    If not checked, please check and click on the "Apply command button", then the "OK' button.

    Tell us if that helps.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 3, 2009 at 12:31 pm

    #952664

    Thats already enabled...doesnt help...

    Thanks!!

Viewing 13 posts - 1 through 12 (of 12 total)

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