Openrowset .CSV File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Openrowset .CSV File

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-03-07 : 01:58:41

any reason why this wont work?

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

i get the following error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

i'm basically just trying to use open rowset to select from a .csv file.

thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-07 : 07:13:05

I have tested it and it worked.
Then I have renamed the file.
Result of my test:
The error message comes up if the C:\order777.csv not exists.

No, you're never too old to Yak'n'Roll if you're too young to die.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-07 : 10:57:14

I have only ever seen a bunch of mumbojumbo about geting this to work properly when it doesn't. One person restarts the SQL service and it works, another person has to sacrifice a chicken under a full moon, a third person gets it working by doing something entirely different.

Depending on what you are trying to do with this data in the CSV file, PowerShell might be an easier alternative. I had a bulk list of partnumbers I had to deactivate a few weeks ago and I used PowerShell to read the CSV file and issue the query to SQL Server to make the change. It worked very well and was quite easy. Plus, since the advent of SQL Server 2008, PowerShell is always good to know anyway.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-03-07 : 14:14:30

that's sweet news... i dont know a bit of powershell. i was using openrowset because i wanted to only pull certain columns... could it be something with the driver?

i also tried these attempts w/ the following errors.


SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO',
'SELECT * FROM order777.csv')
go

--ERROR--
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

also tried:


SELECT *
FROM OPENROWSET('MSDASQL.1',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;', 'SELECT * FROM order777.csv')
go

--ERROR--
OLE DB provider "MSDASQL.1" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL.1" for linked server "(null)".

any ideas on what i can do? is there a way i can check to see if MSDASQL.1 even exists

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-03-07 : 14:49:23

side note, this is server 2008 r2, and i only see SQL Server and SQL Server Native Client 10 in the ODBC Drivers list.

if i go into the ODBC 32bit list, then i see them all.. could that have something to do with it?

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-07 : 21:06:08

Here is the script I used. In PowerShell you can import a CSV into an array. The array is actually like a hash or an array of objects. So say the CSV looks like this:
PartNo, Revision, Description
1234, A, Some part or another
1235, -, Another part
You import the CSV like this: $myInfo = Import-Csv C:\path\information.csv
Then you would refer to each column as $myInfo[0].PartNo (this would be 1234), $myInfo[0].Revision (this would be A)

#Example script.

$partnos = Import-Csv C:\m2mscipts\obsolete.csv

$SqlServer = "sqlserver"
$SqlCatalog = "m2mdata02"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

foreach($item in $partnos)
{
$Sqlcmd.CommandText ="UPDATE [INMAST] SET [INMAST].fcstscode = 'O' WHERE [INMAST].fpartno = '" + $item.Part_No + "' AND [INMAST].frev = '" $item.Rev "'";
$Sqlcmd.ExecuteNonQuery()
}
$SqlConnection.Close()

GunnyL
Starting Member

1 Post

Posted - 2011-06-21 : 15:22:23

Robert,

Did you run your script in PowerShell or PowerShell ISE? Below is the script I am trying to run. I need to figure out how to reformat it for PowerShell. Any Help would be most appreciated. Trying to Educate myself as fast as possible with PowerShell.

select * into #tempRN FROM
OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\;', 'SELECT * from reorder_update.csv');

update SupplierList
set ReorderNumber = X.ReorderNumber
from SupplierList SL
INNER JOIN Item I on I.ID = SL.ItemID
INNER JOIN #TempRN X on X.ItemLookupCode = I.ItemLookupCode
DROP Table #tempRN
My Excel Sheet has the following columns
'ItemLookupCode', 'ReorderNumber'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources