Databases
StackOverflow2013
Postsdbo
POVBA code to import multiple Text files with filter in Excel
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA code to import multiple Text files with filter in Excel
    primarykey
    data
    text
    <p>Base understanding:- I have Many Text Files (around 150) which contains a lot of data for all division. i wanted to import these text files and at the same time apply filter on data so that i dont make my excel too heavy with data. Also the Text files might have different delimeter to be applied.</p> <p>Progress till now: I was using the connection property of Excel to get data and filter data using SQL queries. This is working fine for one table but is not working for different files </p> <p>VBA code:</p> <pre><code>Sub Text_import() VAR_FILE_PATH = "C:\Test\" If Right(VAR_FILE_PATH, 1) &lt;&gt; "\" Then VAR_INPUT_PATH = VAR_FILE_PATH &amp; "\" End If VAR_FILE_NAME = Range("RNG_TABLE_NAME").Value VAR_TABLE_NAME = Range("RNG_TABLE_NAME").Offset(-1, 0).Value Range(VAR_TABLE_NAME).Select Selection.ClearContents Range("RNG_TABLE_NAME").Offset(1, 0).Select VAR_FILTER1_ON = Range("RNG_TABLE_FILTER1").Value VAR_FILTER1_CRITERIA = Range("RNG_TABLE_FILTER1_C").Value VAR_FILTER2_ON = Range("RNG_TABLE_FILTER2").Value VAR_FILTER2_CRITERIA = Range("RNG_TABLE_FILTER2_C").Value VAR_QUERY = "SELECT * FROM `" &amp; VAR_FILE_NAME &amp; "` `A` WHERE `A`." &amp; VAR_FILTER1_ON &amp; " = '" &amp; VAR_FILTER1_CRITERIA &amp; "'" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DBQ=" &amp; VAR_FILE_PATH &amp; ";DefaultDir=" &amp; VAR_FILE_PATH &amp; " ;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _ , Destination:=Range("RNG_TABLE_NAME").Offset(1, 0)).QueryTable .CommandText = Array(VAR_QUERY) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub </code></pre> <p>If anyone can guide me on how i can proceed with current code or suggest a new way of doing things. Thanks in advance for the help.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser3122572
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COEven if you add a filter, the data will still be there, just hidden from the user. If you just want to keep it less cluttered this is fine, but if you're worried about performance you might want to look for a way to filter out what you don't want before adding the data to the spreadsheet.
      singulars
      1. POVBA code to import multiple Text files with filter in Excel
      1. USPaul Brindley
    2. COHi paul Brindley, i will apply the filter in the text file and import only the filtered data. using SQL query VAR_QUERY = "SELECT * FROM `" & VAR_FILE_NAME & "` `A` WHERE `A`." & VAR_FILTER1_ON & " = '" & VAR_FILTER1_CRITERIA & "'"
      singulars
      1. POVBA code to import multiple Text files with filter in Excel
      1. USuser3122572
    3. COWhat is the format of the text files? Are they CSVs?
      singulars
      1. POVBA code to import multiple Text files with filter in Excel
      1. USBlackhawk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload