Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
When I open a text-file, the Text Import Wizard is displayed which has
three steps: In Step 1, I indicate whether the file is delimited or fixed width. In Step 2, I indicate where I want the column breaks to occur. In Step 3, I indicate the format of the data in each column. I very frequently open text-files whose rows have a length of 1,274 characters and, when the column breaks are created, there are 108 columns. Rather than going through all that work everytime, how can I set up a "template" (?) that will automatically import the file, break the data into the 108 columns and format the cells correctly? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
I like to create a dedicated macro workbook that contains the code. And then I
put a big button from the Forms toolbar on the only worksheet in that workbook. I'll add a few instructions to that sheet, too. I'd tweak the code to get the name of the file to open from the user and then include code that adds some more stuff--like formatting, filters, subtotals, page setup (headers/footers/rows to repeat at top/etc). Then it actually becomes a tool that makes life a lot easier. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.TXT", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub GARY wrote: When I open a text-file, the Text Import Wizard is displayed which has three steps: In Step 1, I indicate whether the file is delimited or fixed width. In Step 2, I indicate where I want the column breaks to occur. In Step 3, I indicate the format of the data in each column. I very frequently open text-files whose rows have a length of 1,274 characters and, when the column breaks are created, there are 108 columns. Rather than going through all that work everytime, how can I set up a "template" (?) that will automatically import the file, break the data into the 108 columns and format the cells correctly? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
I opened the text file as a delimited file (so Col A contains cells
1,274 characters long). In B1 thru DD1, I wrote formulas (referring to A1) to divide the data in each row into separate fields (with the desired formatting). I then copied and pasted those formulas down to the last row containing data. It took some time but the results are exactly what I wanted. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
I opened the text file as a delimited file (so Col A contains cells
1,274 characters long). In B1 thru DD1, I wrote formulas (referring to A1) to divide the data into separate fields (with the desired formatting). I then copied and pasted those formulas down to the last row containing data. (It took some time but the results are exactly what I needed) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
I would think that using File|Open and parsing the input record would be
quicker, but it sounds like you have a solution. GARY wrote: I opened the text file as a delimited file (so Col A contains cells 1,274 characters long). In B1 thru DD1, I wrote formulas (referring to A1) to divide the data into separate fields (with the desired formatting). I then copied and pasted those formulas down to the last row containing data. (It took some time but the results are exactly what I needed) -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
Creating column-breaks to parse the 1,274-character rows into 107
fields then formatting the resulting fields ONE TIME would be very tedious. Doing it EVERY TIME I open the files would be horrible! That's why I took the time to write the formulas. Now, the formulas are readily available to divide the contents of the cells in Col A and to format the resulting fields. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing text-files
My suggestion was to take the time to do it once with the macro recorder turned
on. Then use that macro to import the next million text files. I gotta believe that importing the file with the macro recorder turned on would be quicker than creating the 107 formulas. GARY wrote: Creating column-breaks to parse the 1,274-character rows into 107 fields then formatting the resulting fields ONE TIME would be very tedious. Doing it EVERY TIME I open the files would be horrible! That's why I took the time to write the formulas. Now, the formulas are readily available to divide the contents of the cells in Col A and to format the resulting fields. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Need Help Importing Text File Using Two or More Spaces as the Delimiter | Excel Discussion (Misc queries) | |||
Help extract numerous text files and how to use avg formula | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Importing Text Files | Excel Discussion (Misc queries) |