none
How to read .csv file separating the column other than a comma RRS feed

  • Question

  • Hello,

    I open a Spreadsheet (.csv) file with the following statement:

    Open folderNameInput & fileName For Input As FileNbr

     

    Then I read in VBA by using the following statement:

    Line Input #FileNbr, textRow

     

    The input that I received (in textRow) is one row consisted of multiple columns, each are separated by a comma (,).

     

    However, I need to split the data in one of the column (let say column 5 input), to be written into several columns in another output “.csv” files. Unfortunately, a comma also existed in column 5 that I should NOT split, so I cannot split based on the existence of a comma otherwise there is no distinction between the comma that is in column 5 input or this is the comma separating the columns that I read.

     

    I am thinking if I can read each columns separately, so I can replace the comma in the column 5 input with a character that is not used (may be such as ~), before I split column 5 input based on other than a comma that separates the columns. That way when I split based on the comma the input column 5 will not be splitted, then I can replace the ~ back into comma in the output after splitting.

     

    To get my point across, here is an example after reading what my data looks like:

    Column1,column2,column3,column4,colu,mn5,column6 -> since column5 contains a comma.

     

    If I split based on the comma then the result will be:

    Column1

    column2

    column3

    column4

    colu

    mn5

    column6

     

    Which is NOT what I want.

    Any suggestion is appreciated. Thank you.

    oemar01

    Friday, October 27, 2017 1:53 PM

All replies

  • Hope I can explain the satisfactorily.

    If any field in a csv file contains a comma as part of the field then the field is enclosed in double quotes.

    If you open the file with Notepad then you will see the double quotes. (Open notepad first and then set the filter to all files so you can see the csv file and then open it)

    Explanation of the code below:

    Reads one line from the csv file.

    Finds the first double quote in the line.

    Replaces the first comma after the double quote with a vertical line. Note that the line of code to do this must concatenate the left of the string up to the double quote with the string created by the Replace command because replace starts from the first double quote.

    I have included an optional line to remove the double quotes by replacing with null strings. This must be done after finding the double quotes and then replacing the following comma with a vertical line. I am suggesting that you leave this line commented out so you can see the double quotes in the output to the Immediate window and then uncomment the line and re-run with the double quotes removed.

    The resultant string is then split by the remaining commas and assigned to an array.

    The code will output the array contents to the Immediate window.

    Sub test()
        Dim FileNbr As Long
        Dim strPath As String
        Dim strFile As String
        Dim strTextRow As String
        Dim arrFields As Variant
        Dim i As Long
       
        FileNbr = 1     'Set to 1 for testing purposes
        strPath = ThisWorkbook.Path & "\"       'Edit to your path
        strFile = "Testing csv with data commas.csv"    'Edit to your file name
       
        Open strPath & strFile For Input As #FileNbr
       
        Line Input #FileNbr, strTextRow
       
        i = InStr(1, strTextRow, """") 'Find the first double quote
       
        'Next line will Replace first comma after double quote
        'Note Replace only returns text from the double quote to end
        'Need to concatenate left of the double quote with right from the double quote
        'Replaces the comma after first double quote with a vertical line
        strTextRow = Left(strTextRow, i - 1) & Replace(strTextRow, ",", "|", i, 1)
       
        'Next command line will remove the double quotes
        'Test without this row first so you can see the double quotes in the output
        'strTextRow = Replace(strTextRow, """", "")
       
        arrFields = Split(strTextRow, ",")
       
        For i = LBound(arrFields) To UBound(arrFields)
            Debug.Print arrFields(i)
        Next i
       
        Close #FileNbr
       
    End Sub


    Regards, OssieMac

    Saturday, October 28, 2017 7:09 AM
  • Hi oemar01,

    try to refer example below.

    CSV file looks like below.

    code:

    Sub demo()
    Dim FilePath As String
    FilePath = "C:\Users\Owner\VBA\authors.csv"
    Open "C:\Users\v-padee\Desktop\authors.csv" For Input As #1
    row_number = 0
    Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, ",")
    ActiveCell.Offset(row_number, 0).Value = LineItems(2)
    ActiveCell.Offset(row_number, 1).Value = LineItems(1)
    ActiveCell.Offset(row_number, 2).Value = LineItems(0)
    row_number = row_number + 1
    Loop
    Close #1
    
    
    End Sub
    

    Output:

    Reference:

    Open a Text File in Excel VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 30, 2017 8:59 AM
    Moderator
  • Hello Deepak Panchal10,

    I believe that the problem that the OP has is when there is a comma in the middle of one of the fields. The example that the OP provided is like the following where the fifth field has a comma in the middle of it.

    Column1,Column2,Column3,Column4,Colu,mn5,Column6

    The code example that you provided will split the 5th field into colu and mn5.

    With most applications, when a file is saved as csv and there is a comma in the middle of a field, the field is enclosed in double quotes like the following. You can save an example excel file with a comma in the middle of a field and then open the resulting csv file with Notepad and you will see the double quotes.

    Column1,Column2,Column3,Column4,"Colu,mn5",Column6

    The code I provided finds the double quotes and replaces the following comma with a vertical line so that the Split function will only split at the comma field delimiters.

    The simplified code that I provided only handles one field with a comma within the line of data. If there are multiple fields with commas included in the field then the code will need to be somewhat more extensive to identify all of the double quotes.


    Regards, OssieMac


    • Edited by OssieMac Monday, October 30, 2017 10:28 AM
    Monday, October 30, 2017 10:27 AM
  • Hi OssieMac,

    it looks like I misunderstand the op's requirement.

    I try to review the original post and find that your suggestion can solve the op's issue.

    I suggest oemar01 to check the suggestion given by OssieMac.

    (if you think that it can solve your issue then try to mark the suggestion given by the OssieMac as an answer.

    if you have any further question then let us know about that.

    we will try to provide further suggestion to solve the issue.)

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 31, 2017 1:22 AM
    Moderator