importing csv file data into specific fields in a worksheet

[Nigi Umboogu]

Hi, I am a novice at excel and macros etc so need some real help if possible.

I have a .csv file with data in it which I need to import into a blank master sheet.

however, I need to populate the master sheet fields with specific fields from the .csv file. e.g first field in .csv file goes to column D in master sheet, 2nd to Col A, 3rd to Col B etc.

Can anyone tell me how to achieve this?

Thanks in advance

 

[Ruddles]

Go through the steps of importing the file manually and rearranging the columns using Cut > Insert Cut Cells.

When you've worked out exactly how you want the sheet arranged, repeat the process from scratch with the macro recorder running. This will generate code to allow you to repeat the process in the future.

If you post that code here, I suspect there will be plenty of volunteers who will be happy to optimise it for you and maybe add a file-open dialog box front end.

 

[Nigi Umboogu]

hi ruddles,

Thanks for your reply. I had started doing that but got bored! lol
Plus, it is for a "client" so the file locations and final master file etc will be slightly different.

Was hoping to find a more generic way of executing it!

Guess I'll give that ok again then

thanks again

 

[Domenic]

The following macro prompts the user to select a .csv file, and then imports the data into the active sheet...

If the .csv consists of only three fields, try...

Option Explicit

Sub test()

    Dim FileName, Field1, Field2, Field3
    Dim NextRow As Long
    
    FileName = Application.GetOpenFilename( _
        FileFilter:="Comma Separated Files (*.csv), *.csv", _
        FilterIndex:=1, _
        Title:="Select a File")
        
    If FileName = False Then Exit Sub

    Open FileName For Input As #1
    
    NextRow = 2
    Do Until EOF(1)
        Input #1, Field1, Field2, Field3
        Cells(NextRow, "D").Value = Field1
        Cells(NextRow, "A").Value = Field2
        Cells(NextRow, "B").Value = Field3
        NextRow = NextRow + 1
    Loop
    
    Close #1
    
End Sub

Otherwise, try the following instead...

Option Explicit

Sub test2()

    Dim NextRow As Long
    Dim Data As String
    Dim FileName, x
    
    FileName = Application.GetOpenFilename( _
        FileFilter:="Comma Separated Files (*.csv), *.csv", _
        FilterIndex:=1, _
        Title:="Select a File")
        
    If FileName = False Then Exit Sub

    Open FileName For Input As #1
    
    NextRow = 2
    Do Until EOF(1)
        Line Input #1, Data
        x = Split(Data, ",")
        Cells(NextRow, "D").Value = x(0)
        Cells(NextRow, "A").Value = x(1)
        Cells(NextRow, "B").Value = x(2)
        NextRow = NextRow + 1
    Loop
    
    Close #1
    
End Sub

 

[Nigi Umboogu]

Hey Domenic,

That's brilliant thanks for that! I will test it out (and edit as required)

Really appreciate the help tho. Will let you know how I get on

Nigi

 

[Nigi Umboogu]

Hey Domenic,

That worked perfectly (with some tweaking to specify the fields and to skip the first line)

Now, how do I create a button on the master sheet that will call the Macro? :D

 

[Nigi Umboogu]

Hey, it's ok, I figured it out!! :D

thanks again guys, great help!

 

[Ruddles]

Was hoping to find a more generic way of executing it!

That's why I suggested posting the code here - so that we could optimise it and add a file-open dialog box front end.

But I see you're sorted now.