VBA Excel : Extract data in specific format from CSV files

Question

I have different CSV files and these files have some raw data Computer Name,"Computer Serial","User name","Employee Number","Software name" followed by the below data.

added this from the linked file:

Comp;uter;"Name ";Computer;Seria;l""    User";"name""   Employee";"Number"" Software";"name"""
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"LiveUpdate 3.3 (Symantec Corporation)";;;;;;;;                
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Adobe SVG Viewer 3.0";;;;;;;;             
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Adobe Download Manager 2.0 (Supprimer uniquement)";;;;;;;;                
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"ATI - Utilitaire de dÃ©sinstallation du logiciel";;;;;;;;             
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"ATI Display Driver";;;;;;;;               
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"IBM iSeries Access for Windows";;;;;;;;               
DK4408XP0016,108081520001,"GAILLARD Alain",11014,DomusDraw;;;;;;;;              
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"NeXspan SoftPhone i2052 R3.1 D03";;;;;;;;             
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"DÃ©sinstallation du logiciel d''imprimante IBM";;;;;;;;               
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"DÃ©sinstallation du logiciel IBM";;;;;;;;             
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"CA 01 - le Offline MALL de Siemens Automation and Drives";;;;;;;;             
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Java Web Start";;;;;;;;               
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB873339";;;;;;;;              
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885250";;;;;;;;              
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885835";;;;;;;;              
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB885836";;;;;;;;              
DK4408XP0016,108081520001,"GAILLARD Alain",11014,"Correctif Windows XP - KB886185";;;;;;;;

I never worked with Excel VBA before, this is the first time I gonna work on this. I started working on some examples to create & run VBA code in Excel.

Some one please help me out to proceed with this, I want to create VBA code to extract the raw data & put into the following format.

CompName    ComputerSerial  UserName    EmpNo   SoftwareName

DK4408XP0016 1108081520001  GAILLARD Alain  11014   LiveUpdate 3.3 (Symantec Corporation)
DK4408XP0016 1108081520001  GAILLARD Alain  11014   Adobe SVG Viewer 3.0

I checked this link Code for looping through all excel files in a specified folder, and pulling data from specific cells & it has an information about "excel loop through files in folder" but this is not what I'm looking for.

I guess, what I need to do here, remove the special characters like , "" ;from the file & then format. I simply don't know how to proceed with this.

Is there any tool to extract data from .CSV files??? I need some suggestion, idea or some good examples for my problem and it will be really helpful for me.

I'm sharing one of my files here: http://uploadmb.com/freeuploadservice.php?uploadmbID=1323960039&srv=www&filename=4408_NANTES_softwares.csv

The format of your example file does not really look like what you posted here: `Comp;uter;"Name,""";Computer;Seria;"l"",""User";"name"",""Employee";"Number"",""Software";"name""" "DK4408XP0016,108081520001,""GAILLARD Alain"",11014,""LiveUpdate 3.3 (Symantec Corporation)""";;;;;;;;` So which one is it?? — Jean-François Corbett, Dec 15 '11 at 16:49
@ Jean-François Corbett : you are correct. I have many files like this. Now I've added the sample into the question. — linguini, Dec 15 '11 at 17:33

score 1 · Answer 1 · answered Dec 15 '11 at 14:20

you have a lot of choise to realize this.

If your operation are an exception (just for today), you can use the function of import and convert CSV file of Excel.

Open your Excel, in the toolbar, just click on Data/Convert.

If you want to put a task like macro, you can take example by this script :

Public Sub IsValid()
Dim i As Long
Dim valueTemp As String 'Chaine de caractere
Dim wsTemp As Worksheet 'Feuille
Dim rTemp As Range 'Range

'Variable initialise a 1
i = 1
Set wsTemp = ThisWorkbook.Worksheets(NameFileResult)

While (Ws_Result.Cells(i, 1) <> "")
    valueTemp = Ws_Result.Cells(i, 1)

    With ThisWorkbook 'ton fichier dans lequel tu fais ta recherche
        Set rTemp = wsTemp.Range("A:D").Find(valueTemp, , xlValues, xlWhole, , , False)
        If Not rTemp Is Nothing Then
            wsTemp.Rows(rTemp.Row).Interior.ColorIndex = 4 'Vert si la donnees est disponible
            wsTemp.Rows(rTemp.Row).Copy (Ws_Index.Rows(15))
            wsTemp.Rows(1).Copy (Ws_Index.Rows(14))
        Else
            Ws_Index.Rows(15).Clear
            Ws_Index.Rows(14).Clear
            Ws_Index.Cells(15, 5).Value = NameMsgBoxNoFind
            Ws_Index.Rows(15).Interior.ColorIndex = 3
        End If
    End With
    i = i + 1
Wend

End Sub

Thanks for you support. I can open all the files in Excel without any problem but the data is not classified so I want to run a macro. When I tried with Data/Convert nothing is happening. I have shared one file here [URL=http://uploadmb.com/freeuploadservice.php?uploadmbID=1323959598&srv=www&filename=4408_NANTES_softwares.csv]4408_NANTES_softwares.csv[/URL] — linguini, Dec 15 '11 at 14:36

score 1 · Accepted Answer · answered Dec 15 '11 at 20:17

1

This works for your example file:

' Open the csv file as a text file
Workbooks.OpenText Filename:="C:\4408_NANTES softwares.csv"

Excel sometimes parses CSV files automatically, but sometimes not; I can't figure out the pattern. So you can add the following to ensure that it gets parsed properly:

' Parse it using comma and semicolon as delimiters
Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=True, Space:=False, Other:=False, _
    FieldInfo:= _
    Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))

The FieldInfo bit may look a bit cryptic, but the only thing it does is specify that your fields are to be treated as text (mostly to avoid your serial number 108081520001 being formated in scientific notation).

answered Dec 15 '11 at 20:17

Jean-François Corbett

37,420
30
139
188

I'm sure your code will work. I have a question, First I parse the file to remove the comma and semicolon as delimiters then I use your above code, am i right? – linguini Dec 15 '11 at 20:51
No! If you remove the delimiters, then how will Excel know where each column starts and ends? Just use the code above to open and parse the file. I tested it. – Jean-François Corbett Dec 16 '11 at 07:33
It works. What should i do if i want to have any three fields like computerName, UserName, SoftwareName? Is it possible to change the SoftwareName as some other name like "Application"?? what is this array "Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))"Thanks for your help. – linguini Dec 16 '11 at 08:13
Regarding your last question about the Array, please read the last sentence of my answer, as well as the VBA help file for `TextToColumns` and look for a description of the `FieldInfo` argument. – Jean-François Corbett Dec 16 '11 at 09:08
What do you mean by you "want" those fields? This sounds like it's a different issue, and therefore you should probably post it as a new question. – Jean-François Corbett Dec 16 '11 at 09:09
ok, I got the explanation. Is it possible to pick only three fields? As you see, I have these fields'Computer Name' 'Computer Serial' 'User name' 'Employee Number''Software name', can I have only these three fields "Computer Name,Employee Number,Software name" from the file?? – linguini Dec 16 '11 at 09:21
After loading the file, you could just delete the columns you don't want. – Jean-François Corbett Dec 16 '11 at 09:23

score 0 · Answer 3 · answered Aug 19 '16 at 16:02

I know this is an old thread, but I had to do this also and I thought my solution might help future users. If there is a zero length element (e.g. elem1,elem2,,elem4) then the array value will = "". It has worked very well for me.

Function ParseLineEntry(LineEntry As String) As Variant
'This function takes a .CSV line entry as argument and parses it into an array of each element.

Dim NumFields As Integer, LastFieldStart As Integer
Dim LineFieldArray() As Long
Dim i As Long, j As Long

'Determine how many delimitations there are. There will always be at least one field
NumFields = 1
For I = 1 To Len(LineEntry)
    If Mid(LineEntry, i, 1) = "," Then NumFields = NumFields + 1
Next I
ReDim LineFieldArray(1 To NumFields)

'Parse out each element from the string and assign it into the appropriate array value
LastFieldStart = 1
For i = 1 To NumFields
    For j = LastFieldStart To Len(LineEntry)
        If Mid(LineEntry, j, 1) = "," Then
            LineFieldArray(i) = Mid(LineEntry, LastFieldStart, j - LastFieldStart)
            LastFieldStart = j + 1
            Exit For
        End If
    Next j
Next i

ParseLineEntry = LineFieldArray
End Function

VBA Excel : Extract data in specific format from CSV files

3 Answers3

Linked