Excel macro to read text file from Unix.

a simpler approach
http://www.exceluser.com/explore/questions/vba_textcols.htm 

the text file is in Unix. and I need to red the data from it in Excel amcro

can you not map a drive?

Helps if I attach the code...

Option Explicit

Const ForReading = 1
Const ForWriting = 2

'- Given the line delimiter, parses out a KV Pair file and returns a directory object
'-   with the KV pairs
Function LoadKVPairFile(FileName, LineDelim)
  Dim objFSO
  Dim aryLines
  Dim strLine
  Dim nLine
  Dim nIdx
  Dim dicKVPairs
  Dim strKey
  Dim strValue

  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set dicKVPairs = CreateObject("Scripting.Dictionary")

  aryLines = Split(objFSO.OpenTextFile(FileName, ForReading).ReadAll(), LineDelim)
  For nLine = LBound(aryLines) To UBound(aryLines)
    strLine = aryLines(nLine)
    If (Len(Trim(strLine)) > 0) Then '- Skip over blank lines
      nIdx = InStr(strLine, "=")
      If (nIdx = 0) Then
        '- Fail out if we get a line not in "Key=Value" format
        MsgBox(FileName & " line " & nLine & " error: No '=' found")
        Exit Function
      Else
        strKey = Left(strLine, nIdx - 1)
        strValue = Mid(strLine, nIdx + 1)
        If (dicKVPairs.Exists(strKey)) Then
          dicKVPairs(strKey) = strValue
        Else
          dicKVPairs.Add strKey, strValue
        End If
      End If
    End If
  Next

  Set LoadKVPairFile = dicKVPairs
End Function

'- Writes out a sample KV Pair file in UNIX format
Sub WriteSampleUNIXKVPairFile(FileName, LineDelim)
  Dim objFile
  Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(FileName, 

ForWriting)
  objFile.Write "User Id=userid" & LineDelim & "Password=password"
End Sub

Sub TestKVPairFile()
  Dim dicKV
  Dim objKey
  Dim strLineDelim

  '- Note: For UNIX, use vbLf, for Windows use vbCrLf
  'strLineDelim = vbCrLf
  strLineDelim = vbLf

  WriteSampleUNIXKVPairFile "sample.kvp", strLineDelim
  Set dicKV = LoadKVPairFile("sample.kvp", strLineDelim)

  '- Display dialog with each key/value pair
  For Each objKey In dicKV.Keys
    MsgBox objKey & "=" & dicKV(objKey)
  Next
End Sub

TestKVPairFile

Select allOpen in new window

Thanks for tryng to help me. Can I know the below.

----Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(FileName,
In the above line, from which path the FileName is going to be read from Unix.

ForWriting)
--  objFile.Write "User Id=userid" & LineDelim & "Password=password"
--User Id and the Password will have the values in txt file. what are userid and password

pnadakuditi: The path is whatever you specify.  I'm assuming you've already downloaded the UNIX file to your local Windows system.  If not, you'll need to do so first.

The userid and password listed there are just for testing purposes.  That function creates an example file just to demostrate that the code works.

What else do you need?

pnadakuditi: The path is whatever you specify.  I'm assuming you've already downloaded the UNIX file to your local Windows system.  If not, you'll need to do so first.


For this, I have ftp an .txt file from my local Windows system to Unix.
who ever use the macro,, they should get data directly from Unix server and they should not download the file

Can you not create a map?

I think there is some confussion regading my requirement.

I need to write a macro in excel,

1) It should access a file(.txt file) from Unix directly. Not by downloading the file into local Windows machine.
User Id=xxx1
Password=xxx2

2) To read data in it. ex., I need to read xxx1 and xxx2 values from that file.

3) After getting these xxx1 and xxx2,, I can Process with my further processings

no

no I could not create a map

Well, you don't want to use a map and you don't want to ftp.....how else will you access the file?

I'm sorry, your question does not make sense.  Please try re-explaining more clearly and we'd be happy to help you.

ok.. let me explain more clearly...

1) There is a file in Unix,, sample.txt. The file contains the data like,,
User Id=xxx1
Password=xxx2

2) Now, In my excel macro I need to get values of xxx1 and xxx2.
For this I need to open the txt file where its located in Unix and I need to read the data.

--- Is my explaination clear?

I dont want to download the file from Unix and read the data.. directly I want to get those values from the file.

If you can't do that manually without drilling down to it from a PC or ftp'ing it, how do you expect VBA to do it with out a mapping or ftp?

Hi MWGainesJR,

Its not like I cant do that manually.  But the when I complete my application and give the final macro to the user.. I do not want the credentials file to be get known by user. The requirement is like.. Th user should not get up known with the contents of the file.

The application should directly read the file from Unix path and read the contents..

My question is,, is there is any feasibility to acheive this requirement..  

It sounds like what you really trying to do is authorize users based on their name and password against some data on a UNIX system.  That's what LDAP is for, and really the best option for you.  There are several LDAP solutions for UNIX depending on what OS you are using and if you need a free version or not, etc.

If you're stuck with the user/password file as it is (it's a customer requirement, kept for legacy reasons, etc) a better option for you would be to write a web service or something similar on the UNIX side that does the lookup for you, and have your VBA call that web service.  Otherwise, no matter what method you come up with to access the file (FTP, mapped drive, SFTP, etc...) any user with moderate computer skills will be able to figure out that you're accessing the file and get access directly him/herself.  With a web service, you also have the added benefit of being able to use HTTPS so that your user lookups are encrypted.  This protects the usernames and passwords from being picked up off of network traffic.