Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Xerxes
Aged Yak Warrior
666 Posts |
 Posted - 2010-08-27 : 13:04:03
|
I'm attempting to import a large csv file using OPENROWSETDROP TABLE Tickets_INPUTFILESELECT * INTO Tickets_INPUTFILEFROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver(*.txt;*.csv)};DefaultDir=E:\Source;Extended properties="ColNameHeader=True;Format=( );"','SELECT * FROM cmstics_lw08222010.csv')GO Unfortunately, when I do, I get all my data in a single column.What am I missing?Thanks!Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL & VB obviously! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-27 : 15:32:07
|
What is the FORMAT() directive?What column delimeter has the file?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2010-08-27 : 15:34:12
|
| The format should have read: Format (|) to indicate a pipe.There are 13 columns, but they all end up being included in a single column, much like if you did an import by hand into excel.Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL & VB obviously! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-27 : 16:05:55
|
I read somewhere that the column delimeter cannot be set by the connection string.You have to set it in the Registry. See http://connectionstrings.com/textfileOtherwise try "Format=Delimited(|)"
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2010-08-27 : 17:53:41
|
| This doesn't help. It doesn't explain WHY.Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL & VB obviously! |
|
|
|
|
|
|
|
|
Openrowset woes.