Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Open/Import Large Comma Delimited Ascii File

  1. #1
    Join Date
    11th September 2007
    Location
    Southeast
    Posts
    77

    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:

    VB:
    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?

    Phillip

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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.

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th September 2007
    Location
    Southeast
    Posts
    77

    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.

    Phillip

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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.

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    11th September 2007
    Location
    Southeast
    Posts
    77

    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:

    VB:
    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!

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Open Large Ascii File - Comma Delimited

    Hi

    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?

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th September 2007
    Location
    Southeast
    Posts
    77

    Re: Open Large Ascii File - Comma Delimited

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: May 30th, 2008, 08:28
  2. Save As Comma Delimited Text File
    By badger55 in forum EXCEL HELP
    Replies: 4
    Last Post: January 14th, 2008, 22:11
  3. Import tab delimited file with more than 255 variables
    By ecp98rjs in forum Excel and/or Access Help
    Replies: 2
    Last Post: November 15th, 2005, 17:58
  4. converting .xls file to pip delimited ASCII File
    By Pranob in forum EXCEL HELP
    Replies: 1
    Last Post: October 8th, 2004, 18:47
  5. Replies: 9
    Last Post: August 30th, 2003, 03:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno