+ Reply to Thread
Results 1 to 146 of 146

What concerns should i have reading in a text file using VBA

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    What concerns should i have reading in a text file using VBA

    Hi,

    I am reading in a text file using the following code:

    Please Login or Register  to view this content.

    What if any concerns should i have regarding the reading in of this text file in the manner i have posted?

    One issue that i have is the overall_array. I have to dimensionalize this array prior to reading in the file in the following manner:
    Please Login or Register  to view this content.
    So i have to identify the number of cols and rows in the file prior to storing data into the "overall_array". The number of rows is not an issue but the number of cols is........

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: What concerns should i have reading in a text file using VBA

    Are the number of columns in each row variable?

    Any reason why you can not load the csv file in to a worksheet and then simply load the usedrange into an array?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello Welchs101,

    I have made a few changes to your macro code. This is untested but should work.
    Please Login or Register  to view this content.
    Code Correction
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 01-08-2015 at 07:04 PM. Reason: Changeed Put to Get
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    The reason i am reading this into an array is that its a very very large data set. I can not paste it into a worksheet its too large. But i plan on processing the large array then outputting what i need to a worksheet which will be a small subset of the original data.

    If you have ever done this kind of thing before any suggestions would be appreciated as this is my first attempt to readin input file and process it all via the array.

    thanks for the responses so far!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: What concerns should i have reading in a text file using VBA

    Okay so too large for single sheet, until processed.

    So are the number of fields consistent for each record, is the first record a header row?

    Is it possible to process the information 1 record at a time? and only store required data?

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    andy, these are good questions i have not thought of.......thanks. my guess is that each row will contain some number of fields. Each field will be separated by some sort of delimiter..........say a comma for example.........this is as far as i have thought about the file .........

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: What concerns should i have reading in a text file using VBA

    If the structure of the file is unknow then use Leith Ross' approach.

    If you know more detail about the file then it maybe possible to optimise the approach, such as only having to test first row to obtain number of columns.

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks Andy.

    Leith, i had a question about your code. First, thanks.......from your code i see now how to get around the "number of cols" issue that i was thinking about so thanks.

    I did have a question about this code:
    Please Login or Register  to view this content.


    I guess in my code i used the following:
    Please Login or Register  to view this content.

    Just wondering why you recommended your code over what i did.........just trying to understand why you did what you did vs. what i did but i am having trouble making the comparison of the two. I used the one line of code as i did because in an earlier text file readin (for a small small file) i had to use this method because the text file was a different type ...ie...had a different encoding (not sure what this is exactly) and i had to use this method to open the file and get values read in correctly. Is there an advantage of using yours that i just dont see.......thanks again for your help!

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    andy,
    what i do know about the input file is that the header row should be in a certain row location. I guess i could somehow determine the number of cols in this row and then say this is cmax............and then test #-cols of every other row.....if another row has num-cols greater than cmax then halt program and alert user to correct input file..........because theoretically there should not be more cols than those with headers.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: What concerns should i have reading in a text file using VBA

    I would have thought the header row would have been the first row, unless the body of the file is wrap in some additional information.

    For sure a row with more fields that header-fields should be reported. Less fields is common when the system outputting do not complete a record with trailing empty fields. You can certainly warn user that field information was short and program assumes trailing fields are empty.

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i still have the question in post #8 but i had another question regarding array size.

    Is there a limit to the size of the array one can have? If so then i should check this....right?

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello Welchs101,

    I used the "Open" file method instead of the "FSO" because there is less system overhead. With dealing with large files and arrays, system resources, RAM, need to be conserved for optimum system performance.

    The upper end limit of a text file is about 2 GB (2147483648 bytes).

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    hi,

    I am trying to run Leith's code as shown in post "12-17-2014, 01:29 PM" but i am getting a run time error at this step

    Please Login or Register  to view this content.

    anyone have any ideas what is causing this?

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    If you post the macro code you are using now, it will help in isolating the problem.

  15. #15
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    here is the code i am trying to evaluate or get to run

    Please Login or Register  to view this content.

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    Does the file have any data?

    How many bytes?

  17. #17
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    yes the file has data its a text file with 182,420KB

    is this too big?

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    No, not at all. A string can hold around 2 billion bytes. Is the file path correct?

    If the file is not sensitive, can you post a copy for testing?

  19. #19
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    unfortunately i cant post the file........i wish i could. the file path is correct i know that for sure. i keep getting the error for any file size at this location

    Please Login or Register  to view this content.

    i am basically trying to run the code you posted to see if it is faster than my own.

  20. #20
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    just wanted to make sure you understand the file size i am working with
    182420KB

  21. #21
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    Oops, I found the problem. Sorry, it was my fault. The file is being opened for Reading not Writing. Put needs to be changed to Get.

    Code Correction
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    since another post got booted over to hear i basically have two issues:
    '1-I am trying to run the code i just posted but am getting a file/path run time error but i know the file and path are correct so i am not sure what is wrong.
    '2-The file i am reading in is a very large text file (182420000Bytes). The file is taking a really really long time to readin and i am not sure why. i will try and generate another test file but i wont be able to do this for about another 1hr or so.

  23. #23
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, the get fixed the first issue.......but now i am getting another run time error at this step

    Please Login or Register  to view this content.
    cmax = 0

    should i set this to something?

  24. #24
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    another error ......... i just looked at one of the rows of data being read in......what i see when i go into the debugger is a text string of "???????????????????..." basically a row of question marks........which is weird because the file does not have any question marks in it.very strange.

  25. #25
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello Welchs101,

    When VBA encounters files that have Unicode characters you will see question marks in place of the text.

  26. #26
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    quick question......if i have a comma delimitted file should this

    Please Login or Register  to view this content.
    be something else?

  27. #27
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello Welchs101,

    Change the line with the error to this...
    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    disregard my last post.......i see what that split function is trying to do

  29. #29
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok i got the code to run now.......thanks......let me review how it does against the big file.

    thanks for your help.

  30. #30
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    I believe this version should work correctly. Try it out.
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    well, something is not working .........when i stop the code just after when the text file should be in the "overall_array" and then look at whats in "overall_array" its empty

  32. #32
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Did you use the corrected in my last post?

  33. #33
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    sorry i did not see your post..

    So using your new post i get a type mismatch runtime error here


    Please Login or Register  to view this content.

  34. #34
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    note: i am NOT getting the "?????" anymore i can see something like the data......however i do get the type mismatch runtime error at the location i mentioned.

  35. #35
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    This is weird. I ham running the code on a different text files and also get the "type mismatch" error. At first I thought the error might be due to the lack of a comma in the line text. But, that's not the problem. Still working on it.

  36. #36
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks Leith! I really do appreciate your help!

  37. #37
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    should cmax be "set" to some value? as it stands right now it has a value of 0

  38. #38
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    disregard my cmax statement......i see now how your setting it........

  39. #39
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    After a few interruptions and delays, here is the fully annotated and working code.
    Please Login or Register  to view this content.

  40. #40
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks Leith,

    i ran this on a small file first just to see if there were any little issues. it ran fine. HOwever, when i run it on the "big" text file i get a runtime error saying "Out of memory". When i click the debug button it takes me to the following step in the code

    Please Login or Register  to view this content.
    Any ideas?

  41. #41
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i am starting to think the text file is just too big..........

  42. #42
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    any one have any ideas?

  43. #43
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    You need to read the file in chunks and process line by line. It's too large to simply whack into memory like that

  44. #44
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    any ideas on how to do that?

  45. #45
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    keep in mind that this big text file never gets stored in any excel sheets.......i just use the data (or the plan was ) to derive results which get put into excel sheets and the results are only a few hundred lines at best.

  46. #46
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    So you're looping through and filtering out unnecessary lines? If so, why do you need to read it all into memory at once?

  47. #47
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    no, i use all the data to derive certain results.........and its the results which i put into excel.......does this make sense?

  48. #48
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    Ok, what are you actually trying to do?

    Think big picture, you have a large dataset and you want to do what with it?

    Perform calculations on every row?

    Look up items like a dictionary?

    Add all the fields?

    It is an unusual request to want to load such a large amount of data into memory to do stuff with it. If you tell us what you've got and what your end goal is with a bit of background, there may be a better method

  49. #49
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, there is a lot going on in this macro but i will try and summarize.

    '1-I read the "header row" to identify what column headers are in the file. The current file has 19 cols of data. Of the 19 cols i only use 6 cols of the data to make comparisons / decisions.
    '2-One of the cols is a "site-id" col. I create a dictionary of site-id values. In this dictionary i store multiple items for a given site id. I store the site id value and the count of the site-id value.......so in the end for a given siteid value in the dictionary i know the number of times this site-id value appeared.
    '3-Then i take this site-id dictionary and i take the BIG-Array file and i make some comparisons and then based on these comparisons i put data onto an excel sheet.

    does this help?

  50. #50
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    Think we're getting there:
    Then i take this site-id dictionary and i take the BIG-Array file and i make some comparisons and then based on these comparisons i put data onto an excel sheet.
    So do you have 2 large text files? One with the site info and then another that you use for comparison?

  51. #51
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    no, the data is All in one text file..........

  52. #52
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    Ok, so what are these comparison? And can you post a small sample of the data?

  53. #53
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    posting actual file i cant do as there might be sensitive information and i could get in trouble. Let me try and create a dummy file......this might take a while.

  54. #54
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, i have enclosed a sample of the data file i am working with.

    If your interested in how i get my BIG file here is what i did to create it.
    I opened the enclosed file using note pad......note: header row is row 1 in the file. I copied all the rows in the file except the header row and pasted it 13 times........this gave me approximately 1.3million rows of data. i did this to test out the reading in of the file into an array to see if i had issues and i sure did.

    I still would like to know what is causing the memory error. is it that the single array is to big? i have read some where that arrays can have a max size and no more.....for example: lets say max array size is 100kB. In this example you cant have an array which takes up more memory than 100kB (again just an example not real numbers). However, you can have several arrays say at 10kB which when added up are more than the 100kB. If this is the case then is there some way to parse the input file into multiple arrays?
    Attached Files Attached Files

  55. #55
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Hi Welchs101,

    interesting thread
    I tried to stretch your file at 1300000 rows and behold:
    Please Login or Register  to view this content.
    t = Split(x(i), ",")(1) is the second column ('SiteNum')
    Last edited by nilem; 01-09-2015 at 12:30 PM.

  56. #56
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    thanks. but i am getting a run time error (subscript out of range) at this location

    Please Login or Register  to view this content.

  57. #57
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    let's add one line and correct one mistake (all in red)
    Please Login or Register  to view this content.

  58. #58
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    The file parsed fine for me using the macro I wrote. It took less than 2 seconds.

    Press the Windows key and the Pause/Break key together. This will display information about your system. How much RAM do you have installed?

  59. #59
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    installed memory is 4GB and its a windows 7 system 64bit

  60. #60
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    note: the file i enclosed is NOT the full 1.3million rows........its only ~94k rows........did you run the full 1.3million?

  61. #61
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    4GB is the minimum needed to run Windows 7. You really need 8GB to run other programs and Office without problems.

    The only alternative is "chunk" the file. That is bring it in smaller pieces.

  62. #62
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    in case anyone was interested: The data i collect in the dictionary is shown below in the code. Of course the program never gets to this procedure but i wanted to share what i was putting into the dictionary incase someone had some ideas on how to implement this with the 1.3million rows. There are some other procedures i woudl need to modify but this is the big one.

    Please Login or Register  to view this content.

  63. #63
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, read it in smaller chunks.....ok

    but i had a thought.

    What i really need from the 19 cols of data are 7 cols of data that i actually use which are

    Cntry
    SiteNum
    SubjNum
    VisCol
    Formcol
    FmStrt
    S_StatucCol

    Is there a way to create 7 arrays from the data for each of the cols above?

  64. #64
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    any ideas?

  65. #65
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Hi welchs101,
    Are you getting an 'out of memory' message with the code from post #55 (+#57)? It works for me after restarting Excel only (win7, 4Gb ram)

    And also try this
    Please Login or Register  to view this content.

  66. #66
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    hi nilem,

    the code in post #55 and #57. No, it does not work for me when i try and run the 1.3million row file. Note: The file i posted on line here was only ~94000 rows.......i could not post the big file as the file manager said the file was too big. To get the 1.3million row file take the 94k file i posted and copy/paste it 13 times in notepad and then save it.

    Regarding your most recent post code: I am trying to get this to run but am having issues. I think my problem has something to do with the "thisworkbook.path" variable but i am not sure.

  67. #67
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    initially i was not able to get your latest code to work. then i realized that i was using an input file which did not have the correct "column header values". So i created a test input file with 1.3million rows with the correct header values and IT WORKED........well it ran to completion at least. So this is a good step in the right direction. So i thought perhaps the same input file issue was what was causing your code posted in #55 and #57 to not work so i re-ran this code using the same input file i just created............i still get an out of memory error at this step
    Please Login or Register  to view this content.
    good news is i can get your latest code to work.........just now need to figure out how to implement this..........

    why do you think your latest code worked but not the one in #55 and #57

  68. #68
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    is there a way using your latest code to "verify" the column header values are in the file and then do the importing of the data?

  69. #69
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Perhaps you know the column numbers (starting with 0), for example 0, 1, 2, 6, 9, 11, 17
    then try
    Please Login or Register  to view this content.

  70. #70
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    thanks......i will take a look at this later today. i have family duty now.

  71. #71
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i did have one question concerning the setting of the reference.

    Is there a way to set this reference in the vba code itself? For example, when using dictionaries i use the following

    Please Login or Register  to view this content.
    in the vba code instead of setting a preference.

  72. #72
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    you can use reference Tools -> References -> MS Scripting Runtime
    and use variable like this
    Please Login or Register  to view this content.
    it is called 'early binding' and the macro will run a bit faster in this case
    Last edited by nilem; 01-10-2015 at 12:47 PM.

  73. #73
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem, thanks. i wasnt actually referring to the dictionary i was asking the question about

    'with using reference to the ActiveX Data Objects library:
    'Tools -> References -> MicroSoft ActiveX Data Objects 6.1 Library


    how to do this in the vba code instead of having someone have to set their references in vba..........ie......if i give this macro to someone wont they have to set their references inorder for it to run correctly? or is the reference set in the file itself?

    thanks again for your help.

  74. #74
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Hi welchs101,
    try this
    Please Login or Register  to view this content.

  75. #75
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    hi nilem,

    thanks. that one worked as well and you dont need to go into references and check " MicroSoft ActiveX Data Objects 6.1 Library". Thanks.

  76. #76
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    i was hoping you could explain the following code a little bit:

    Please Login or Register  to view this content.
    can you explain what is going on here? not sure i understand it completely.

  77. #77
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Okay, look at this link
    and read this book: Excel® 2007 VBA. Programmer’s Reference (John Green, Stephen Bullen, Rob Bovey, Michael Alexander) chapter 20 "Data Access with ADO"

  78. #78
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks i will take a look at those.

    I did have another question. I am now trying to apply what was given here to "my" real code and i am having issues.

    I am getting this run time error (see attached file)
    missing_operator.JPG

    not sure why........any ideas?

  79. #79
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Can you show your real code and your actual file you are working with?

  80. #80
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    You need to enclose the column names in [].
    Remember what the dormouse said
    Feed your head

  81. #81
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    when i click on debug it highlights this line of code

    Please Login or Register  to view this content.

  82. #82
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    nilem, i get this error when i use your code......i am just using a different input text file which has a different header string.

  83. #83
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    below is the code i am using............notice how i comment out / replace two lines of the original code


    Please Login or Register  to view this content.

  84. #84
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    romperstomper,
    the code works fine for one input file, ie...not [] but not another.

    but for the life of me i cant figure out whats different with this other text file that would cause this.......

  85. #85
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Does one have spaces in the field names and the other not?

  86. #86
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    same number of cols in this other text file. important cols are in the same locations as well.......so i dont get it.

  87. #87
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Try to add this line
    Please Login or Register  to view this content.
    What message do you get? Are there any gaps in the title headings?

  88. #88
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i am enclosing a "similar" test file which does show the same error when i try to run it.

    test file only has header and then 2 or 3 lines of text. but it shows the same run time error.
    Attached Files Attached Files

  89. #89
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Change the loop:
    Please Login or Register  to view this content.

  90. #90
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: What concerns should i have reading in a text file using VBA

    Yes, Romperstomper was right, and it's my fault, sorry
    replace this line
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.

  91. #91
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    that was it.........thanks.

    nilem, no worries i would not have gotten this far without your help.........thanks.

    thanks to you too romper.

  92. #92
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Glad to help. Please don't forget to mark the thread solved.

  93. #93
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i will...........

    I did have another question. I read some of the material provided but i still dont get what this is doing in the code

    Please Login or Register  to view this content.
    what are the "0,1,1"

  94. #94
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Those three arguments are Cursor Type, Lock Type and Options respectively. In order:
    0 = adOpenForwardOnly (the default - means you can only move forward through the records.)
    1 = adLockReadOnly (the default. In conjunction with forward only, this is known as a firehose cursor and is the fastest performer)
    1 = adCmdText (this specifies that you are passing a SQL string, rather than say the name of a table or stored procedure).

  95. #95
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks. i found the move forward but could not find what the others ment.

    i had another question.

    Currently in the code data is returned in the array "x"........but the headers are not included in this array. Is there a way for the first item in the array to be the column header?

  96. #96
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i tried changing this statement

    Please Login or Register  to view this content.
    to this

    Please Login or Register  to view this content.
    But this gives the same result. For completeness i tried changing the code to this

    Please Login or Register  to view this content.
    But i get a runtime error..........

    Note: i dont even know if this is the correct way to approach getting the headers included in the extract

    any ideas?

  97. #97
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    Note the run time error i get is this:

    runtimerrror1.JPG

    also when i press the debug button the following code is highlighted as yellow

    Please Login or Register  to view this content.

  98. #98
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    GetRows won't include headers since they are not rows of data. There is no simple way to add the headers to the start of the array, but you can add them to a new array simply by looping through the Fields collection of the recordset.

    You get a runtime error when you say the data doesn't have headers because your SQL is trying to use field names.

  99. #99
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks...........i have been doing some surfing and thats kinda what i was thinking based on what i was reading........still dont fully understand all this ADO/SQL stuff........but i am learning.......

    thanks. I will just work with what i have......

  100. #100
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, sorry but i have another question. as i move forward with this code i am finding out things i need to address.

    For example. Please find and run the enclosed input file.

    Notice that in the 2nd col of data the first data entry was changed from 101 to 101a......for some reason this causes the first element in the array to be a "Null" value.........

    I get that it sees this value as text while the others are numeric but is there a way to have all the data read in as text? this way i can be sure i am getting all the data?
    Attached Files Attached Files

  101. #101
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    You can add "IMEX=1" to the extended properties but that won't work if you have lots of one data type in a field before another type. Better to use a Schema.ini file in the same directory as the text file you are trying to read.

  102. #102
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok....how do i do this? can you show me?

  103. #103
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    is the only way really to have an external file? that kinda stinks......

  104. #104
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i tried the IMEX=1 ........ just to see on a file that basically had only two rows.........it did not work........

    is this how i should have put IMEX into the code.........

    Please Login or Register  to view this content.

    Why did this not work?

  105. #105
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i guess i could create the Schema.ini file save it to the directory location.......

    but could you show me what the file would need to look like and how my code would need to change to use it?

  106. #106
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Actually, this would be a simple solution to both issues. Specify HDR=No and IMEX=1 (so you get the headers in the data) and use F1, F2 etc rather than the actual column headers. Note that in this case you want 1 in the array for the first column rather than 0 and so on:

    Please Login or Register  to view this content.

  107. #107
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i am doing something wrong........i know this.......just not sure what........i copied/pasted your code and tried to run it and got a runtime error..........

    the column names F1, F2........ not sure why this is?

    again, i know i am doing something wrong just not sure what........is this code supposed to use a "schema.ini" file?

  108. #108
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Nope. Did you adjust the folder and file names to match your actual data source?

  109. #109
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    is the code you posted only retrieving the 7 cols or is it trying to retrieve all the cols of data from the input file?

  110. #110
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Only the 7 columns specified.

  111. #111
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    runtime error shown is this

    runtime2.JPG

    when i click on debug it takes me to this line of code

    Please Login or Register  to view this content.
    any ideas..........

  112. #112
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    did i need to create a schema.ini type file?

  113. #113
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    No, the code is self contained. I suggest restarting Excel and testing it again, just to clear out any memory allocations.

  114. #114
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, i shut down excel and then brought it back up..........IT RAN!

    I dont know how you guys know this stuff.......anyway, good news it ran.

    When i run my test file i get the desired result (keep in mind my test file has a header row and only two rows of data). See figure below:test_file_array.JPG

    However, when i run my BIG TEXT FILE........ i get a NULL VALUE see below

    big_file_array.JPG

    so its kinda like you said when most of the data is numeric......and it sees the header then it makes header value a Null value.

  115. #115
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i guess i need ot use the schema.ini approach..........can you show me how to do this?

  116. #116
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    in the schema.ini file do i put only the 7 cols i want or do i put all the columns in the input file?

    if i use a schema.ini file approach how does this change the code if at all?

    if i use the schema.ini file will i still be able to extract only the 7 cols of data i want?

  117. #117
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    does anyone have suggestions?

  118. #118
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, well i think i figured it out through lots of trial and error. Here is what i found out.

    I created a "schema.ini" file using notepad (see below) and i put this file in the same directory as the input file.
    Please Login or Register  to view this content.


    Then i used the code nilem supplied as shown below
    Please Login or Register  to view this content.

    and it seemed to work. i get the headers and all the items are read in as text so no null values.....

    does anyone see any problems with what i have done? anything that may cause me a problem further down the coding-road?

  119. #119
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    I am still working through this text import and process macro. What keeps happening every now and again is i get a run time error saying

    "not enough storage is available to complete this operation"

    Based on what romperstomper suggested in an earlier post i just shut down excel and then re-open it and i can run again for a few times. Any idea why this keeps happening? Is there something i should be doing that i am not or is there something i am doing i shouldnt that may be causing this like not closing or clearing something?

    Also, one more question.........given that i have this very large array of 7 cols and 1.3million rows...........should i be passing this array to other procedures or should i not?

  120. #120
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Arrays are always passed ByRef so it shouldn't make any difference whether you use a variable with appropriate scope or pass the array as an argument. My suspicion would be that Excel may be the wrong program for what you are doing but it's very hard to say as you haven't really explained what you are doing.

  121. #121
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    one more question: Recall this assignment which reads in the data from the file to an array.

    Please Login or Register  to view this content.
    The data is stored in the variable "x". "X" becomes an array organized in the following manner.

    X: Array is orgainized (1 to y, 1 to z)
    'where y is really the number of COLS and z is really the number of ROWS


    any idea why the array is organized this way vs say (1 to z, 1 to y) as i normally do it.

  122. #122
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: What concerns should i have reading in a text file using VBA

    I suggest that you Google: excel memory leak vba ado.

    Check if any of the links are relevant to what you are doing.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  123. #123
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    That's just how GetRows works. Since you aren't putting it into a range, it shouldn't really pose any problems.

  124. #124
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    romperstomper did you have any opinions on my previous question regarding sending big array to a procedure and the memory issue?

  125. #125
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA


  126. #126
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    what does byref have to do with anything.......i thought a copy of what ever you send to the procedure is made for that procedure regardless if its byref or byval.........is this not true?

  127. #127
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    I still think you're doing it the wrong way (or using the wrong tool). I suspect that you don't really need all that data in memory at once - I reckon aggregating with SQL will give you some mileage. I'd still chunk it and only get the relevant info, but since we don't really know what you're doing it's impossible to give you any better advice

  128. #128
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i can run the macro reading in the large file.......since i am still working on the macro i usually stop and check the results of what i just completed. when i try to run the macro again i get an out of memory error............

    any thoughts on how to fix? why would it let me run it once but not twice?

  129. #129
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: What concerns should i have reading in a text file using VBA

    RE ByVal, ByRef, no, that's not true but it's a different question and merits a new thread in it's own right

  130. #130
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    any thoughts on my last post why i can run the macro once but not twice? is there some clearing of memory i need to do between runs

  131. #131
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    I would suspect that you're interrupting VBA's garbage collection or failing to properly close and destroy the objects you are creating.

  132. #132
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok so how to "destroy" them afgter i have stopped the macro.......can i do this in the immediate window and if so how .....thanks again.

  133. #133
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    I suggest you put all the clean up operations into a section at the end, and always run that even if you skip the rest of the code.

  134. #134
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    sorry but have another question.

    it seems that if the input file name is too long then i get the following error "Syntax error in FROM clause"
    filename_too_long_error.JPG

    the file name in question that is too long is this
    "ABCDEFGH ABC Reconciliation Report 31Oct2014"

    when i click on the debug button it takes me to this line
    Please Login or Register  to view this content.
    note: sSQL is defined in another line of code
    Please Login or Register  to view this content.
    so i guess the parameter "Input_Filename" can only be a certain number of characters? or does sSQL limited to certain number of characters?

  135. #135
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    turns out i dont think it was the number of characters at all but rather the "spaces" that seem to be causing the problem. i wonder why?

  136. #136
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    i am still getting "out of memory" runtime error when ever i run the macro a 2nd time. i tried putting an erase statement in my code but this does not seem to help........

    when i get the out of memory error and press the debug button it takes me to this step in the code where your using ADO to get data from the file and store it into an array

    Please Login or Register  to view this content.
    but i added an

    "Erase overall_array()" statement at the end of this procedure but it did not seem to help........any ideas?

  137. #137
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok, got it........

    i read what everyone was saying about memory ie......

    set objects to nothing
    erase arrays

    i thought i had done this several times but the last time i went through "Every" procedure and cleared the memory on exiting the procedure........and this finally solved the problem..........

  138. #138
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    ok.....i have another question

    as i mentioned in my previous post i was able to around the out-of-memory error on the 2nd run of the macro by erasing arrays and setting object stuff to nothing.

    i added the following code and now i get the out of memory error again.......but this code should not cause a problem

    Please Login or Register  to view this content.


    anyone have any ideas as to why? i am completely lsot on this one.

  139. #139
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Re the spaces you just need to enclose the filename in square brackets in the SQL string.

    Are you creating the schema file in your code?

  140. #140
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    yes i am creating the schema file in my code.........

    where do i put the square brackets exactly?

  141. #141
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What concerns should i have reading in a text file using VBA

    Here:
    Please Login or Register  to view this content.

  142. #142
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: What concerns should i have reading in a text file using VBA

    Have you considered importing the data into a database?

    PS Sorry if that's already been suggested.
    If posting code please use code tags, see here.

  143. #143
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    norie, can you explain what you mean by this approach? Do you mean not to use excel but rather import data into a database?

  144. #144
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: What concerns should i have reading in a text file using VBA

    Probably being picky, but you have a whole chunk of code, in the FileExists function, to check if the file exists before you delete it.

    That accesses the hard disk using the Dir function. Then, if it returns True, you go back to the hard disk to delete it. So, four lines of code plus the call to the function plus an extra trip to the hard disk ...

    Maybe, just this:

    Please Login or Register  to view this content.

    The error trapping captures the error message if the file does not exist and "throws it away". And, I'm guessing that you created the file anyway so, chances are, it does exist and the error trap is mostly redundant.

    Regards, TMS

  145. #145
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: What concerns should i have reading in a text file using VBA

    welchs

    Yes I mean import the data into a database.

    As for not involving Excel, it really depends on what you want to do with the data.

  146. #146
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: What concerns should i have reading in a text file using VBA

    thanks TMS........

    norie, yes, i can get the 1.3million row file to run on my computer.....however, when i ask someone else to run it they get out of memory issues.........some of the times probably because they are "doing" other things on their computer.........

    so really a 1.3million row file being processed with excel.......is probably not a good idea. I know jmp could handle such a file no problem but that raises more issues.......i think you might be correct......it might be that loading the input file into a database and processing it that way may be the way to go.........

    but i LEARNED A LOT just trying to get this to work on my computer.......so for me it was worth the effort........

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. reading values in from a text file
    By thegoat001 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2011, 07:03 AM
  2. Reading a Text File
    By SDruley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2010, 03:49 AM
  3. reading from text file to excel file
    By dgoel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2005, 03:06 PM
  4. Reading a text file
    By WTG in forum Excel General
    Replies: 2
    Last Post: 02-21-2005, 10:06 PM
  5. Reading a text file ?
    By WTG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2005, 10:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1