Solved

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

Posted on 2014-11-19
7
86 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
Comment Utility
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
Comment Utility
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é
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
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
Comment Utility
@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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

6 Experts available now in Live!

Get 1:1 Help Now