Tags: asc, contains, excel, file, fixed, hundred, import, lines, microsoft, msdn, opening, software, text, width, wizard

Import *.asc file into excel fixed width

On Microsoft » Microsoft Excel

4,667 words with 3 Comments; publish: Fri, 23 May 2008 08:03:00 GMT; (30646.88, « »)

I am opening a .asc file into excel. When i do so i get the import text wizard and choose fixed width. The file contains several hundred lines of information with about 300 characters on each line. The default column dividers are not correct for the way i need the data broken up. For instance; The 25 characters in position 41-65 is the name of a person but the column divider is set at position 53 splitting up the name. It takes me a considerable amount of time to change the dividers for this whole file. What I would like to do is automate the task. What tool can I use to import the file into excel and automatically set 110 column dividers at very specific points.

All Comments

Leave a comment...

  • 3 Comments
    • Hartsell,

      Look up OpenText in the help and/or record a macro of your manual actions.

      nickHK

      "Hartsell" <anonymous.excel.itags.org.discussions.microsoft.com> wrote in message

      news:B72CDF8B-34C4-4B2B-A322-DD4E7BC9A073.excel.itags.org.microsoft.com...

      > I am opening a .asc file into excel. When i do so i get the import text

      wizard and choose fixed width. The file contains several hundred lines of

      information with about 300 characters on each line. The default column

      dividers are not correct for the way i need the data broken up. For

      instance; The 25 characters in position 41-65 is the name of a person but

      the column divider is set at position 53 splitting up the name. It takes me

      a considerable amount of time to change the dividers for this whole file.

      What I would like to do is automate the task. What tool can I use to import

      the file into excel and automatically set 110 column dividers at very

      specific points.

      #1; Fri, 23 May 2008 08:04:00 GMT
    • And you could use this instead of .txt:

      filefilter:="ASC Files, *.ASC"

      To make it slightly prettier/easier.

      Hartsell wrote:

      > I am opening a .asc file into excel. When i do so i get the import text wizard and choose fixed width. The file contains several hundred lines of information with about 300 characters on each line. The default column dividers are not correct for the way i need the data broken up. For instance; The 25 characters in position 41-65 is the name of a person but the column divider is set at position 53 splitting up the name. It takes me a considerable amount of time to change the dividers for this whole file. What I would like to do is automate the task. What tool can I use to import the file into excel and automatically set 110 column dividers at very specific points.

      --

      Dave Peterson

      ec35720.excel.itags.org.msn.com

      #2; Fri, 23 May 2008 08:05:00 GMT
    • I think I'd start a new workbook and record a macro when I did it once manually.

      Then I could just replay this macro whenever I wanted to import a file with the

      same layout.

      After you've recorded the macro, you'll probably want to adjust the code a

      little to make it more generic. When you recorded your macro, you got something

      that looked like:

      Option Explicit

      Sub Macro1()

      Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _

      DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _

      Array(41, 1))

      End Sub

      Well, instead of having your filename in the code, you can give the user a

      chance to pick it themselves (take a look at getopenfilename in VBA's help):

      Sub macro1A()

      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

      I like to drop a big old button from the Forms toolbar on a worksheet in the

      workbook that contains the code. I assign the macro to the button. And I add a

      few notes to that worksheet.

      Then just hit the big old button when I want to bring in my text file.

      Hartsell wrote:

      > I am opening a .asc file into excel. When i do so i get the import text wizard and choose fixed width. The file contains several hundred lines of information with about 300 characters on each line. The default column dividers are not correct for the way i need the data broken up. For instance; The 25 characters in position 41-65 is the name of a person but the column divider is set at position 53 splitting up the name. It takes me a considerable amount of time to change the dividers for this whole file. What I would like to do is automate the task. What tool can I use to import the file into excel and automatically set 110 column dividers at very specific points.

      --

      Dave Peterson

      ec35720.excel.itags.org.msn.com

      #3; Fri, 23 May 2008 08:06:00 GMT