Importing Data

Question

Importing Data

Richard Sabbara

Ten Centuries

Points: 1382
More actions
July 26, 2006 at 9:52 am

#169770

When I am importing data from an Excel file SQL gives me the option to use a query, when I am importing a text file, SQL does not, does anyone know why? This is a big deal to me right now b/c one of the files that I need to use is huge and formatted funny so I have to import it to Access, then export it to a text file, then import that text file to SQL.
Thanks for the help.
Richard

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

did u try to import it to Access, modify it there, and then imort it into SQL Server? Why do u need these 2 steps : Access => text => SQL ? do it directly: Access => SQL

Martin Schoombee SSCoach Points: 19126 More actions · Answer 2

There is a way to do it, but its a bit of a hack. DTS will not give you the option to query a text file connection, but it will allow you to query an odbc connection.

Set up an odbc connection (System DSN) to your text file, using the Microsoft text driver. Then instead of using a text file (source) connection in DTS, you use "Other connection" and point it to your DSN. It will then give you the option to query that connection. (You won't find this in any "best practises" manual)

I don't know your specific scenario, but it is always a better idea (and faster) to import the file into a staging table and then filter it afterwards before inserting it into your final destination table.

Hope this helps,

Martin.

Richard Sabbara Ten Centuries Points: 1382 More actions · Answer 3

Thank you both, I am not that strong in Access which is why I don't import directly from Access. As for the setting up the ODBC, that sounds like a more viable soultion for what I am looking to do, if I can't get it to work then I will try and figure out the Access thing ....

Garth Waring Newbie Points: 6 More actions · Answer 4

Yous should be able to set up the ODBC Connection ( as suggested ) but for Microsoft Access and import directly from Access database via this connection - eliminates need to export the text file after you have processed it in Access

Connection string examples-

SQL server:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Data Source=ROO

Access database:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False