How to import multiple CSV files.

Question

How to import multiple CSV files.

Viewing 14 posts - 31 through 43 (of 43 total)

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

Heh.... I actually take exception to anyone calling any import method other than SSIS a "band aid solution". Let me ask... can you execute two instances of the same SSIS package at the same time for parallel loading to save even more time? 😉

--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)

dji Ten Centuries Points: 1229 More actions · Answer 2

Apologies, Jeff!

I was most definitely referring to my solution of trying to squeeze the out dated OPENROWSET into the 64 bit environment when mentioning band-aid 🙂

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

dji (8/18/2011)
Apologies, Jeff!
I was most definitely referring to my solution of trying to squeeze the out dated OPENROWSET into the 64 bit environment when mentioning band-aid 🙂

Understood and thank you for getting back to me. 🙂

If I understand correctly, though, most folks I know say that SSIS won't import "text qualified" files (files with double quotes in them if a delimiter, such as a comma, is present in a given field of the file. It's also my understanding that the new "ACE" (a "replacement for the JET drivers) will and that it works just fine using OPENROWSET. Knowing that, I'm not quite ready to call OPENROWSET or similar technologies "outdated". 😉

--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)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Jeff Moden (8/18/2011)

dji (8/18/2011)
If I understand correctly, though, most folks I know say that SSIS won't import "text qualified" files (files with double quotes in them if a delimiter, such as a comma, is present in a given field of the file.

You are correct. If you have the same character in a column as the delimiter the package crashes.

You have to use an ANSI character that a user would never enter.

SSIS is a good ETL tool but if there is an alternative that takes less time to develop or executes faster then it is a better solution.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

Welsh Corgi (8/18/2011)

Jeff Moden (8/18/2011)
dji (8/18/2011)
If I understand correctly, though, most folks I know say that SSIS won't import "text qualified" files (files with double quotes in them if a delimiter, such as a comma, is present in a given field of the file.
You are correct. If you have the same character in a column as the delimiter the package crashes.
You have to use an ANSI character that a user would never enter.
SSIS is a good ETL tool but if there is an alternative that takes less time to develop or executes faster then it is a better solution.

Thank you for the confirmation. Not being one to use SSIS, I wasn't 100% sure.

--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)

dji Ten Centuries Points: 1229 More actions · Answer 6

Jeff Moden (8/18/2011)
dji (8/18/2011)
Apologies, Jeff!
I was most definitely referring to my solution of trying to squeeze the out dated OPENROWSET into the 64 bit environment when mentioning band-aid 🙂
Understood and thank you for getting back to me. 🙂
If I understand correctly, though, most folks I know say that SSIS won't import "text qualified" files (files with double quotes in them if a delimiter, such as a comma, is present in a given field of the file. It's also my understanding that the new "ACE" (a "replacement for the JET drivers) will and that it works just fine using OPENROWSET. Knowing that, I'm not quite ready to call OPENROWSET or similar technologies "outdated". 😉

This would be a BIG problem for me, fortunately the data is not quite so bad that my particular text qualifier " is contained in any of the text fields.

I was unaware of any 'working' replacement for the JET drivers, I have read of developers who were able to get something working with OPENROWSET but I never managed to get it working despite downloading and intsalling everything I read on various 'work-arounds' posted on different forums. The SSIS solution I have for looping through a directory is very simple and many times quicker than using openrowset from within a vb.net programme to achieve the same results. At my time of desperation it seemed as though the rug had been well and truly pulled from under the feet of anyone using OPENROWSET, and now I have discovered SSIS I would hate to go back to the dark days when MS simply decide on a whim to stop supporting a particular technology leaving many people high and dry, hopefully this won't happen anytime soon with SSIS.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

dji (8/18/2011)
Jeff Moden (8/18/2011)
dji (8/18/2011)
Apologies, Jeff!
I was most definitely referring to my solution of trying to squeeze the out dated OPENROWSET into the 64 bit environment when mentioning band-aid 🙂
Understood and thank you for getting back to me. 🙂
If I understand correctly, though, most folks I know say that SSIS won't import "text qualified" files (files with double quotes in them if a delimiter, such as a comma, is present in a given field of the file. It's also my understanding that the new "ACE" (a "replacement for the JET drivers) will and that it works just fine using OPENROWSET. Knowing that, I'm not quite ready to call OPENROWSET or similar technologies "outdated". 😉
This would be a BIG problem for me, fortunately the data is not quite so bad that my particular text qualifier " is contained in any of the text fields.

I use SSIS extensively but it's not made of magic pixie dust as some have touted. It is perfect from far, but far from perfect 😀

SSIS actually makes use of OPENROWSET under the covers in some scenarios too. I am still not sure why the dev team could not get CSV support "right". IMHO it's borderline unforgivable and I have seen it be a barrier to adoption.

At any rate, I am happy you found a working solution, and a new toy it seems 🙂 All my gripes aside, SSIS is definitely worth the effort to get to know. Good luck!

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 8

dji (8/18/2011)
... and many times quicker than using openrowset from within a vb.net programme to achieve the same results

Heh... just about anything is faster than that. Why on Earth would you even get vb.net involved with OPENROWSET? Certainly not to get a simple list of files... 😛

EXEC master.dbo.xp_DirTree 'C:\Windows', 1, 1

--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)

dji Ten Centuries Points: 1229 More actions · Answer 9

Jeff Moden (8/19/2011)
dji (8/18/2011)
... and many times quicker than using openrowset from within a vb.net programme to achieve the same results
Heh... just about anything is faster than that. Why on Earth would you even get vb.net involved with OPENROWSET? Certainly not to get a simple list of files... 😛
EXEC master.dbo.xp_DirTree 'C:\Windows', 1, 1

oh:blush:

Is there a big overhead using vb.net --> SQL?

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 10

Sorry... lost track of this post.

Maybe, maybe not. "It Depends" on how the VB is written. But, personally, I'd rather just use the xp_DirTree thing because if I can keep things in T-SQL, I don't need to worry about where source code is stored. 🙂

--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)

Aiello DBA Right there with Babe Points: 784 More actions · Answer 11

SSIS have no problem with large files. I use it to import cvs files about 7G every day (6 millions rows) and have no problem.

-

Lic. Andrés M. Aiello

DBA MSSQL - Oracle

http://aiellodba.blogspot.com/

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 12

Aiello DBA (8/25/2011)
SSIS have no problem with large files. I use it to import cvs files about 7G every day (6 millions rows) and have no problem.
-
Lic. Andrés M. Aiello
DBA MSSQL - Oracle
http://aiellodba.blogspot.com/

How long did the 6 million rows take?

--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)

Aiello DBA Right there with Babe Points: 784 More actions · Answer 13

About 5 and a half hours. Was a firewall log monitoring all pcs information about internet.

-

Lic. Andrés M. Aiello

DBA MSSQL - Oracle

http://aiellodba.blogspot.com/

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 14

Any idea how long it would take without the firewall monitoring? The reason I ask is that I've seen BULK INSERT import 5.1 million, 20 column rows from a CSV file in a minute flat.

--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)