VBA - Importing select data from .txt to excel by specific value in a row

It probably wouldn't be too hard to come up with something. Are you able to publish a sample dataset?

EDIT:PS if you're using a pc platform, it might be possible to use sql, otherwise you'd probably have to read the file one line at a time and test the 4th data item

I've knocked this together, after a fair bit of surfing:

Might give odd results if your data doesn't have a header tho.

PS I don't know how it'll handle 3m records either - my sample only had 250!

licenseStateName licenseProductName licenseKey licenseCreationDate licenseExpirationDate userFirstName userLastName userEmail marketingInfoState marketingInfoCountry marketingInfoIndustry postalCode accountId accountName licenseGroupId licenseGroupName licenseId licenseSeatsNumber licenseSessionCount lastViewerVersion licenseNewsletterOptIn licensePromotionOptIn numAccountLicenses ContactName ContactTitle ContactEmail ContactPhone
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 neha lastname name@name.com null null null null 9411884 1 13 13 4 9411690 Software Online Order 9425002 2 null null X X 1 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 Michael lastname name@name.com null null null null 9411838 2 13 13 4 9411643 Software Online Order 9424956 2 2 PR 1 X X 1 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 Craig lastname name@name.com null null null null 9411819 3 13 13 4 9411624 Software Online Order 9424937 2 7 PR 2 X X 1 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 David lastname name@name.com null null null null 9412025 4 13 13 4 9411830 Software Online Order 9425155 2 14 PR 3 X X 1 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 Yandong lastname name@name.com null null null null 9411676 5 13 13 4 9411480 Software Online Order 9424790 2 null null X X 1 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 null null name@name.com null null null null 9412116 6 13 13 4 9411924 Software Online Order 9425256 2 null null X X 2 None None null null null
customer kh XXXXXXXXXXXX 6/30/10 6/30/11 null null name@name.com null null null null 9412116 7 13 13 4 9411924 Software Online Order 9425255 2 null null X X 2 None None null null null

Click to expand...

That is the exact format - in small quantities like this, it imports to excel with the wizard using delimited spacing. The date by which we want to identify is right after the "XXXXXXXXXXXX" - in these cases, almost all 6/30/10. Note in the txt file, the date format is 2011-06-30.

Thanks for your help!!!

Essentially, I want to select rows by the value in the 4th of 27 columns.

Thanks!

Okay, just to be clear, there IS a header row, but the data is SPACE delimited.

The previous code, I believe, relies on the data being comma delimited. I'll have a look later to see if there's a way of setting the delimiter.

HTH

Right, I think this ought to do the job. Obviously you're going to need to put your own file path in there and it may not also be desirable to dump the data to the current worksheet, but it should get you started.

As it turns out, I believe the date format is a definite advantage in this scenario.

HTH

ok here is something I use to extract data from a CSV file you will need to add to the schema routine to define ALL of your fields i just use simple F1, F2 etc and define the type, change the delimiter bit to a space, the schema file needs to be in the same folder as the data

finally change the SQL select to select by dates, you may need a # around the dates

'****
' Declare some global variables
'****

Public FileName As String
Public StrPathToTextFile As String

Sub ParseDataFile()

'****
' Setup constants for the SQL query
'****

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

'****
' Declare stuff
'****

Dim StrSQL As String

'****
' create objects that we need
'****

Set objConnection = CreateObject("ADODB.Connection")
Set ObjRecordSet = CreateObject("ADODB.Recordset")

StrPathToTextFile = "C:\mydata\"
FileName = "file1.csv"

Call CreateSchema

'****
' All preparatory work done, lets begin the main process
'****

'****
' Open JET connection to our data file
'****

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & StrPathToTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

'****
' Build SQL strings to get unique categories in no order
'****

StrSQL = "SELECT * FROM " & StrFile
StrSQL = StrSQL & " WHERE F5 <> 0"

'****
' Execute the SQL and get the results and store for next stage
'****

ObjRecordSet.Open StrSQL, objConnection, adOpenStatic, adLockOptimistic, adCmdText

Range("A1").CopyFromRecordset ObjRecordSet
Columns("A:F").EntireColumn.AutoFit

ObjRecordSet.Close

End Sub
Sub CreateSchema()

Open StrPathToTextFile & "schema.ini" For Output As #1

Print #1, "[" & StrFile & "]"
Print #1, "Format=Delimited(,)"
Print #1, "ColNameHeader = False"
Print #1, "MaxScanRows=0"
Print #1, "Col1=F1 Integer"
Print #1, "Col2=F2 Integer"
Print #1, "Col3=F3 Integer"
Print #1, "Col4=F4 Integer"
Print #1, "Col5=F5 Integer"
Print #1, "Col6=F6 Text"

Close #1

End Sub

Hi,

My requirement is also similar, could I get some help:

The rows I want can be identified by a value in column named - "PRODUCT_CODE", if it contains the any one of the following codes 4007, 4008, 4009, 4011/C, 4011/E, 4020, 4021, 4022, 4023, 4024, 4025, 4066, 4067, 4070, 4074, 4075, 4076, 4078, 4092