Importing specific areas of a csv file in certain columns

[hakantoz]

Hello,

I am very new to the excel VBA and using macro and need some help.

I have a software that exports files in csv format and with a format as the following.
[TABLE="width: 380"]
<tbody>[TR]
[TD="width: 380"][/TD]
[/TR]
</tbody>[/TABLE]
PN:NB%SN:7A1350Y00369%MAC:00C0B70000
PN:NB%SN:7A1350Y00329%MAC:00C0B70012
PN:NB%SN:7A1350Y00360%MAC:00C0B70003

I created three columns PN , SN and MAC

And I need to create a button in excel sheet for user to select which file they would like to import (csv or text file) and excel somehow needs to detect and auto sort under the columns PN, SN and Mac automatically with the numbers in the file. It will look something similar like below once they are sorted after the file selection

PN SN MAC
NB 7A1350Y00369 00C0B70000
NB 7A1350Y00329 00C0B70012
NB 7A1350Y00360 00C0B70003


Thank you for all the help

 

[VBA Geek]

Sub splityu()
Dim j As Long: j = 1
Dim v As Variant
While Not Range("A" & j) = ""
    v = Split(Range("A" & j), ":")
    Range("C" & j & ":E" & j).Value = Array(Replace(v(1), "%SN", ""), Replace(v(2), "%MAC", ""), v(3))
    j = j + 1
Wend
End Sub

 

[hakantoz]

Sub splityu()
Dim j As Long: j = 1
Dim v As Variant
While Not Range("A" & j) = ""
    v = Split(Range("A" & j), ":")
    Range("C" & j & ":E" & j).Value = Array(Replace(v(1), "%SN", ""), Replace(v(2), "%MAC", ""), v(3))
    j = j + 1
Wend
End Sub

I entered the following code hoping it would work but was not successful. Does the code include a button to be able to select file and import it to the excel sheet?

 

[VBA Geek]

from your query i thought you'd wanna convert

PN:NB%SN:7A1350Y00369%MAC:00C0B70000
PN:NB%SN:7A1350Y00329%MAC:00C0B70012
PN:NB%SN:7A1350Y00360%MAC:00C0B70003

into

NB 7A1350Y00369 00C0B70000
NB 7A1350Y00329 00C0B70012
NB 7A1350Y00360 00C0B70003

 

[hakantoz]

I just need to create a button on the screen that when I press on it it will basically ask me which file I would like to select (cvs or txt file) and then it needs to do the sorting automatically to corresponding columns depending on what the file has. When I tried to input the code and run it manually it did not ask me which file I am trying to pull the information from.

I am really sorry but I am really new in this and I really appreciate all your help.

 

[VBA Geek]

so you want to have a pop up box where you can choose a file to import?

 

[hakantoz]

it can also be a button on the screen. imagine I have three columns already generated with names PN, SN and MAC . and on the side there will be a button where we can click on and lets us select the csv or txt file which have multiple similar data as below.
PN:NB%SN:7A1350Y00369%MAC:00C0B70000
PN:NB%SN:7A1350Y00329%MAC:00C0B70012
PN:NB%SN:7A1350Y00360%MAC:00C0B70003
PN:NB%SN:7A1350Y00369%MAC:00C0B70000
PN:NB%SN:7A1350Y00329%MAC:00C0B70012
PN:NB%SN:7A1350Y00360%MAC:00C0B70003

After selecting the file excel will basically look inside the file and extract the necessary data and put it under the right columns.

 

[VBA Geek]

Sub GG()
Dim data As String
Dim G As Variant
Dim j As Long: j = 2
Dim v As Variant
Dim fn As Integer: fn = FreeFile
G = Application.GetOpenFilename("Text Files And CSV Files, *.txt; *.csv", , "Select a File", , False)
    If G = False Then Exit Sub
Open G For Input As #fn
    While Not EOF(fn)
        Input #fn, data
        If Not data = "" Then
            v = Split(data, ":")
            Range("A" & j & ":C" & j).Value = Array(Replace(v(1), "%SN", ""), Replace(v(2), "%MAC", ""), v(3))
            j = j + 1
        End If
    Wend
Close #fn
End Sub

 

[hakantoz]

Sub GG()
Dim data As String
Dim G As Variant
Dim j As Long: j = 2
Dim v As Variant
Dim fn As Integer: fn = FreeFile
G = Application.GetOpenFilename("Text Files And CSV Files, *.txt; *.csv", , "Select a File", , False)
    If G = False Then Exit Sub
Open G For Input As #fn
    While Not EOF(fn)
        Input #fn, data
        If Not data = "" Then
            v = Split(data, ":")
            Range("A" & j & ":C" & j).Value = Array(Replace(v(1), "%SN", ""), Replace(v(2), "%MAC", ""), v(3))
            j = j + 1
        End If
    Wend
Close #fn
End Sub

Hello VBAGeek,

First of all thank you very much helping me out with my problem.

I am trying to use the code you have written and I am getting a Run-time error '9' , Subscript out of range error on line :

Range("A" & j & ":C" & j).Value = Array(Replace(v(1), "%SN", ""), Replace(v(2), "%MAC", ""), v(3))


I have gone and checked the document I am trying to import and looks like the formatting is little bit different. The file I am trying to import have the following layout on the csv when I open it in excel.

A B C
[TABLE="width: 492"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name
[/TD]
[TD] Text
[/TD]
[TD] Date
[/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD]PN:NBPD0180%SN:7A1350Y00340%MAC:00C0B70000821523
[/TD]
[TD="align: right"]2/28/2014[/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD]PN:NBPD0180%SN:7A1350Y00508%MAC:00C0B700008215CB[/TD]
[TD="align: right"]2/28/2014[/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD]PN:NBPD0180%SN:7A1350Y00369%MAC:00C0B70000821540[/TD]
[TD="align: right"]2/28/2014[/TD]
[/TR]
</tbody>[/TABLE]


I hope these help so we can debug it

 

[VBA Geek]

Can you show me your actual csv file and the expected output after import it