Solved

Prefilter CSV file while importing

Posted on 2011-03-21
10
361 Views
Last Modified: 2012-05-11
Here is the code I have to import data.(created by recordmacro)
I only need about 5% of the data to process and Trying to figure out HOW I can filter while importing from CSV.
Sub Import_RTR_RIP()
'
' Import_RTR_RIP Macro
'Working import of ALL
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://172.24.8.17/commercial/not_in_rtr_rip.csv", Destination:=Range( _
        "$A$1"))
        .Name = "not_in_rtr_rip"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window


Below is a sample of code that does similar to what I am looking for, but I am not sure how to modify my code to filter the 3rd column to import just the following: (I need all columns, and need them as TEXT)
IPC: Fresno
IPC: Sacramento
IPC: SFBA
IPC: Stockton


Sub ReadTextFile()
    Dim strPath As String
    Dim objConnection As Object
    Dim objRecSet As Object
    Dim strTable  As String
    Dim strConnection As String
    Dim strSql As String

    Range("A5:J10000").ClearContents 'clear off previos data

    ' strPath = "\\server\HammerT"
    strTable = "Hampunches.txt"
    strPath = "c:\"
    'strTable = "test.txt"
    
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";Extended Properties='Text;HDR=NO'"


    Set objConnection = CreateObject("ADODB.Connection")
    With objConnection
      .CursorLocation = 3  ' adUseClient
      .ConnectionString = strConnection
      .Open
    End With

    strSql = "SELECT * " & _
                " FROM " & strTable & _
                " WHERE F3=" & Range("C2") & " AND " & _
                " F1>=#" & Range("A2") & "# AND " & _
                " F1<=#" & Range("B2") & "#"

    Set objRecSet = objConnection.Execute(strSql)

    Range("A5").CopyFromRecordset objRecSet 'write new data
    
    objRecSet.Close
    objConnection.Close

End Sub

Open in new window

0
Comment
Question by:Bruj
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35183999
What's the title of your third column?
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35184037
Once you have it in, you can try. Could you load a sample of your text file so I could test my code?

T
Sub ReadTextFile()
    Dim strPath As String
    Dim objConnection As Object
    Dim objRecSet As Object
    Dim strTable  As String
    Dim strConnection As String
    Dim strSql As String

    Cells.ClearContents 'clear off previos data

    ' strPath = "\\server\HammerT"
    strTable = "Hampunches.txt"
    strPath = "c:\"
    'strTable = "test.txt"
    
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";Extended Properties='Text;HDR=NO'"


    Set objConnection = CreateObject("ADODB.Connection")
    With objConnection
      .CursorLocation = 3  ' adUseClient
      .ConnectionString = strConnection
      .Open
    End With

    strSql = "SELECT * " & _
                " FROM " & strTable & _
                " WHERE column3title=""IPC: Fresno"" " & _
                "OR column3title=""IPC: Sacramento"" " & _
                "OR column3title=""IPC: SFBA"" " & _
                "OR column3title=""IPC: Stockton"""
    Set objRecSet = objConnection.Execute(strSql)

    Range("A5").CopyFromRecordset objRecSet 'write new data
    
    objRecSet.Close
    objConnection.Close

End Sub

Open in new window

0
 

Author Comment

by:Bruj
ID: 35184487
Very Close! The filter works, but the data gets scrambles with the code:(
It looks like columns 2,4 and 5 are being treated as numbers

Here is a sample of data:
 Book1.csv

The 3rd column name is "cran" Book1.csv

Here is the modified code. How do I force to be TEXT using the sql filter method
Before, I could use
       .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2)

Sub ReadTextFile2()
    Dim strPath As String
    Dim objConnection As Object
    Dim objRecSet As Object
    Dim strTable  As String
    Dim strConnection As String
    Dim strSql As String

    Cells.ClearContents 'clear off previos data

    strTable = "not_in_rtr_rip.csv"
    strPath = "E:\"
    
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";Extended Properties='Text;HDR=YES'"


    Set objConnection = CreateObject("ADODB.Connection")
    With objConnection
      .CursorLocation = 3  ' adUseClient
      .ConnectionString = strConnection
      .Open
    End With

    strSql = "SELECT * " & _
                " FROM " & strTable & _
                " WHERE cran=""IPC: Fresno"" " & _
                "OR cran=""IPC: Sacramento"" " & _
                "OR cran=""IPC: SFBA"" " & _
                "OR cran=""IPC: Stockton"""
    Set objRecSet = objConnection.Execute(strSql)

    Range("A5").CopyFromRecordset objRecSet 'write new data
    
    objRecSet.Close
    objConnection.Close

End Sub

Open in new window


Thanks a lot guys!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35185037
what if you put at the beginning of your sub:

cells.numberformat="@"

Open in new window


0
 

Author Comment

by:Bruj
ID: 35185289
nutsch, I had high hopes, but it did not do the trick... I am looks more indepth as the ADO stuff.
If you have any more ideas, plese shoot them to me!
Thanks!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:nutsch
ID: 35185322
You could do a post-facto filtering, using the text import then an autofilter to remove your data. or convert your data in text in SQL directly using a cast or convert (I'm not that good at SQL and don't have access to a server).

0
 

Author Comment

by:Bruj
ID: 35186481
>>You could do a post-facto filtering, using the text import then an autofilter to remove your data
Thats what I have been doing. Just takes some more time (dealing with about 110k records...
I have a script that does this, but it takes about 10 min.

I am not sure what you mean by:r convert your data in text in SQL directly using a cast or convert (I'm not that good at SQL and don't have access to a server).
I will see if I can find more info


After more research, it appears that I should have "IMEX=1" at the end of my connection string, but that did not seem to help.

Anybody else have I ideas?
Thanks
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35186488
Can you load a two or three line template of your text file, removing any confidential info so I can try and reproduce the issue you have on my end? Also, can you tell me what version of excel you're using?

T
0
 

Author Comment

by:Bruj
ID: 35193352
Hey nutsch, I did post it earlier. See:03/21/11 02:02 PM, ID: 35184487
Basically, it is a string, then an IP Block,string,IP Block. and then string.
OK, after doing more research, it appears that I need to edit my registry
typeguessrows = 8 to typeguessrows = 0
My registry is LOCKED down:(
I found a workaround and that is if I insert 8 rows of strictly text, it works fine. So I need to find out how to do that through a macro.
0
 

Author Closing Comment

by:Bruj
ID: 35193367
Due to an issue with typeguessrows (in registry) I need to modify the source a little bit. Or find a workaround to my specific settings. I can also insert 8 rows of text data, and then it will seem to train it to only text


Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now