Solved

Excel macro to read text file from Unix.

Posted on 2010-08-30
20
806 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
  • 9
  • 6
  • 5
20 Comments
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 250 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
 
LVL 13

Expert Comment

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

Assisted Solution

by:apresence
apresence earned 250 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now