csv file or array to compare against other tables

[03Explorer]

I have to take a list of over a few thousand values, which I converted into comma delimited with single quotes saved as a text file, and compare against other tables...

I have SQL server 2000 and 2005 tools installed on my XP workstation.

I tried OpenRowSet, but consitantly get an error message... 'The OLE DB provider "MSDASQL" has not been registered.' which all web sites comment about the error being a dll or driver for 64 bit... my pc is only 32 bit.

My last resort is to figure out Array and get the data into an array to use a loop statement with INSERT INTO in the middle per item.

 

[gmmastros]

Where is the database that you are trying to import in to? Is that local too?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[03Explorer]

The database is on the network, not local.

The data I have in the CSV is to populate only one column, that seems to be my issue I am running into. That and the fact that I can not use DTS to make a table on this server other than a temporary.

 

[gmmastros]

Is the server that you are attempting to import in to on a 64 bit processor?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[03Explorer]

Here is the code for the OpenRowSet that I am trying to use

select a.* from OpenRowSet('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\Documents and Settings\rwagn12\Desktop\Sandbox; ','select * from Book1b.txt') AS a

 

[03Explorer]

Not sure if the server is 64 bit... should that matter?

 

[pontupo]

Here's a user defined function that I created. Assuming you have permission to create a user defined function, you should be able to add this to your database and then use it like so:

select * from dbo.create_table_from_array('your list here','your delimiter here')

CREATE FUNCTION create_table_from_array (@array ntext, @delimiter nvarchar(10))
	RETURNS @tbl TABLE(expr1 nvarchar(4000) NOT NULL) --return table of nvarchars
	AS 
BEGIN
DECLARE @pos int, --position of the next comma
	@textpos int, --position in the incoming data set
	@chunklen smallint, --end point of the chunk to take from the main feed
	@internallist nvarchar(4000), --just a working copy of the passed list
	@str nvarchar(4000), --current zip we're working with
	@leftover nvarchar(4000); --text rolling over from each inside loop


SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= (datalength(@array) / 2)
BEGIN
	SET @chunklen = 4000 - (datalength(@leftover) / 2) --set the end point of the substring
	SET @internallist = @leftover + substring(@array, @textpos, @chunklen) --get a piece of the full feed
	SET @textpos = @textpos + @chunklen	

	SET @pos = charindex(@delimiter,@internallist) -- find the first @delimeter
	WHILE @pos > 0 -- (until there are no more @delimeters)
	BEGIN
		SET @str = substring(@internallist,1,@pos-1) -- snag the string up to the next @delimeter
		INSERT INTO @tbl VALUES(cast(@str AS nvarchar(4000))) -- insert that string, cast as nvarchar
		SET @internallist = substring(@internallist,@pos+len(@delimiter),len(@internallist)) -- cut off the used zip
		SET @pos = charindex(@delimiter,@internallist) --find the next @delimeter
	END
	
	SET @leftover = @internallist --save off the remainder for the next time around
END

--get any leftover zip for insertion
SET @str = @internallist
INSERT INTO @tbl VALUES(@str) --insert it and cast as an nvarchar

RETURN
END

Sorry, all of the commenting is assuming that it's a list of zip codes, which was what I originally created it for. It will work, however, for any list of values and any delimiter. You may need to adjust it for you particular application, depending on the kind of values you are trying to feed in. Hopefully this (or the general idea) helps. I've found this function invaluable, incidentally, because I run into situations like yours all the time: someone gives me an excel file or something to run a query with.

 

[gmmastros]

Well.... yeah.

You see... it doesn't matter what computer you run your command from.

Think of it this way.... When you use sql tools (Enterprise Manager, Query Analyzer, SQL Server Management Studio, etc...) you may be running those applications on your computer, but all those applications do is send your query to the 'real' server and execute them there. Of course, these tools also show you the results of your query, too.

So... when you do stuff like...

C:\Folder\File.txt

That 'string' is sent to the server and executed there. If it cannot find the file (relative to the server) it will display an error message.

Does this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[03Explorer]

I can't create a function or SP in the database...

 

[gmmastros]

Can you copy files to the server? Also... can you post the first couple rows of the data file here (protecting sensitive data of course, no names, addresses, etc...)?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[03Explorer]

Data looks like this in the csv or txt file (keep in mind there is aprox 5600 data points...

'8307227', '10551288', '10559183', '10562806', '10568514'

(no header, just data in the file)

 

[gmmastros]

So... there is just 1 row with comma's separating the data?

If you can, I would recommend that you...

1. re-format the data
2. copy the newly formatted file to the server
3. import the data using 'Bulk Insert'

1. I recommend you open the file in something like word that allows you to replace comma with CR-LF. Also replace apostrophes with nothing (empty string).

Ultimately, the file should look like this...

[tt][blue]8307227
10551288
10559183
10562806
10568514
[/blue][/tt]

2. Copy the file to the server, in a folder of your choosing.

3. Run this script on the server (from your client workstation).

Create Table #Temp (data int)

Bulk Insert #Temp From '[!]C:\tt.txt[/!]' 

Select * From #Temp

Drop Table #Temp

When I tested this code, I named the file C:\tt.txt. You will, of course, need to modify this code to suit your file name. Most importantly though, the file MUST exist on the server, or else this will not work.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[03Explorer]

SNAP! I can't use Bulk Load either!

I am going to get the DBA involved and either adjust rights or something!

Was hoping to get the OpenRowSet fuction to work... seems like the option that would populate the temp table from the txt or CSV file.

 

[03Explorer]

Are you referring the script be run outside of 2005 server studio or 2000 Query Analyzer? Because the file location should be able to be mapped or local to the machine running the code.

Just a thought.

 

[03Explorer]

This is a question that might shed some light on the OpenRowSet issue... I even get the OLE DB provider "something" has not been registered when trying to pull data from Excel file.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1c.xls', [Sheet1$])

With this for error:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.


This makes no sense!!