-- Open a text file using OPENROWSET doesn't allow No header
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\SQL\;',
'SELECT * FROM [My Listing 1.txt];'
)
-- Open a text file using OPENDATASOURCE does. See schema.ini
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\SQL\;Extended Properties="Text;HDR=No;FMT=Delimited"')...[My Listing 1#txt]
-- Try this quoted text comma delimiter example:
-- Run this in DOS
bcp "SELECT [OrderID] AS [OrderID], char(34) + [CustomerID]+ char(34) AS [CustomerID], [EmployeeID] AS [EmployeeID], [OrderDate] AS [OrderDate], [RequiredDate] AS [RequiredDate], [ShippedDate] AS [ShippedDate], [ShipVia] AS [ShipVia], [Freight] AS [Freight], char(34) + [ShipName]+ char(34) AS [ShipName], char(34) + [ShipAddress]+ char(34) AS [ShipAddress], char(34) + [ShipCity]+ char(34) AS [ShipCity], char(34) + [ShipRegion]+ char(34) AS [ShipRegion], char(34) + [ShipPostalCode]+ char(34) AS [ShipPostalCode], char(34) + [ShipCountry]+ char(34) AS [ShipCountry] FROM Northwind.dbo.Orders"queryout "E:\MSSQL\REPORTS\Orders20081106.txt"
-SSQLDEV -T -t "," -c
--
-- Check that the results has the Comma delimited Quoted text that you want.
--
-- Now query this in Query Analyzer to aid in importing it into SQL.
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\MSSQL\REPORTS\;Extended Properties="Text;HDR=No;FMT=Delimited"')...[Orders20081106#txt]
NOTE: REPLACE the .txt WITH #txt AS per OPENDATASOURCE usage.