Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Controlling column order when reading text file based on schema.ini and OPENDATASOURCE Expand / Collapse
Author
Message
Posted Friday, January 11, 2008 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:36 PM
Points: 10, Visits: 52
Hi All,

I receive pipe-delimited text files that I want to query using SQL SMS 2005. The problem is that the columns from the text file are returned in alphabetical order according to the column name rather than the physical column order. Without specifying the columns in the select statement, is there a way to default the order to the physical column order? I've found this behavior also in the Query Analyzer as well.

Thanks very much for any help here,
Steve Lord



The select statement that I'm using is:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\FTP_Membership_Files;Extended Properties="text"')...Mbrshp_200712#txt

The schema.ini file is:

[Mbrshp_200712.txt]
ColNameHeader=TRUE
CharacterSet=ANSI
Format=Delimited(|)
MaxScanRows=1
DateTimeFormat="yyyy-mm-dd hh:nn:ss"

Col1=Carriermemid char width 255
Col2=Lastname char width 255
Col3=Firstname char Width 255
Col4=Middlename char width 255
Col5=Sex char width 255
Col6=DOB Date
Col7=SSN char width 255
Col8=addr1 char width 255
Col9=addr2 char width 255
Col10=city char width 255
Col11=state char width 255
Col12=zip char width 255
Col13=phone char width 255
Col14=effdate Date
Col15=termdate Date
Col16=primarystatus char width 255
Col17=insurredSSN char width 255
Col18=Group# char width 255
Col19=GroupName char width 255
Col20=Carrier char width 255
Col21=Region char width 255
Col22=BenefitPlan char width 255
Col23=PCPCopay Currency
Col24=SpecCopay Currency
Col25=RxCopays Currency
Col26=DirectSpecCopay Currency
Col27=VisionExam char width 255
Col28=VisionHardware char width 255
Col29=PCPName char width 255
Col30=enrollid char width 255
Col31=OldMemID char width 255
Col32=AsOfDate Date
Col33="Run Date" Date


The header row and an example row from the text file:
carriermemid|lastname|firstname|middlename|sex|dob|ssn|addr1|addr2|city|state|zip|phone|effdate|termdate|primarystatus|InsurredSSN|Group#|GroupName|Carrier|Region|BenefitPlan|PCPCopay|SpecCopay|RxCopays|DirectSpecCopay|VisionExam|VisionHardware|PCPName|enrollid|OldMemID|AsOfDate|RunDate

888576176 |KNROKLD |JARRYD |L |M|1945-01-18 00:00:00|945955332 |9999 AN BLVD | |Somewhere |FL|88888 |8888888888 |2007-05-01 00:00:00|2078-12-31 00:00:00|P|945955332 |110028 |SOME EMPLOYER |Commercial HMO |SB|VP5|$15|10%|$2/10/25/45|N/A| | | |PEN000000123456|94595533201 |2007-12-01 00:00:00|2007-12-07 09:45:00

Post #441953
Posted Tuesday, January 15, 2008 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 335, Visits: 1,957
ok, the Jet OLEDB connection is breaking the text file up in to object compenents which are then returned to your query, the order of the columns is never important as they are always named correctly.
(If, programatically the order is important then you need to review your system)

To reorder the columns in your query; explicitly name them in the select list:

select Carriermemid, Lastname, Firstname ...etc with the rest of the column names
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\FTP_Membership_Files;Extended Properties="text"')...Mbrshp_200712#txt
Post #442956
Posted Tuesday, January 15, 2008 6:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:36 PM
Points: 10, Visits: 52
Hi Samuel,

Thanks very much for the explanation. For files with a relatively small number of columns, explicitly naming them in the select is not a problem. Really I was mostly curious about the behavior - didn't find it in the documentation.

Thanks again,
-Steve Lord
Post #442965
Posted Tuesday, January 15, 2008 7:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 335, Visits: 1,957
for larger number of columns - copy and paste the header row into your select command, then you can find and replace the pipe symbol with ],[ and then finish off with a leading [ and trailing ]
col1|col2|col3|col4

becomes

[col1],[col2],[col3],[col4]

or if you want to be really lazy, use something like textpad which has regex search and replace, you can then replace the pipe symbol with:
],
[

so your columns are listed nicely.

or use an online tool like:
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
to format the query.


remember, if you're working as a programmer than anything can be programmed, even the sometimes tedious task of programming

Post #442970
Posted Wednesday, March 23, 2011 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 03, 2012 6:09 PM
Points: 1, Visits: 5
Could you please let us know where the schema.ini file should be located? How come we don't need to specify the name of the schema.ini file in the query statement. Could we call the ini file with different name or it has to be named schema.ini? Please for letting us know.
Post #1082900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse