Solved

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

Posted on 2014-11-19
7
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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.

 
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 47

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with formula in excel 10 41
Excel - Filter Copy/Paste Unique Items 5 23
Balance After Payment 12 21
Access lists formating 8 18
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

697 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