Import data

[rollingzep]

Hi,

In my Excel application, I import a .CSV file using VBA with the file dialog.
The file contains more than 100,000 rows. I need to import data which are >= 9 characters

The below should be imported

these should not be imported

TIA

 

[alansidman]

With out data which I can manipulate (can't manipulate data in pictures), I will give you the steps I would use.
1. Load your CSV file to Power Query (Get and Transform Data found on the Data Tab)
2. Add a column and test for length.

if Text.Length([Code]) <= 9 then [Code] else null

where Code is the name of the field
3. Highlight the new column and filter out Nulls
4. Close and Load to your Native Excel Spreadsheet.

Note. Based upon your note and visual, it appears that you want to keep those that are less than or equal to 9 digits while you indicate the opposite in your statement.

 

[rollingzep]

With out data which I can manipulate (can't manipulate data in pictures), I will give you the steps I would use.
1. Load your CSV file to Power Query (Get and Transform Data found on the Data Tab)
2. Add a column and test for length.

if Text.Length([Code]) <= 9 then [Code] else null

where Code is the name of the field
3. Highlight the new column and filter out Nulls
4. Close and Load to your Native Excel Spreadsheet.

Note. Based upon your note and visual, it appears that you want to keep those that are less than or equal to 9 digits while you indicate the opposite in your statement.

Is it possible to do it using VBA?

 

[Joe4]

Is it possible to do it using VBA?

Have a look here. You should be able to adapt the solution to do what you want:

Import CSV file based on certain conditions

I am trying to import a CSV text file using the below code. I essentially have 2 issues which I need assistant in: 1. I am trying to read the text file in question and only import certain rows based on whether column 2 of the CSV file has a certain text included or not 2. When I import the file...

www.mrexcel.com

 

[Joe4]

This is what I came up when trying to adapt the code in the thread I referenced:

Sub ImportData()
    Dim R As Long
    Dim C As Integer
    Dim sDelim As String
    Dim sRaw As String
    Dim ReadArray() As String
    Dim vFileName As Variant

    sDelim = ","
    
    vFileName = Application.GetOpenFilename("Comma Separated Files (*.csv),*.csv")
    
    If vFileName <> False Then
    
        Worksheets.Add
        
        Open vFileName For Input As #1
        R = 1
        Do While Not EOF(1)
            Line Input #1, sRaw
            
            ReadArray = Split(sRaw, sDelim, 35, vbTextCompare)
            
            If Len(ReadArray(0)) = 9 Then
                Cells(R, 1).Value = ReadArray(0)
                R = R + 1
            End If
            
        Loop
        Close #1
        
    End If
    
    MsgBox "Import complete!"
    
End Sub