Import CSV file based on certain conditions

[Taf]

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, the text qualifier (i.e. Apostrophes ... " ) also get imported - how can I avoid that.

My macros code is as below

Sub Import_H10_Ranking()
    Dim R As Integer
    Dim C As Integer
    Dim sDelim As String
    Dim sRaw As String
    Dim ReadArray() As String

    sDelim = ","
    
    Worksheets.Add
    vFileName = Application.GetOpenFilename("Comma Seperated Fiiles(*.csv),*.csv")
        Open vFileName For Input As #1
                
        Do While Not EOF(1)
            Line Input #1, sRaw
            ReadArray() = Split(sRaw, sDelim, 35, vbTextCompare)
            If ReadArray(1) <> "($)*" Then ' Column 2 of the CSV file must not include ($). This will always be the first three characters of the text string within column 2
            
            Cells(R + 1, 1).Value = ReadArray(0) 'First column of CSV File
            Cells(R + 1, 2).Value = R ' Row number
            Cells(R + 1, 3).Value ="Text 1"
            Cells(R + 1, 4).Value = ReadArray(1) ' Column 2 of CSV
            Cells(R + 1, 5).Value = ReadArray(7) ' Column 8 of CSV
            Cells(R + 1, 6).Formula = "=G" & R + 1 & "/ 30" 'Formula
            Cells(R + 1, 7).Value = ReadArray(9) ' Column 10 of CSV
            Cells(R + 1, 8).Value = ReadArray(13) ' Column 14 of CSV
            Cells(R + 1, 9).Value = ReadArray(12) ' Column 13 of CSV
            Cells(R + 1, 10).Value = ReadArray(11) ' Column 12 of CSV
            
            R = R + 1
            End If
        Loop
        Close #1
End Sub

 

[John_w]

Maybe:

Sub Import_H10_Ranking()
    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 Left(ReadArray(1), 3) <> "($)" Then ' Column 2 of the CSV file must not include ($). This will always be the first three characters of the text string within column 2
                Cells(R, 1).Value = ReadArray(0)  'First column of CSV File
                Cells(R, 2).Value = R  ' Row number
                Cells(R, 3).Value = "Text 1"
                Cells(R, 4).Value = ReadArray(1)  ' Column 2 of CSV
                Cells(R, 5).Value = Replace(ReadArray(7), "'", "")  ' Column 8 of CSV - remove apostrophes
                Cells(R, 6).Formula = "=G" & R + 1 & "/ 30"  'Formula
                Cells(R, 7).Value = ReadArray(9)  ' Column 10 of CSV
                Cells(R, 8).Value = ReadArray(13)  ' Column 14 of CSV
                Cells(R, 9).Value = ReadArray(12)  ' Column 13 of CSV
                Cells(R, 10).Value = ReadArray(11)  ' Column 12 of CSV
                R = R + 1
            End If
            
        Loop
        Close #1
        
    End If
    
End Sub

As an example, apostrophes are removed from column 8 of the CSV - assumes there are no embedded apostrophes you want to keep.

 

[Taf]

Thanks John, the code worked but now I gotten into a quagmire.

When I processed the data based on the above code, I went through a series of 'firefighting' procedures related to the Text qualifier (") and comma delimiter. Let me explain

1. The Code related to ($) did not work because the actual csv file had "($) ... this was a simple solution.
2. However, now the issue was that a number of columns have commas as part of the text (e.g. long note based columns, or numbers in thousands) .. hence the code separates the everything and my column counts get messed up
3. My solution to tat was to keep "," (i.e. the end of a text, qualified through a " then a comma and the beginning of the next text qualifier through a ") but then the result of that was that blank columns which had ,, as the separators did not get picked up.

Anything you can do to help. I basically need to import the text / csv file which has comma separators and " as text qualifiers (but which the condition related to (S) ... and I really dont want to open up connections, etc because the location of the files can change.

I know this might be out of the scope of the original issue but any assistance would be appreciated.

 

[John_w]

2. However, now the issue was that a number of columns have commas as part of the text (e.g. long note based columns, or numbers in thousands) .. hence the code separates the everything and my column counts get messed up

Do the long note columns have text qualifiers (e.g. double quote ")? Do the numbers in thousands have text qualifiers?

If yes to both, then you could try Excel's Text Import wizard (which automatically removes the text qualifiers) to import every line in the .csv file to another sheet and then process the rows in that sheet as required.

If no to either question you would need custom code which reads the .csv file and identifies each column.

 

[Taf]

Hey John, Sorry about the delayed response. Providing the answers to the specific questions below:

Do the long note columns have text qualifiers (e.g. double quote ") ? Do the numbers in thousands have text qualifiers?
- Yes. Every field has text quotations.

If yes to both, then you could try Excel's Text Import wizard (which automatically removes the text qualifiers) to import every line in the .csv file to another sheet and then process the rows in that sheet as required.
- If you mean that I run a macro code to import ... I could do that, but the only issue is that this method creates a sort of "live" connection to the file in question. In my case the location and actual file to be imported would keep changing and hence I would assume that this suggested method would result in a number of "connetion" or "queires" which would be not required or in essence dead. If there is a workaround to this, please let me know.

If no to either question you would need custom code which reads the .csv file and identifies each column.
- This is too much effort. I just want to be able to copy the text, paste the data, and remove certain rows, its too simple to go through a custom request format. I am literally doing it daily for about 3 files, and spending about 5 minutes. The process is too robotic and it is "begging" to be automated.

 

[John_w]

If yes to both, then you could try Excel's Text Import wizard (which automatically removes the text qualifiers) to import every line in the .csv file to another sheet and then process the rows in that sheet as required.
- If you mean that I run a macro code to import ... I could do that, but the only issue is that this method creates a sort of "live" connection to the file in question. In my case the location and actual file to be imported would keep changing and hence I would assume that this suggested method would result in a number of "connetion" or "queires" which would be not required or in essence dead. If there is a workaround to this, please let me know.

Data -> From Text/CSV creates a query and connection to the .csv file as part of the import, however you can always delete the connection.

You can also try File -> Open -> browse to the .csv file and open it and then copy and paste the data to the destination workbook. This method doesn't create a connection.

Both methods can be automated with a macro - start by recording the steps with the macro recorder. With the changing location and file name, somewhere you would need specify either both these values or part of them and then the macro could search folders to find the file to import. Or the macro could simply ask you to browse for the file.