<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Articles
Editorials
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
T-SQL (SS2K5)
»
Using OPENROWSET to import CSV files
22 posts, Page 2 of 3
««
1
2
3
»»
Using OPENROWSET to import CSV files
Rate Topic
Display Mode
Topic Options
Author
Message
Simon Parry
Simon Parry
Posted Friday, November 06, 2009 2:23 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, November 11, 2009 1:49 AM
Points: 13,
Visits: 27
Hi
i have tried using BULK INSERT
using
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
exec (@string)
but this requires the destintation table to have the same number of columns as the import file
The files i want to import have an unknown number of coulmns
Can you help at all?
Simon
Post #814762
GSquared
GSquared
Posted Friday, November 06, 2009 6:39 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:36 PM
Points: 7,848,
Visits: 4,296
Simon Parry (11/6/2009)
Hi
yes im using a 64bit sever
im having trouble locating to odbc drivers for this and can only find 32bit
can you point me in the right direction
thanks
simon
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #814891
Simon Parry
Simon Parry
Posted Friday, November 06, 2009 10:52 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, November 11, 2009 1:49 AM
Points: 13,
Visits: 27
Hi thanks for that
unfortunately i still get the same error
sorry if im missing something here
can you help?
thank you
simon
Post #815082
GSquared
GSquared
Posted Friday, November 06, 2009 11:14 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:36 PM
Points: 7,848,
Visits: 4,296
If you have the driver correctly installed, then the error is most likey to be from not having the correct filename and/or path in the openrowset command.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #815098
Jeff Moden
Jeff Moden
Posted Friday, November 06, 2009 9:18 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 18,349,
Visits: 12,359
Simon Parry (11/6/2009)
Hi
i have tried using BULK INSERT
using
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
exec (@string)
but this requires the destintation table to have the same number of columns as the import file
The files i want to import have an unknown number of coulmns
Can you help at all?
Simon
Yep... attach a copy of one of the files (unless it has private info in it) to your next post and tell me what you think you'd like to do with it.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers, 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/
Post #815296
Paul White
Paul White
Posted Friday, November 06, 2009 9:51 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 2,086,
Visits: 2,075
Please also consider using an SSIS package to import data from files to a database - that is its primary purpose in life, and I don't like to see a good tool get upset.
The quality of the answers is directly proportional to the quality of the question.
Post #815309
GSquared
GSquared
Posted Tuesday, November 10, 2009 6:39 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:36 PM
Points: 7,848,
Visits: 4,296
Paul White (11/6/2009)
Please also consider using an SSIS package to import data from files to a database - that is its primary purpose in life, and I don't like to see a good tool get upset.
SSIS requires a fixed number of columns in the import definition.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #816443
Paul White
Paul White
Posted Tuesday, November 10, 2009 3:33 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 2,086,
Visits: 2,075
GSquared (11/10/2009)
SSIS requires a fixed number of columns in the import definition.
Well it certainly prefers to deal with stable metadata, that's for sure. But, unless the requirement is actually to import a file with truly 'any structure' into an unspecified table, there's often a way around it.
Most frequently I've come across this as different files being subsets of a larger structure, or something like that. If there's some structure or logical to work with, it's often possible to do.
The quality of the answers is directly proportional to the quality of the question.
Post #816889
Jeff Moden
Jeff Moden
Posted Tuesday, November 10, 2009 3:53 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 18,349,
Visits: 12,359
Jeff Moden (11/6/2009)
Simon Parry (11/6/2009)
Hi
i have tried using BULK INSERT
using
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
exec (@string)
but this requires the destintation table to have the same number of columns as the import file
The files i want to import have an unknown number of coulmns
Can you help at all?
Simon
Yep... attach a copy of one of the files (unless it has private info in it) to your next post and tell me what you think you'd like to do with it.
Simon?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers, 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/
Post #816903
Simon Parry
Simon Parry
Posted Tuesday, November 10, 2009 7:35 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, November 11, 2009 1:49 AM
Points: 13,
Visits: 27
Hi
Sorry for the delay
I have attached 3 sample files, its possible each one of these may be used
If the files were the same size each time i could do it, but because they have a varying number of columns im stuck
Many thanks for all your help
Simon
Post Attachments
sample3.zip
(
3 views,
733 bytes
)
Post #816944
« Prev Topic
|
Next Topic »
22 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2009 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
