Import certain data from comma seperated text file to excel

  • I have chunks of data in notepad(.txt) which is seperated by comma but the catch here is it doesnt start from the very first line and every chunk of data must be read to import only second half of each line to corresponding header in the excel sheet.


    FOR example: This is the type of data in notepad having date,time. So only time must be extracted and copied to cell (excel file) which corresponds to specific date(16/11) and header(RunbookBCompletion) present in the notepad.

    Date,RunbookBCompletionTime

    20201116,05:44 AM

    20201117,05:47 AM

    20201118,05:39 AM

    20201119,06:10 AM

    20201120,05:49 AM

    20201121,07:13 AM

    20201122,06:01 AM


    Like wise all the data must read and copy in the same manner. This report is ran once in every week so the excel should update the all the values from previous week through data in notepad

  • Hello CapG


    This does not seem too difficult, - you have a fairly well organised text file .


    I have done a simple macro, Sub LookInAndImportTextStringSample() , to get you started.


    There are probably lots of different ways to do this.


    You can import the text file into Excel, I would suggest using VBA initially , put the data in an array, usually a 1 dimensional array of rows is convenient to start with, then go through that array, picking out the data you want.


    ( BTW, you have what I would call a text file. It can be opened by Excel, Notepad , and a lot of other software. It is not “in notepad” and it is not a notepad or a notepad file. At least that’s how I understand it. I could be wrong


    ( Also, I personally probably wouldn’t call your text file a comma separated text file, since I think that usually implies it contains a fairly regular data table , separated by commas. ) )



    The only thing I can see that might cause awkward problems is that you are dealing with comparing dates and times. I have simply copied your date text strings accross to the Excel file for now.


    There seems to be some inconsistency between the Excel file and the text file in the exact text used for the Header. You’ll need to consider that and tidy up either you text file or Excel file. For the purposes of getting you started with a macro I have modified your Headers in the Excel file so that they tie up with those in the sample file.


    ( The macro could be adjusted to match similar words as an alternative, if you can’t control the exact words used, but I am keeping it simple initially )


    Also your time format is different in different sections in the text file. I have simply copied your date text strings accross to the Excel file for now.



    This is basically wot I done in the macro for you:


    It looks like the conventional “hidden” characters of vbCr and vbLf are used as line separators in your text file, so we can use that to Split the text file string into a 1 D array where each element has the text from a single line


    I make an array to put the required output into, arrOut(). For convenience, to simplify things later, I pick a range to capture via use of .Value, that makes the array, and the specific range I choose so that the second dimension ( “column” ) will coincide with the day. So my range starts at column B , and goes up to AC, so that conveniently gives me 1 To 31 in the second dimension.


    After that its just tedious basic looping stuff to go through and match headings and put the data in the appropriate place.


    The macro I did certainly isn’t the most efficient, but it should be easy for you to follow through, figure out what’s going on, and modify to suit your actual data.


    An initial look at the results , suggest that the macro is doing what you want, but I leave it to you to check it thoroughly






    Alan



    P.S. Whenever I tried to do anything at all in your Excel file it took ages and sometimes crashed. Possibly something is corrupted in it. You don’t have much in it, so I would recommend you dump it and start a new file

  • Edit P.S. Possibly the problems I encountered in your file are the results of a lot of conditional formatting you have in your file, which I have no experience with. So forget my comments about corruption in your original file. ( Although I have heard some experts comment that conditional formatting is a form of corruption , Lol )

    Here is your file again with the my macro in it, in your original form:


    _._____________________________________________________


    Share ‘Sample excel file.xls’ : https://app.box.com/s/hw4uxwjlm8t8zty17kc07xihq0bfhifs

    Share ‘Sample excel file.xlsm’ : https://app.box.com/s/ccmk5sgazueejb4dc0eqw6yex0zhjar2