Is there way of automating the import of a text file to Excel?

[Ron0z]

Is there a utility (perhaps open source) that can take a text file of data where each line equals one data record, reads the data, and creates an excel file as output that has been configured to certain user defined specs (ie. column ranges, field names, and data types). The data has no field names and columns have no spaces between fields.

My solution to date (which works fine) is to import the data into Access and export it to Excel. My only complaint is that when I do this several times per day it all becomes tedious. I’m looking for a faster method. Something more automated. Perhaps there is a method of Excel reading a text file into predefined field names from predefined columns. If so I don’t know how it’s done and would appreciate your advice.

[humdingaling]

not quite sure exactly what you want but perhaps you can draw inspiration from a thread i looked at recently

http://www.excelforum.com/excel-prog...txt-files.html

[Marc L]

Text files can be directly opened in Excel …

[apo]

Your best bet would be to attach a sample text file and also a sample workbook showing the desired results..

[Ron0z]

Input file: VLL2.txt (this is my data)
Desired output: VLL2.xlsx

The spreadsheet is what I want to get from the text file. I can create this by importing the text file into Access then exporting the table to Excel. Ideally, I'd have a utility that I run (maybe with a single click) and instantly a new excel file would be created.

The data structure will never change. The file names will never change. The file path will never change. The data is changing constantly.

[JoeFoot]

if no pre-treatment is needed then just do:

Please Login or Register  to view this content.

[Ron0z]

I assume this is VB. I'm not familiar with VB, as yet. If that loads the data, how would the field names get inserted? And how would this code identifiy each field based on different column widths.

[MrShorty]

A non-VBA approach that I often use is the Data->Get External data-> From text command. After selecting the text file to import:

1) Choose the type of data -- your sample file looks like fixed-width, since there does not appear to be any character used as a delimiter.
2) Locate the break points between each field. This will be somewhat tedious, but not difficult.
3) Make any choices in the 3rd step of the text import wizard as needed and finish the text import wizard.
4) Decide where to put the data in the spreadsheet. There is also a "properties" button that will open up other options for this "data range" such as:
a) save query so you can import the same file or another file using the same settings by refreshing the data range
b) other refresh options
c) and so on
Then finish with the sequence of dialogs and evaluate the importation of the data.

[Ron0z]

Thanks for your advice Shorty, however, there seems to be a lot of clicks in your solution. It's clicks that I'm trying to avoid. I have been using Access because I have a predefined spec set up (ExternalData>TextFile>Browse for file>Advanced>Specs>My Spec) to read the data into particular fields based on certain columns widths and it also allocates the field names for me. My current solution is good because I don't have to look for break points, but even that has more clicks that I want to do. Though, if Excel offered a similar facility of a predifined data spec that would be good, but I don't know that it can.

I'm rather envious of a database I sometimes use. It allows reports to be run and put the output on the screen. When the report has completed an icon appears. And here's the thing: when you click on the icon you are offered the choice of saving the reprot output as an Excel file, pdf file, word file. I select Excel and Excel opens with the data I saw on the report a moment ago. That's what I want. A utility that formats my text file into a format that excel thinks is an excel file. I can do this with SPSS: read the data file, write it as a CSV file, but that's a bit of a pain too.

[MrShorty]

Thanks for your advice Shorty, however, there seems to be a lot of clicks in your solution. It's clicks that I'm trying to avoid.

You are right, the first time through, there are several steps to go through. However, if you instruct it to "save query definition" and such correctly in step 4, the second time you need to do it (assuming that the filename is the same and the file format is exactly the same -- only the data has changed), then you only need one Data-> Refresh click. Or, if you have it set up to refresh on open or refresh every hour or something, you need to do nothing more to get the data range to refresh. Before you give up on my suggestion, try it. Mess around with some of the options available in step 4, then see what happens.

[humdingaling]

late reply but VBA solution - one button press and loads up (1 single) text file
filtered just like your example

there may some be tweaking required to get the text import just right

[humdingaling]

cleaned up the version

added extra descriptions for the coding
dialog opens first instead of clearing data first

[Ron0z]

MrShorty, your solution works very well. It took me a little while to see what you were doing, but as you say, once it’s set up it’s so easy to refresh the data. The automatic refresh is an idea that’s probably worth remembering too. Obliged to you.

Humdingaling, that was kind of you to go to such an effort. It’s most appreciated. I’ll retain this and use it. I managed to find the code behind the button. I’ll see if I can use it as a starter to my VB study. I’ve never really tried VB. Though, it’s always been on my wish list. You have inspired me. Just wondering, how much of this code was created by you and how much if any by Excel (I presume it can record Macros)?

[humdingaling]

about 20% was taken from macro record
mainly the importing querytable

20% was taken from google search as i couldnt remember the syntax for the code
60% from the old noggin or old files i have handy