• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Filter importing cvs file?

Is there a method where I can filter an importing/updating cvs file?
I have multiple users and would like to only import thier data from the cvs.
The data has been imported to an excel and it refreshes from there.
0
Euro5
Asked:
Euro5
  • 3
  • 3
1 Solution
 
Patrick MatthewsCommented:
Can you post a more complete description of what you need to do, as well as (sanitized) samples of the CSV and Excel files?
0
 
Euro5Author Commented:
Each user has a copy of the same file that imports the same csv file.
However, what I need is for the file to only import the number entered in cell C1.
This number would correspond to column A in csv file.

Is this enough description? Thanks so much!
IPSAMPLE.csv
SAMPLEIP.xlsx
0
 
SteveCommented:
Yes you can.
If you open up MS Query and use this to open up the csv file you can use SQL to select the data you want... the below code is modified from recorded MS Query...

Dim cmd As String
Dim src As String
Dim dsp As String
Columns("A:C").Insert

    dsp = "txt_" & Format(Now(), "hhmmss")
    src = "ODBC;DefaultDir=" & ThisWorkbook.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
    cmd = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `filename.csv` `filename`" & Chr(13) & "" & Chr(10) & "WHERE (filename.Fieldname =  'SearchCriteria')"

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=src, Destination:=Range("$A$1")).QueryTable
        .CommandText = cmd
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = dsp
        .Refresh BackgroundQuery:=False
    End With

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SteveCommented:
to extract a single value:

strPath = ThisWorkbook.Path & "\"

Set cn = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.Open strcon
strSQL = "SELECT * FROM IPSAMPLE.csv;"
 
Dim rs As Recordset
Dim rsARR() As Variant

Set rs = cn.Execute(strSQL)
thevalue = rs("Region").value
rs.close
set cn = nothing

Open in new window

0
 
Euro5Author Commented:
How do I open up MS Query?
0
 
SteveCommented:
MS Query is from DataFrom other Sources > MS Query
Then you can point it at csv files.
0
 
Euro5Author Commented:
Thanks so much!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now