Results 1 to 6 of 6

Thread: Select Column

  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: Select Column

    Is there a way to select a column number without knowing its name? For example I want to select column 3 of a file without knowing it is:

    Select [column 3] FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
    DefaultDir=D:\Reports\Test\;','select * from Report.txt ')

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    None that I know of...

    Technically it would be possible to generate some dynamic SQL bassed on an analysis of the system tables but the question that comes to mind is, "Why are you attempting to select the third column?"
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Oct 2005
    Posts
    58
    The name of the 3rd column is not always the same in the .txt file.

  4. #4
    Join Date
    Oct 2005
    Posts
    58
    Not possible???????

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As MSDASQL is deprecated, I'd advise against using it at all. Based on what you've described so far, I'd suggest using BULK INSERT into a table where you already know the column names.

    -PatP

  6. #6
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you can use DTS to import the CSV data into a SQL table. in DTS you are allowed to map columns by position and can ignore columns that are not of interest.

    otherwise, you can use something like below as workaround

    Code:
    declare @3rdCol as varchar(50)
    Select * into Dummy FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=D:\Reports\Test\;','select * from Report.txt')
    set @3rdCol = (select name from syscolumns where id = object_id('Dummy') and ColOrder=3)
    exec ('select ' + @3rdCol + ' from Dummy')
    drop table Dummy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •