CODEWITHSUNDEEP
excelvbacsvsplit
Wildsniper911
Wildsniper911

Reputation: 19

Importing certain columns from a csv file

I am trying to Import a csv file and certain columns to excel and then splitting the data according to a certain column

I am a bit new to the VBA programming and know only basic programming, unfortunately the codes I tried didn't have the intended outcome.

I included the link to the csv file I would like to import to excel: or screenshot:

enter image description here

This file is a stock list exported from the accounting software we need to try and sort this list for stock take purposes, the following needs to be adjusted to the csv file:

  1. The file needs to be imported to excel and only certain columns need to be imported which are

Column A: Code

Column B: description

Column C: Category

Column AA: Text User Field 1 (which in the accounting software it is the Bin number, this is important as the stock take is usually done according to the bin number of the parts)

Column AP: Quantity

The export data will always be the same columns and headings

  1. The excel file then needs to be split according to Column AA: Text User Field 1

  2. The specific data in Column AA: Text User Field 1, "SO0000" needs to be filtered to only include those items (Columns A & B) that do not equal to zero (Column AP: Quantity) meaning all items (Column A: Code) that have Zero quantities (Column AP: Quantity) and have "SO0000" (Column AA: Text User Field 1) should be removed from the list.

I was thinking of using Buttons in sheet 1 where they are named:

Import items button: to import the csv file in sheet 2 of the excel file.

Split data button: To split the items according to the Column AA: Text User Field 1, it would be great if each worksheet could be named according to the data in Column AA: Text User Field 1

I would greatly appreciate it if anyone can assist me with the VBA programming for this.

Upvotes: 0

Views: 2435

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Sub CsvFile_Read()
    Dim vR(), vDB
    Dim WbCSV As Workbook, Wb As Workbook
    Dim Ws As Worksheet
    Dim i As Long, n As Integer
    Dim vFile As Variant

    
    Application.ScreenUpdating = False
    Set Wb = ThisWorkbook
    Set Ws = Wb.Sheets(2)
    
    'Select a text file through the file dialog.
    'Get the path and file name of the selected file to the variable.
    vFile = Application.GetOpenFilename("ExcelFile *.txt,*.txt;*.csv", _
       Title:="Select CSV file", MultiSelect:=False)
       
    'If you don't select file, exit sub.
    If TypeName(vFile) = "Boolean" Then Exit Sub
    
    'The selected text file is imported into an Excel file. format:2 is csv, format:1 is tab
    Set WbCSV = Workbooks.Open(Filename:=vFile, Format:=2)
    
    'Bring all the contents of the sheet into an array.
    With WbCSV.Sheets(1)
        vDB = .UsedRange
        For i = 1 To UBound(vDB, 1)
            'AA column = 27, AP column = 42
            If vDB(i, 27) = "SO0000" And vDB(i, 42) = 0 Then
            Else
                'Get the contents corresponding to the dynamic array. Bring the matrix to the inverted state.
                n = n + 1
                ReDim Preserve vR(1 To 5, 1 To n)
                vR(1, n) = vDB(i, 1)
                vR(2, n) = vDB(i, 2)
                vR(3, n) = vDB(i, 3)
                vR(4, n) = vDB(i, 27)
                vR(5, n) = vDB(i, 42)
            End If
        Next i
    End With
    'Colse the text file
    WbCSV.Close (0)
        
    'The dynamic array is recorded in sheet2.Bring the row to the inverted state.
    With Ws
        .UsedRange.Clear
        .Range("a1").Resize(n, 5).Value = WorksheetFunction.Transpose(vR)
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions