Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Import certain data from comma seperated text file to excel using macro

0

I have chunks of data in a 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(2020/11/16) 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 till monday through data in notepad.

Is it possible?

Answer
Discuss

Discussion

Dr Liss
I've answered the CSV text import question but don't understand how the import links to the sample spreadsheet you uploaded.
Please Select my answer (if correct) or revise your Question (not the Discussion) to explain more fully what you want to do with the data.
John_Ru (rep: 6102) Nov 24, '20 at 9:19 am
I've now removed my earlier answer, given you've added ".. using macro" to the subject of the Question.

I doubt I'll have time to respond today (perhaps later) but hope others do. 
John_Ru (rep: 6102) Nov 24, '20 at 9:40 am
Thank you for your time. To answer how it is linked to the spreadsheet attached, the exctracted data from notepad should be imported to the attached spreadsheet.Since Im not able to attach text files. Its bit difficult to understand without it.
Dr Liss (rep: 26) Nov 24, '20 at 9:45 am
My guess is that the time from  goes on that row (of the spreadsheet) with the column corresponding to the date in the text file.
Is there only ever one time per date? Can the spreadsheet have  row (perhaps hidden) with the dates in the same format as the text file? 
John_Ru (rep: 6102) Nov 24, '20 at 10:10 am
No, there are no hidden rows in the spreadsheet.
Dr Liss (rep: 26) Nov 24, '20 at 10:42 am
Perhaps we can add a row that corresponds the format of date with that of textfile
Dr Liss (rep: 26) Nov 24, '20 at 10:44 am
Understood, I was suggesting that we add a hidden row with the format of the date in the text file.
Please confirm that the time is unique per date i.e. there is only ever one time for a given date 
John_Ru (rep: 6102) Nov 24, '20 at 12:12 pm
YES.A single time entry goes to each date.
Dr Liss (rep: 26) Nov 24, '20 at 1:12 pm
Also Ive attached a sample excel file that was recently imported from the textfile(the code I just tried on).
Dr Liss (rep: 26) Nov 24, '20 at 1:15 pm
Not sure why the newly-added book file is macro-enabled (.xlsm) but see there's a connection to GetWSReport.
It represents a bigger import/ mapping problem than your original question implied. Sorry but I won't have time tonight or tomorrow to look at it (but others might). 
John_Ru (rep: 6102) Nov 24, '20 at 1:52 pm
Add to Discussion

Answers

0

The "discussion" has become too big. Therefore I now choose the Answer format but my intention is to delete this "Answer" later on. Please, Dr Liss, revise your question to include all the vital detail you provided in your exchange with John and delete your discussion points once they are included in the question.

There are two ways to import a text file into Excel. One is to open the text file and read the data in a stream. The other is to open the TXT file in Excel and then process the data as Excel data. For this purpose you would open Excel, choose File > Open > Browse, ensure that the file type selector at the bottom right shows All files (*.*), select ther TXT file and open it. You will be prompted to select the delimiter and perhaps set some more parameters for the import.

If the file you posted was created in this way we have to know the delimiters and parameters you entered because that tells us the structure of the text file which we would have to know if we want to read a text stream.

You say you have loads of files therefore it may not be convenient to import them into Excel file by file. However, if the text files are not all structured on the same pattern we would have to know that, too. So, please, when you revise your question, make sure we have all the information there. Thank you.

Discuss

Discussion

Understood. I am posting another question in detail.
Dr Liss (rep: 26) Nov 25, '20 at 2:34 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login