Solved

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

Posted on 2014-11-19
7
93 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 69

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 32

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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