Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Prefilter CSV file while importing

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
Bruj
Asked:
Bruj
  • 5
  • 5
1 Solution
 
nutschCommented:
What's the title of your third column?
0
 
nutschCommented:
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
 
BrujAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
nutschCommented:
what if you put at the beginning of your sub:

cells.numberformat="@"

Open in new window


0
 
BrujAuthor Commented:
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
 
nutschCommented:
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
 
BrujAuthor Commented:
>>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
 
nutschCommented:
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
 
BrujAuthor Commented:
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
 
BrujAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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