Solved

Prefilter CSV file while importing

Posted on 2011-03-21
10
387 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

626 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