Solved

Prefilter CSV file while importing

Posted on 2011-03-21
10
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 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