No announcement yet.

Open/Import Large Comma Delimited Ascii File

  • Filter
  • Time
  • Show
Clear All
new posts

  • Open/Import Large Comma Delimited Ascii File

    I have a very large (and growing) ASCII file I manually open and then bombard with macros. I tried using the macro recorder to automate opening the file but it is apparently too large. Everything errors out and Excel tells me there are too many continuations.

    Once open the worksheet columns go to EV (fixed) and the rows currently are down to 20627.

    Here is the only code the recorder generates before it stops and throws an error:

    Sub Macro1()
        Workbooks.OpenText Filename := _
            "C:\Documents and Settings\Administrator\Desktop\Files\file.asc" _
            , Origin := 437, StartRow := 1, DataType := xlDelimited, TextQualifier := _
            xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE _
            , Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo := Array(Array(1,1), _
            Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1),Array(7,1),Array(8,1), _
            Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1),Array(14,1),Array(15 _
            ,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array(20,1),Array(21,1), _
            Array(22,1),Array(23,1),Array(24,1),Array(25,1),Array(26,1),Array(27,1),Array( _
            28,1),Array(29,1),Array(30,1),Array(31,1),Array(32,1),Array(33,1),Array(34,1), _
            Array(35,1),Array(36,1),Array(37,1),Array(38,1),Array(39,1),Array(40,1),Array( _
            41,1),Array(42,1),Array(43,1),Array(44,1),Array(45,1),Array(46,1),Array(47,1), _
            Array(48,1),Array(49,1),Array(50,1),Array(51,1),Array(52,1),Array(53,1),Array( _
            54,1),Array(55,1),Array(56,1),Array(57,1),Array(58,1),Array(59,1),Array(60,1), _
            Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1),Array(66,1),Array( _
            67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array(72,1),Array(73,1), _
            Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1),Array(79,1),Array( _
            80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array(85,1),Array(86,1), _
            Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1),Array(92,1),Array( _
            93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array(98,1),Array(99,1), _
            Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104,1),Array(105,1), _
            Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110,1),Array(111,1), _
            Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1),Array(117,1), _
            Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1),Array(123,1), _
    End Sub
    Is there a more streamlined way to approach this?


  • #2
    Re: Open Large Ascii File - Comma Delimited

    Hi Phillip

    What do you do with this text file? You could read it into memory and then parse it from there, but whether you want to do this or not will probably depend on what you want to ultimately do with the data.



    • #3
      Re: Open Large Ascii File - Comma Delimited

      The file is an accumulation of data retrieved from hundreds of sources. I cannot change the way the data is retrieved so I am stuck with a huge ASCII file until the system is reset. Once reset the row number goes back to zero and we start over.

      I use only the latest data from the file. The user picks the range of dates to review. Once I have the ASCII file opened in Excel I have many, many macros which are run against this data to populate various forms, create charts, find statistical patterns, etc.

      I am unfamiliar with reading the file into memory and parsing it from there. How would this actually work in my case?

      I currently have the user simply open the ASCII file and then use keyboard shortcuts to run series of marcos. I just thought I would eliminate one more step for them if I could by having the file open via a macro as well.



      • #4
        Re: Open Large Ascii File - Comma Delimited

        By the sounds of it you only want to extract a limited number of records from the file each time (ie the new data) - presumably each record is date-stemped? Have you considered using Data>Import External Data>New Database Query? You could specify criteria for the data to be returned (eg records after a certain date). To assist you with writing this it would be really helpful to see the structure of the existing text file and explain how it can be determined which records to return.



        • #5
          Re: Open Large Ascii File - Comma Delimited

          I can't offer up any of the data within the text file and I know if I could post it you could find a work around to all the continuations. The data is related to currently open court cases.

          The structure of the text file ends up being 152 columns wide and currently is 20,627 rows long.

          If the continuations are the underscores in the code continuing the command to the next line then couldn't I manually make it fewer continuations? In the arrays, I see it starting at Array(1,1) and then moving to Array(2,1) and then to Array(3,1) and so on. The macro recorder stops at Array(123,1). Does this indicate the 123rd cell? The 123rd column? If it is columns I think I can just alter things a little and make up the difference.
          Auto Merged Post Until 24 Hrs Passes;

          That was all it needed. Too many continuation marks. I didn't know what "too many continuations" was referring to.

          The corrected code which works as it should is:

          Sub Macro1()
              Workbooks.OpenText Filename:="C:\Documents and Settings\Administrator\Desktop\Files\File.asc", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
                  xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
                  Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
                  , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
                  28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _
                  41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array( _
                  54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array( _
                  67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array( _
                  80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array( _
                  93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104, 1), Array(105, 1), _
                  Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, 1), Array(111, 1), Array(112, 1), Array(113, 1), Array(114, 1), Array(115, 1), Array(116, 1), Array(117, 1), _
                  Array(118, 1), Array(119, 1), Array(120, 1), Array(121, 1), Array(122, 1), Array(123, 1), Array(124, 1), Array(125, 1), Array(126, 1), Array(127, 1), Array(128, 1), Array(129, 1), _
                  Array(130, 1), Array(131, 1), Array(132, 1), Array(133, 1), Array(134, 1), Array(135, 1), Array(136, 1), Array(137, 1), Array(138, 1), Array(139, 1), Array(140, 1), Array(141, 1), _
                  Array(142, 1), Array(143, 1), Array(144, 1), Array(145, 1), Array(146, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), Array(151, 1), Array(152, 1))
          End Sub
          So my question now is can this code be streamlined or shortened? It seems very long and redundant with so many arrays being listed.

          Thanks, Richard!

          Last edited by channel7; April 18th, 2008, 05:08. Reason: Auto Merged Doublepost


          • #6
            Re: Open Large Ascii File - Comma Delimited


            Yes, that means the 123 rd column within the text file - I'm not sure why the recorder would stop at 123 (I've never had a text file with that many columns!) - it is possible that the OpenText method simply doesn't support categorisation/specified formatting for any more columns.

            If you find you can't resolveyour problem, come back to me. Is the new data added to the bottom of the text file at each update? ie so the first 20,000 odd rows will be the same as yesterday, but the next 20 rows will be new data?



            • #7

              Re: Open Large Ascii File - Comma Delimited

              Originally posted by RichardSchollar
              Is the new data added to the bottom of the text file at each update? ie so the first 20,000 odd rows will be the same as yesterday, but the next 20 rows will be new data?
              Yes. Data is added twice a week and creates 300-400 new rows each time.

              This code appears to be working just fine and effectively eliminates the four or five mouse clicks the user has to make each time it is opened. Shaving of ounces works in racing; we like to think shaving off seconds works for us.