Solved

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

Posted on 2014-11-19
7
91 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

778 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