• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Data from text file to excel based on specific value

FeJohnsonFe

New Member
Hi team,

I have one input file (csv or text) format, Input file will contains numerous data, So I just want to extract data from Input file based on the cell value in A1 in another excel sheet.
When I paste in Cell A1 then all other information should get retreived from (csv or text)
Please advise how we can create based on VB Macro

Regards,
Johnson
 

Attachments

Marc L

Excel Ninja
Hi,​
do you want to use a command button or when cell A2 is changed (so without any button) ?​
Explain at least what should happen whatever using a button or not, about the file (always the same or not) …​
The better explanation, the quicker & more targeted solution.​
 

FeJohnsonFe

New Member
Hi Marc,

Yes I need the command button.
Once I paste the information in cell A1 (and cell below), and command button should reterive the data from notepad or csv file to the cell B1 TO G1
 

Marc L

Excel Ninja
A1 ? According to your attachment it's a header (column title) so it's should be cell A2 or … ?​
 

Marc L

Excel Ninja
So once the button is clicked, what should happen, you forgot to give any detail about the file :​
unique or to be selected each time the button is called ?​
As again, the better explanation, the more targeted solution …​
As I may give it only a try, no matter if you are able to mod any VBA procedure.​
 

FeJohnsonFe

New Member
Once command button is clicked..Based on the cell value A2,A3, A4, A5,A6 Macro should retrieve the data from input file (csv or text file) and paste the information into cells (B2:G2), (B3:G3), (B4:G4), (B5:G5), (B6:G6) as output file
 

Marc L

Excel Ninja
Without any crystal clear explanation about the file I could write an 'one shot code' as a beginner starter​
and you will have to apply all the necessary mods for all you forgot to well explain …​
 

FeJohnsonFe

New Member
ok Marc.
But information do you need from my end, I can able to provide the same..May be snapshot attached will be useful
 

p45cal

Well-Known Member
The attached contains a Power Query offering, but because it doesn't know where to find the .csv file on your system it will need to be tweaked on a once-only basis:
On opening the file, go to the Data tab of the ribbon and click on Refresh All in the Queries & Connection section. (You could just as easily click on the command button, but you'll get a vba error too, which you'll have to End)
Either way, it will complain, so then:
On the Data tab of the ribbon, in the Queries & Connections section, click on the Queries & Connections icon. This will open a pane on the right. Choose the Queries at the top if it's not already selected. You should then see Input 'Download did not complete'.
Right-click Input and choose Edit… This will open the Power Query editor.
On the right hand side you'll see an area Applied Steps, and below that a series of several steps. (You can click on these to see what's going on.)
Next, on the Home tab of the menu, in the Query section, click Refresh Preview. It will complain with a yellow band. Click on the Source step on the right and you'll see a button: Edit Settings: Click it. It will allow you to browse to the Input.csv file (or whatever you've called it) on your system, then click OK.
The query should now work. (If you click on the last step, Changed Type with Locale, you should see the final table.)
All you need to do now is to Close & Load which is at the extreme left of the Editor's ribbon's Home tab. The data should refresh automatically, if not, click your command button.

I've used cell K2 instead of 'A1 on another sheet'. K2 is a named range Symbol which can be anywhere in the workbook which means you can delete that named range and create a new one of the same name wherever you want (except within the result table!) If you choose to make the named range bigger than one cell, it's the top left cell of that range's value which will be taken.

So the procedure's like this: change the value in K2, click the command button.

You don't actually need a command button or vba (it's only one line), you could either click the Refresh All button in the ribbon as indicated earlier, or you can right-click the result table and choose Refresh.
 

Attachments

FeJohnsonFe

New Member
Power query wont be suits for multiple users..So its better you can give me VB code..Also csv or txt file I can save in same path of excel output file.
 

Marc L

Excel Ninja
According to your attachment as a VBA beginner starter :​
Code:
Private Sub CommandButton1_Click()
        V = ThisWorkbook.Path & "\Input.csv":  If Dir(V) = "" Then Beep: Exit Sub
        Application.ScreenUpdating = False
    With Workbooks.Open(V, , , 2).ActiveSheet
         [A1].CurrentRegion.Columns(1).Copy .[K1]
        .[A1].CurrentRegion.AdvancedFilter xlFilterCopy, .[K1].CurrentRegion, [A1].CurrentRegion.Rows(1)
        .Parent.Close False
    End With
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

FeJohnsonFe

New Member
Thanks..But not working as expected.

1) Input file gets open always which is not required.
2) If I delete or add anything in input file, then I am getting error as "Invalid range"
 

Marc L

Excel Ninja
Thanks..But not working as expected.
It well works according to your explanation level …​
1) Input file gets open always which is not required.
« No arm, no chocolate ! » :rolleyes:
It's like how to pick up a beer from the fridge without opening its door …​
2) If I delete or add anything in input file, then I am getting error as "Invalid range"
No issue on my side like I wrote « According to your attachment » as after all we are not on any mind readers forum ‼​
I won't guess what means 'anything' and as I warned, the more accurate explanation, the more targeted solution …​
So your bad : do not forget to update the worksheet headers accordingly with the source text file after the change you made.​
Exact matching headers - whatever the order - no more range error …​
 
Top