| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2011-04-06 : 15:14:42
|
| okay, very basic question, but with a fun curve ball.how can i import a .csv file into a NEW MSSQL Table. now here's the trick. the table and csv files cannot be a set format. so i need SSIS or something else inbetween to dynamically create a table, based off the header of the file. make sense? kinda like if i manually imported a table with ssis. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-04-06 : 15:29:54
|
| Tara, can you give me an example? only thing i've ever used was openrowset on a csv or xls file, and since we moved to x64, it doesnt work because of the Jet Driver Support :( |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-04-06 : 15:42:56
|
| i guess my issues is i have to link to the flat file in order to use openquery against it right? that's where my issue would be here... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-04-06 : 16:04:14
|
| maybe i'm confused, do i have to create a linked server to the text file first, and then access the data using openquery?do i just access the data directly w/ openquery w/o linking to it first?maybe that'll help me figure out how to do this... everything i find is basicall:select * from openquery(mylinkedobject,select * from mytable) my issue is the portion where i link to the file. i cant figure that out. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-04-06 : 16:46:55
|
tried: select * INTO EMP_TABLE from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from junk.txt')got: OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".any ideas? i checked my Linked Server > Providers list and i see MSDASQL in there, so that means the driver exists right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-04-06 : 17:05:20
|
| tried it, and i running this local to the server, so check, and check, same error :( |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
import CSV file into Table