Join Two CSV Files with a Common Column

Question

Join Two CSV Files with a Common Column

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

gorthog Ten Centuries Points: 1084 More actions · Answer 1

corey lawson - Tuesday, January 24, 2017 11:03 PM
Jeff Moden - Tuesday, January 24, 2017 10:32 PM
Just a note to anyone that may be watching... I think it's a mortal sin to install MS Office on the same "box" that SQL Server lives on.
I agree with you Mr. Moden. But it at least gets done on (to?) SSIS servers to help deal with Excel files, especially with old .xls (XBIFF) files. There are at least two libraries that can directly read and write Excel files, though. (EPPlus.dll and I forget the other one)... It may be worth spending some time writing a Custom Excel component or two for SSIS that uses either of them... or just automate the process through Powershell like I did if your company won't spring for CozyRoc's components (SSIS calls powershell to run the script...)

Is there any prospect of Microsoft providing a 64-bit version of the Microsoft Text Driver?

Mike Giuffre Old Hand Points: 334 More actions · Answer 2

I like this article and the potential. But can you explain the three dots and pound symbol in the following query?

SELECT s.ProductID,Productname,InvoiceNumber FROM csvlinkedserver...sales#csv sINNER JOIN csvlinkedserver...products#csv pONs.ProductID=p.ProductID

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

corey lawson - Tuesday, January 24, 2017 11:03 PM
Jeff Moden - Tuesday, January 24, 2017 10:32 PM
Just a note to anyone that may be watching... I think it's a mortal sin to install MS Office on the same "box" that SQL Server lives on.
I agree with you Mr. Moden. But it at least gets done on (to?) SSIS servers to help deal with Excel files, especially with old .xls (XBIFF) files. There are at least two libraries that can directly read and write Excel files, though. (EPPlus.dll and I forget the other one)... It may be worth spending some time writing a Custom Excel component or two for SSIS that uses either of them... or just automate the process through Powershell like I did if your company won't spring for CozyRoc's components (SSIS calls powershell to run the script...)

It would be interesting to see how people might use SSIS to import the types of Excel files that I've had to deal with (never mind writing to them ;)). Same goes for PS. Using the ACE drivers, I've not had a problem with reading .xls files.

As a bit of a sidebar, I also prefer not to have SSIS on the same box as the databases for similar reasons as Office.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corey lawson Hall of Fame Points: 3773 More actions · Answer 4

gorthog - Wednesday, January 25, 2017 9:30 AM
corey lawson - Tuesday, January 24, 2017 11:03 PM
Jeff Moden - Tuesday, January 24, 2017 10:32 PM
Just a note to anyone that may be watching... I think it's a mortal sin to install MS Office on the same "box" that SQL Server lives on.
I agree with you Mr. Moden. But it at least gets done on (to?) SSIS servers to help deal with Excel files, especially with old .xls (XBIFF) files. There are at least two libraries that can directly read and write Excel files, though. (EPPlus.dll and I forget the other one)... It may be worth spending some time writing a Custom Excel component or two for SSIS that uses either of them... or just automate the process through Powershell like I did if your company won't spring for CozyRoc's components (SSIS calls powershell to run the script...)
Is there any prospect of Microsoft providing a 64-bit version of the Microsoft Text Driver?

Yes, the ACE engine comes in 32-bit and 64-bit flavors.

corey lawson Hall of Fame Points: 3773 More actions · Answer 5

Mike Giuffre - Wednesday, January 25, 2017 1:10 PM
I like this article and the potential. But can you explain the three dots and pound symbol in the following query?
SELECT s.ProductID,Productname,InvoiceNumber FROM csvlinkedserver...sales#csv sINNER JOIN csvlinkedserver...products#csv pONs.ProductID=p.ProductID

That's how you do "fully qualified paths" to tables & views on linked servers.
<linked server name>.<database>.<schema>.<dataset>

Mark Dalley SSCrazy Points: 2981 More actions · Answer 6

corey lawson - Wednesday, January 25, 2017 6:17 PM
Mike Giuffre - Wednesday, January 25, 2017 1:10 PM
I like this article and the potential. But can you explain the three dots and pound symbol in the following query?
SELECT s.ProductID,Productname,InvoiceNumber FROM csvlinkedserver...sales#csv sINNER JOIN csvlinkedserver...products#csv pONs.ProductID=p.ProductID
That's how you do "fully qualified paths" to tables & views on linked servers.
<linked server name>.<database>.<schema>.<dataset>

The pound symbol (#) represents a dot, i.e. sales.csv in this case. This particular syntax is a rather horrible idiosyncrasy of the text driver that has seems to have been around a long time. Maybe someone just got tired of using lots of dots and decided to use something else!

MarkD

David Burrows SSC Guru Points: 65143 More actions · Answer 7

Jeff Moden - Wednesday, January 25, 2017 4:38 PM
As a bit of a sidebar, I also prefer not to have SSIS on the same box as the databases for similar reasons as Office.

How would/do you schedule and run the packages?

Far away is close at hand in the images of elsewhere.
Anon.