is a JOIN between text file and table possible 2

[EchoAlertcom]

Hello,

Is it possible to use a text file in a JOIN with a table as I would between two tables?

For example if I have:

1. a text file c:\cust_phone.txt that contains 1 phone number per line.

2. a table that has a phone number column.

what we're using is something like this currently:

I import a specific list of phone numbers into dbo.suppression.Phone with a ListID that will show all these phone numbers as being apart of the same list.

select *
From dbo.Customers
LEFT OUTER JOIN dbo.ExCustomers
ON dbo.Customers.Phone = dbo.Suppression.Phone
and dbo.Suppression.ListID = 5
WHERE dbo.suppression.Phone IS NULL

My goal would be for me to not to have to import this list anymore into sql server. and have the users put the list in a directory on the server and use that in the query.

Thank you for the help.

Regards,
Steve

 

[gmmastros]

Yes, it's possible.

Take a look at this thread where an excel spreadsheet was used. thread183-1290354

Also take a look at

http://www.connectionstrings.com

because instead of Excel, you want to use a 'text' driver.

Give this a shot. If you run in to any problems, post back here with followup questions.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EchoAlertcom]

Great!!! Thank you. I will go through those now.

 

[EchoAlertcom]

Hello,

Here is how far I've been able to come. But I am getting an error that I can't figure out where to go from.

Also, since the text files will always only have 1 column. How would I change the code to reflect that there isn't a header? I know I have to change HDR=Yes to HDR=No but in the ON dbo.Addresses2.Phone = OpenDataSource.Phone how do i change the fact that there isn't a column name for the text file?

SELECT *
FROM dbo.Customers
LEFT OUTER JOIN OpenDataSource('Provider=Microsoft.Jet.OLEDB.4.0','Date Source="c:\supplists\list1.txt";Extended Properties="text;HDR=Yes;FMT=Delimited"')
ON dbo.Addresses2.Phone = OpenDataSource.Phone
WHERE dbo.suppression.Phone IS NULL

Here is the error I'm getting:


Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'ON'.


Thank you for your help.

Steve

 

[AlexCuse]

I haven't worked with this feature before, but shouldn't

'Date Source

read

'Data Source

Also, I think you could refer to your column in the text file by number (if it's 20 then [Col020] to make up for the fact that there isn't a header/column name.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gmmastros]

As an alternative... You could create a temp table, load the data from the file, and then use the temp table.

Create Table #Temp(Phone VarChar(50))

Bulk Insert #Temp From 'c:\supplists\list1.txt'

SELECT *
FROM   dbo.Customers
       LEFT OUTER JOIN #Temp
         ON dbo.Customers.Phone = #Temp.Phone

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EchoAlertcom]

Thank you for catching the typo. Yes it should be Data Source. The typo was when i typed it into tek-tips. In query analyzer I have it spelled correctly. So the fact that it doesn't run is still an open issue. But good eye .

Once I have the error fixed and the query runs I will give the Col001 a try. thank you.

Regards,
Steve

 

[360degreehosting]

Hi George. This is Steve. My EchoAlertcom handle isn't working all of a sudden. I've emailed them but I need to keep convervasation going in the mean time.

I like the first approach better for how we're doing things. So if we can stay with that way I would be greatful.

I am getting that same error. Do you have any suggestions?

I would think a connection string for a text file would be easier than Excel. LOL, but i am stuck in trying to figure out what is wrong.

Warmest Regards,
Steve

 

[gmmastros]

Have you tried the Bulk Insert method?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[360degreehosting]

No, I haven't tried. My concern is that they run this in a 3rd party program. They are given a textbox to put their query. I'm not sure how that application will deal with something that is not a select statement.

I am pretty focused on getting it working this way to get people off my back. I can then take time to experiment with the bulk insert.

I am usually eager for trying different suggestions to find the best. But I'm under pressure on this one.

Regards,
Steve

 

[gmmastros]

I learned a little since my previous post.

First, I recommend you create a schema.ini file. Use notepad to do this and place the file in to the same folder as your data file. For your case, the schema.ini file should look like this...

[list1.txt]
ColNameHeader=False
CharacterSet=1252
Format=CSVDelimited
Col1=PhoneNumber Text

When opening the data source, SQL Server will use the schema.ini file to get additional information. In this case, ColNameHeader=False and Col1=PhoneNumber will indicate that there is no header row and that the first column should be named 'PhoneNumber'.

Then, your query would be...

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\supplists\";Extended Properties="text"')...[list1#txt]

If you want to join other tables to this...

SELECT A.PhoneNumber
FROM   OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\supplists\";Extended Properties="text"')...[list1#txt] As A
       Inner Join OtherTable
         On A.PhoneNumber = OtherTable.PhoneNumber

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[360degreehosting]

Ah YES!!!!

That worked like a charm!!!!!

George, Thank you very much for your help through this. I appreciate it very much.

Warmest Regards,
Steve

 

[gmmastros]

No problem. I learned something too.

Just curious... why the 'name' change? You started off as EchoAlertcom and then changed to 360degreehosting.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[360degreehosting]

I was in the middle of responding to your question about the alternative method and when I clicked Submit Post the page redirected to an tek-tips administrative page saying there are issues with my account or something. I've been using tek-tips for 4-5 years without any issue so I'm not sure what happened. I've emailed them and asked what happened. But in the meantime I had to keep the dialog going. I did notice (for me anyway) earlier today tek-tips web site was going up and down. That may have had something to do with it.

Thanks for your help. I'm glad you learned something too. That's why I love this business - there is always something new to learn or figure out.

Regards,
Steve