Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel macro to read text file from Unix.

Posted on 2010-08-30
20
Medium Priority
?
1,018 Views
Last Modified: 2012-05-10
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
Comment
Question by:pnadakuditi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 5
20 Comments
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 1000 total points
ID: 33561064
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33561089
0
 

Author Comment

by:pnadakuditi
ID: 33561140
the text file is in Unix. and I need to red the data from it in Excel amcro
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33561242
can you not map a drive?
0
 
LVL 6

Assisted Solution

by:apresence
apresence earned 1000 total points
ID: 33561527
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
 
LVL 6

Expert Comment

by:apresence
ID: 33561530
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
 

Author Comment

by:pnadakuditi
ID: 33561930
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
 
LVL 6

Expert Comment

by:apresence
ID: 33561980
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
 

Author Comment

by:pnadakuditi
ID: 33562207
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33562220
Can you not create a map?
0
 

Author Comment

by:pnadakuditi
ID: 33562243
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
 

Author Comment

by:pnadakuditi
ID: 33562248
no
0
 

Author Comment

by:pnadakuditi
ID: 33562285
no I could not create a map
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33562384
Well, you don't want to use a map and you don't want to ftp.....how else will you access the file?
0
 
LVL 6

Expert Comment

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

Author Comment

by:pnadakuditi
ID: 33562592
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
 

Author Comment

by:pnadakuditi
ID: 33562599
I dont want to download the file from Unix and read the data.. directly I want to get those values from the file.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33562647
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
 

Author Comment

by:pnadakuditi
ID: 33563526
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
 
LVL 6

Expert Comment

by:apresence
ID: 33565101
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question