[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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

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
Justincut
Asked:
Justincut
1 Solution
 
mark_harris231Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now