SQL 2005 retrieves data from a file

Question

I have data in a simple txt file, trying to query it (put it in a table), but when I use

select * from OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\DB\;Extensions=TXT;', 'select * from data.txt '); 

I get strings, but all of them are NULL, because (I think) there is no field terminator and is in UTF-16LE format, in TXT all fields are known by the icon, for example:

 FIELD1FIELD2FIELD3FIELD4FIELD5FIELD6 FIELD1FIELD2FIELD3FIELD4FIELD5FIELD6 FIELD1FIELD2FIELD3FIELD4FIELD5FIELD6 FIELD1FIELD2FIELD3FIELD4FIELD5FIELD6 

To make things worse, the file is in UTF-16LE , but I need it in UTF-8 (or change the format in sql 2005) so openrowset works

And when I use

 SELECT * FROM OPENROWSET ( BULK 'C:\DB\data.txt', SINGLE_NCLOB) AS BinFile 

I get all the data, but on one line :(

Thank you in advance

ps: also used sp_addlinkedserver and did not work

Answer 1

Thank you guys for your time and brains to try to crack this problem, finally decided to solve it:

 SELECT SUBSTRING(F1, 1, 19) col 1 ,SUBSTRING(F1, 20, 19) col 2 FROM OpenRowset(''Microsoft.Jet.OLEDB.4.0'', ''Text;HDR=NO;CharacterSet=unicode;Database=' +@path +'; Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" '',JOURNEY_PLAN#txt); 

SUBSTRING to get data from a file that is not limited by anything

CharacterSet = unicode to enable the database engine to read from a specific character set of a file

Thanks!!!!!!!!!

ps: In http://msdn.microsoft.com/en-us/library/bb177651.aspx information about the attribute "CharacterSet" is found, the bad thing is that the manual for OPENROWSET dosent says a thing about it http://msdn.microsoft .com / en-us / library / ms190312.aspx >: (

Answer 2

Have you tried using BULK INSERT for this?

 BULK INSERT dbo.myTable FROM 'C:\temp\MyFile.txt' WITH ( FIELDTERMINATOR = ',') 

MSDN Bulk Upload Syntax

Answer 3

Make sure the contents of data.txt are in the correct format . If it is not formatted correctly, then NULL values ​​will be returned if they cannot be interpreted. For example, if the data file looks like this:

 f1,f2,f3 1,x,3 a,b,c 

The driver then interprets the first and third fields as some numeric value and then returns NULL for the fields f1 and f3 in the second row of data.

Change For the data in this example, make sure that there is something like the following in the schema.ini file:

 [data.txt] Format=FixedLength ColNameHeader=False Col1=Field1 Text Width 6 Col2=Field2 Text Width 6 Col3=Field3 Text Width 6 Col4=Field4 Text Width 6 Col5=Field5 Text Width 6 Col6=Field6 Text Width 6