Post reply

Connecting to a Text File

  • March 10, 2003 at 4:04 pm

    #105629

    This is a real basic question, but how do i connect to a text file through an ActiveX Script? I need to change the name of the file, and i already have a string that contains the new name, as well as the path. The file is sitting on a server (I dont think that matters), and I just need to know how to connect to that file so i can change the name.

  • March 10, 2003 at 4:12 pm

    #450745

    Check out this thread from a few months ago...

    (watch for url wrap!)

    http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=6872&FORUM_ID=19&CAT_ID=3&Topic_Title=Changing+the+Source+File+in+DTS&Forum_Title=Data+Transformation+Services+%28DTS%29

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • March 10, 2003 at 4:15 pm

    #450746

    Also, you could use ADO in your ActiveX task and set the appropriate connection properties...ADO can connect to a text file...this will work if you are 'querying' the text file and bringing in data via a recordset or command object opened over the text file...

    Michael Weiss


    Michael Weiss

  • March 10, 2003 at 4:20 pm

    #450747

    I have a querry that puts data in my text file, all i need to do is append a series of numbers to it that represents the week. I have been trying this with ADO all afternoon, but this statement that has popped up in numerous examples makes no sence to me.

    Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")

    I understand what the connection variable is, but what is a Parent, what is the connections, and what is suppoed to go in the quotations for a text file?

    chris

  • March 10, 2003 at 4:23 pm

    #450748

    A lot of commands i have seen also have this:

    Set cnn = New ADODB.Connection

    well, i am not opening a Database, so i would suspect the ADOBD command dosn't work. What would the command be for a Text file.

    Also, is there some ADO reference on the Internet somewhere? I really haven't been able to find that many great sites.

    chris

  • March 10, 2003 at 4:33 pm

    #450750

    If you are talking to a text file through ADO, then you are using an OLE DB (or ODBC) driver for text files. In a way, you are using the text file as a database. I had never done it with the text file drivers and not sure about the right parameters.

    However you may consider simply using

    master.dbo.xp_cmdshell( 'rename filename_1 filename_2' )

    which is just a DOS command for renaming files.

  • March 10, 2003 at 4:35 pm

    #450751

    DTSGlobalVariables.Parent is a reference to the package object that contains the Global Variables...in your case, your DTS package. DTSGlobalVariables.Parent.Connection is a reference to a connection object in the package containing your global variables. So the line:

    Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")

    Sets the variable oConn to the Excel connection object in the package.

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • March 10, 2003 at 4:37 pm

    #450752

    Here is a link to some ADO connection examples:

    http://www.able-consulting.com/ado_conn.htm

    and here is where you can find the ADO API programming reference on MSDN...

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • March 11, 2003 at 2:35 pm

    #450836

    Ok, i have gotten the script down to

    Set conn = CreateObject("ADODB.Connection")

    conn.Provider = "Microsoft.Jet.OLEDB.4.0"

    conn.Open = "\\slsben01\DvlpApps\Data\WMTBW\WMTBW_Stores.csv"

    Unfortunatly, i get an error back saying that my csv file is an unrecognized format to Microsoft Jet. According to the links posted on this thread, you have to the use Microsoft Jet Provider to connect to text files beacause there is no Provider for text files. Does anyone have any suggestions on how to fix this problem?

    Sorry for all the questions, i am a c++ person myself, but my boss needs more stuff done in VB and ActiveX scripts, which i know nothing about.

    chris

  • March 11, 2003 at 2:53 pm

    #450838

    Try the following at

    http://search.microsoft.com/default.asp?qu=GetFileName%28strFN%29&boolean=ALL&nq=NEW&so=RECCNT&p=1&ig=01&i=00&i=01&i=02&i=03&i=04&i=05&i=06&i=07&i=08&i=09&i=10&i=11&i=12&i=13&i=14&i=15&i=16&i=17&i=18&i=19&i=20&i=21&i=22&i=23&i=24&i=25&i=26&i=27&i=28&i=29&i=30&i=31&i=32&i=33&i=34&i=35&i=36&i=37&i=38&i=39&i=40&i=41&siteid=us/dev

  • March 11, 2003 at 4:03 pm

    #450842

    I am sorry, but my lack of knowledge in VB won't allow me to understand any of that script on Microsoft's website. What is it supposed to do, and how can it remedey my situation?

    chris

  • March 11, 2003 at 5:05 pm

    #450845

    Think I missed the point

    Dim fso As New FileSystemObject

    Dim folder As folder

    Dim file As file

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set folder = fso.GetFolder("c:\t") ' Set path

    Set file = folder.files.Item("t.ttt") ' Get reference to file

    file.Name = "123.123" ' Rename it

    Done this in VB should work about same the vbscript.

  • March 13, 2003 at 10:25 am

    #451018

    RE: MS Jet error

    I have seen strange errors when trying to use file names that aren't in the 8.3 DOS name format. You might try renaming the file to verify this is not an issue.

Viewing 13 posts - 1 through 12 (of 12 total)

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