Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1159
  • Last Modified:

Excel macro to read text file from Unix.

Hi,
I am tring to open a .txt file and read the data in it from excal macro.

the txt file contains data in the format below,
User Id=xxx
Password=xxx

Could someone help me to have this code

0
pnadakuditi
Asked:
pnadakuditi
  • 9
  • 6
  • 5
2 Solutions
 
MWGainesJRCommented:
0
 
MWGainesJRCommented:
0
 
pnadakuditiAuthor Commented:
the text file is in Unix. and I need to red the data from it in Excel amcro
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MWGainesJRCommented:
can you not map a drive?
0
 
apresenceCommented:
Drop this code in an Excel VBA Module and you should be in shape.  The code writes out a file called sample.kvp in UNIX format, and then loads the file and displays one dialog for each key/value pair.

The reusable function you need is:
LoadKVPairFile(FileName, LineDelim)

Where LineDelim should be vbLf for UNIX-format files.  It returns you a key-indexible dictionary of values.

You can also use it for Windows format files by changing the LineDelim parameter.

Let me know if you need any further clarification.
0
 
apresenceCommented:
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

Open in new window

0
 
pnadakuditiAuthor Commented:
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
0
 
apresenceCommented:
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?
0
 
pnadakuditiAuthor Commented:
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
0
 
MWGainesJRCommented:
Can you not create a map?
0
 
pnadakuditiAuthor Commented:
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
0
 
pnadakuditiAuthor Commented:
no
0
 
pnadakuditiAuthor Commented:
no I could not create a map
0
 
MWGainesJRCommented:
Well, you don't want to use a map and you don't want to ftp.....how else will you access the file?
0
 
apresenceCommented:
I'm sorry, your question does not make sense.  Please try re-explaining more clearly and we'd be happy to help you.
0
 
pnadakuditiAuthor Commented:
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?
0
 
pnadakuditiAuthor Commented:
I dont want to download the file from Unix and read the data.. directly I want to get those values from the file.
0
 
MWGainesJRCommented:
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?
0
 
pnadakuditiAuthor Commented:
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..  
0
 
apresenceCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now