Flat file return NULL value

Hi,
I am facing problem while extracting data from flat file through query.

It return NULL value for the column EmpID for the 3rd row ('x')

i have a flat file for eg:

EmpID, EmpName, ParentEmpID
1,test1,10
2,test2,13
x,text3,15

Below mentioned two type of query I tried

1)

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','select * from test.txt')

2)

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\;Extended Properties="text;FMT=Delimited"')...test#txt

Pls help me out this issue

If you don't specify a schema.ini file providing mapping of fields and data types then the provider will try to determine the data type based on sampling the data file. Since the first column of two rows have numeric values the provider will treat that column as numeric. This might result in the 3rd row having NULL value in first column because 'x' is invalid. Check out the provider documentation for more details and you can also learn about schema.ini file. Using a schema.ini file you should be able to specify the first column as character or string.|||

Thanks for your reply

my requirement is that from a perticular path i have to read the flat files and insert into a temporary table. In the path contain more than one file and file have no predefined format or order.From the temp table i have to do some process. so i have to read the file as it is.

so through schema.ini is it possible or can you suggest me how to do the same

Thanks
Sunil

|||Man,
If the file don't have predefined format or order, give a dummy order in the ini file & you can have different sections in that, which specifies the which sections belongs to which flat file name.
as in my previous reply, now you may have your .ini file as
"SCHEMA.INI"

---

[test.txt]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=ANSI
Col1=EmpID Char Width 20
Col2=EmpName Char Width 20
col3=ParentEmpID Char Width 20

[test1.txt]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=ANSI
Col1=EmpID1 Char Width 20
Col2=EmpName1 Char Width 20
col3=ParentEmpID1 Char Width 20

[test2.txt]

ColNameHeader=True

Format=CSVDelimited

CharacterSet=ANSI

Col1=EmpID2 Char Width 20

Col2=EmpName2 Char Width 20

col3=ParentEmpID2 Char Width 20

---

this file contains the example of three flat file named test.txt , text1.txt. &text2.txt.

Hope this may help you little.

Gurpreet S. Gill|||

Hi Gurpreet,

thanks a lot.
my situation is like i don't know the colname (because flat file is generating daily), in that situation how can we include Col1="colname" Char width 20?
or any other way to do the same

Pls help me..

|||Hi SUNIL
if you do not know the ColName then just follow this

---

[test.txt]
ColNameHeader=false
Format=CSVDelimited
CharacterSet=ANSI

---

This will make the column name automatically (in my case it is F1, F2 & F3, may be different in your case, may be you need to set the first line as blank in the flat file).

But please note that if you use the this ini file, it will again leads to the problem of NULL, so some where, i think, you need to make compromise(either the null or make the column name with width).
I will again suggest you to count the number of columns & have the dummy name & set the width for each.
Read more at
http://www.devx.com/tips/Tip/12566
to create quick Schema.ini file.

Gurpreet S. Gill