Solved

I need to convert this file to a csv or excel sheet How would I do this?

Posted on 2014-11-19
7
89 Views
Last Modified: 2014-12-22
I need to convert this file to a csv file or an excel file. This is a flat file that we created years ago for our medical program. Any help on getting these type of files into a csv would be great. I have included a screenshot of an example. what would be the best way to accomplish this?
consult.png
0
Comment
Question by:Steve Lizardi
7 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 40453750
FIrst import it in table to MS-Access with single field.
write VBA function to get the column names
Function GetColumnName(str as String) As String
   Select Case str
   Case "<"
         GetColumnName = "Names"
   Case ">"
         GetColumnName = "Address"
......

Open in new window


Then add to query GetColumnName(Left$([Field],1)) AS MyColumn, Right([Field], Len([Field]) - 1) AS MyValue

Then you can add new query for cross-tab
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40453956
A CSV requires a static format, and the data shown have variable amount of fields.
The second and third "record" have ">" instead of "<" as Name marker.
And there is additional text found - what should happen with that?

Lastly, is PowerShell an option?
0
 
LVL 31

Expert Comment

by:Paul Sauvé
ID: 40453972
this seems to be a text file. Open Excel, Data tab -> Get External Data -> From text

Navigate to the file and click on import
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40456493
It's a textfile.
In VBA you could open it as a TextStream object.
You could then walk down it, line by line.
If the line contains nothing but a carriage return, you move to a next row in Excel
If the line contains anything else, you write the lines contents to a cell and move to the next cell on the right.
@Qlemo is right, there is no consistency so a traditional csv is out of the questions.
When you are done, you'd need to clean the resulting file up considerably, but it would be Excel
Put this code in a module in Excel.
Put the path to your file in A1 of the sheet
Run the macro
Sample attached for you to run it from.

Option Explicit
Public Sub ParseIt()
Dim x As Long
Dim y As Long
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim f As TextStream
Dim LineContent As String

x = 1 'rows
y = 1 'columns
Set f = fs.OpenTextFile(ActiveSheet.Cells(1, 1), ForReading, TristateFalse)
' Read the file line by line
Do While Not f.AtEndOfStream
    LineContent = f.ReadLine
    If LineContent = "" Then ' blank line
        x = x + 1
        y = 1
   Else
        ActiveSheet.Cells(x, y).Value = LineContent
        y = y + 1
   End If
Loop

f.Close
Set f = Nothing

MsgBox "done"

End Sub

Open in new window

test.xls
0
 

Author Comment

by:Steve Lizardi
ID: 40460871
@nick67 I will take a look. sorry for the delay. I will try it on Monday. We are moving from one EHR to another and I need to be able to import files. Thanks guys for all your help.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40512657
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 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

25 Experts available now in Live!

Get 1:1 Help Now