Solved

Formatting and aligning a text file into an Excel or Access file

Posted on 2013-10-31
2
309 Views
Last Modified: 2013-11-01
Hi Guys, I recieve a Daily text file. Can someone show me how to export it to Excel or Access with the data columns in line? I enclose an attachment
GBO-Dashboard-Report.txt
0
Comment
Question by:Justincut
2 Comments
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 500 total points
ID: 39614030
In Excel, you can import a text file:

- Select Data tab > From Text
- Select text file to import from saved location
- Select Delimited
- Select Tab and Comma as the delimiters
- Click Finish

This appears to work fairly well in terms of preserving column alignment, at least until the bottom sections (i.e., Bond Events for Reprocessing and New Branch/Instrument Type Combo).  Unless you have some control over the output format, these would require some manual manipulation.  If you can add commas to the end of the headers and values in each column of the original file, this method will do a better job of aligning the columns in Excel.

Note that this method does result in a significant number of cells with leading spaces.  You can use the TRIM() function to clean these up, or you could try this free utility to do this en masse:

Trim Worksheet
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614139
Your text files seems to contain data from different "tables"
(all with different fields and datatypes.)
- N  O  N     V  A  L  I  D  A  T  E  D      T  R  A  D  E  S / E  V  E  N  T  S  - [D E T A I L S]
- T  R  A  D  E  S    W  I  T  H     N  O     M   T   M
-  C  A  S  H  F  L  O  W      S   T   A   T   U   S
-  R   E   Q   U   E   S    T            S   T   A   T   U   S
...etc
(There is also Header info for each of the "Tables")

Therefore there will be no simple way to import this one file (into Excel or Access) and create all the needed "tables"

What you need to do is go back to the creator of this textfile, and ask that you be given instead, one file for each 'table' (data structure)

From there it looks this the data is comma delimited and can be imported into Access or Excel fairly easily

JeffCoachman
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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 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

15 Experts available now in Live!

Get 1:1 Help Now