Post reply

Importing Data

  • 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

  • July 26, 2006 at 4:04 pm

    #651766

    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

     

  • July 27, 2006 at 6:42 am

    #651854

    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. 

  • July 27, 2006 at 8:42 am

    #651895

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

  • July 27, 2006 at 5:32 pm

    #652046

    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

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

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