Controlling column order when reading text file based on schema.ini and OPENDATASOURCE

Question

Controlling column order when reading text file based on schema.ini and OPENDATASOURCE

steve lord

SSC Veteran

Points: 258
More actions
January 11, 2008 at 12:16 pm

#182674

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

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

steve lord SSC Veteran Points: 258 More actions · Answer 2

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

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

lynndang22 SSC Rookie Points: 27 More actions · Answer 4

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.