Export Excel CSV to MS word with specific table format

originalsauce

New Member
[FONT=&quot]Im a bit out of my comfort zone with excel so was wondering if someone can point me in the right direction of how this could be accomplished?[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]We have a CSV file that contains multiple columns, some rows however have additional columns that need to be inside that specific row ONLY and not create additional rows.. i need to export this data into a word document table like so (pic attached).[/FONT]
[FONT=&quot]
[[/FONT]

[FONT=&quot]example attached (pic) - is that if there are additional collateral types and descriptions for the same FS number, split the column for the row instead in the word table.[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]basically i need to get the excel data into a table like that into a word table.. is that possible?[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]is there any way to accomplish this?[/FONT]
 

John_w

MrExcel MVP
This is a Word VBA macro which imports the .csv file into the current Word document, so put the code in a Word module, not an Excel module.

Code:
Public Sub Create_Table_From_Csv_File()

    Dim csvFile As String
    Dim FSO As Object
    Dim csvTextStream As Object
    Dim csvLines As Variant
    Dim csvData As Variant
    Dim csvTable As Table
    Dim i As Long, r As Long, c As Long
    
    csvFile = "C:\folder\path\Your csv data.csv"   'CHANGE THIS
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set csvTextStream = FSO.OpenTextFile(csvFile)
    csvLines = Split(csvTextStream.ReadAll, vbCrLf)
    csvTextStream.Close
    
    ReDim csvData(0 To UBound(csvLines) - 1)
    For i = 0 To UBound(csvLines) - 1
        csvData(i) = Split(csvLines(i), ",")
    Next
    
    Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=4, _
                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
    With csvTable
    
        .Cell(1, 1).Range.Text = csvData(0)(0)   'FS Number
        .Cell(1, 2).Range.Text = csvData(0)(1)   'Reg
        .Cell(1, 3).Range.Text = csvData(0)(3)   'Collateral type
        .Cell(1, 4).Range.Text = csvData(0)(4)   'Collateral description

        r = 0
        For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            If UBound(csvData(i)) > 4 Then
                'Split this row (i+r+1) columns 3 and 4 into multiple rows
                .Cell(i + r + 1, 3).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 4).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
            End If
            For c = 3 To UBound(csvData(i)) Step 2
                .Cell(i + r + 1, 3).Range.Text = csvData(i)(c)
                .Cell(i + r + 1, 4).Range.Text = csvData(i)(c + 1)
                r = r + 1
            Next
            r = r - 1
        Next
        
    End With

    MsgBox "Done!"
    
End Sub
 

originalsauce

New Member
Hi John, that's really nearly perfect thank you!

i'm struggling with understanding the rangetext and .cell part as in the real version there are 10 columns and the possibility of columns 8,9,10 with the same headers repeating more times for the same row.

the repeating columns are Collateral Type, Collateral Description and Item(s).

I tried to fiddle with the numbers but it doesnt work for me and get a debug!



Where can i figure out what the numbers mean and what column/row they are referencing?

I understand a few parts to change but not the numbers in specific parts

Code:
    Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines),[COLOR=#00ffff][U] [B]NumColumns:=10[/B],[/U][/COLOR] _                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
    With csvTable
    
        .Cell(1, 1).Range.Text = csvData(0)(0)   'FS Number
        .Cell(1, 2).Range.Text = csvData(0)(1)   'Reg
        .Cell(1, 3).Range.Text = csvData(0)(3)   'Collateral type
        .Cell(1, 4).Range.Text = csvData(0)(4)   'Collateral description
[B][COLOR=#00ffff].cell(1,5).range.text = Csvdata(0)(5) 'Column name ??[/COLOR][/B]


        r = 0
        For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            If UBound(csvData(i)) > 4 Then
                'Split this row (i+r+1) columns 3 and 4 into multiple rows
                .Cell(i + r + 1, 3).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 4).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
[COLOR=#00ffff]This bit i am not understanding :)[/COLOR]
            End If
            For c = 3 To UBound(csvData(i)) Step 2
                .Cell(i + r + 1, 3).Range.Text = csvData(i)(c)
                .Cell(i + r + 1, 4).Range.Text = csvData(i)(c + 1)
                r = r + 1
            Next
            r = r - 1
 

John_w

MrExcel MVP
The .Cell syntax is .Cell(row, column). Add the numbers and variables either side of the comma to determine which row or cell they are referencing. When a Cell is Split, the number of rows in the table increases by NumRows (or it might be NumRows-1 - I don't know without checking), hence the need for the i + r + 1 addition.

NumColumns specifies the number of columns in the new Word table, so specifying NumColumns:=10 will create the Word table with 10 columns; is that what you want? You seem to be saying that it should be 5 columns, with "Item(s)" as the 5th column, so shouldn't it be NumColumns:=5?

For your repeating "Item(s)" column as the 5th column in the table, try adding (untested):

Code:
.Cell(i + r + 1, 5).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
and in the For Next loop:
Code:
                .Cell(i + r + 1, 5).Range.Text = csvData(i)(c + 2)
 

originalsauce

New Member
Slowly getting there trying to get an understanding of i + r + 1 is proving difficult, where is information for this looping command online?

so to confirm i have 10 columns, columns 8,9,10 can repeat so there may be further information for certain rows with data in 11,12,13 , 14,15,16 etc that need to be looped from if exist for that row so it creates one column for all collateral type, description and items.

the editing i have done is this but it debugs on me i assume as i have the looping bit syntax slightly wrong :)

Code:
    Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
    With csvTable[SUP][/SUP]
    
        .Cell(1, 1).Range.Text = csvData(0)(0)   'Fin
        .Cell(1, 2).Range.Text = csvData(0)(1)   'P
        .Cell(1, 3).Range.Text = csvData(0)(3)   'P2
        .Cell(1, 4).Range.Text = csvData(0)(4)   'Expiry Date and Time
        .Cell(1, 5).Range.Text = csvData(0)(5)   'Status
        .Cell(1, 6).Range.Text = csvData(0)(6)   'D
        .Cell(1, 7).Range.Text = csvData(0)(7)   'Secured
        .Cell(1, 8).Range.Text = csvData(0)(8)   'Collateral Type
        .Cell(1, 9).Range.Text = csvData(0)(9)   'Collateral description
        .Cell(1, 10).Range.Text = csvData(0)(10)  'Items






        r = 0
        For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            If UBound(csvData(i)) > 7 Then
                'Split this row (i+r+1) columns 3 and 4 into multiple rows
                .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
            End If
            For c = 3 To UBound(csvData(i)) Step 2
                .Cell(i + r + 1, 8).Range.Text = csvData(i)(c)
                .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 2)
                .Cell(i + r + 1, 10).Range.Text = csvData(i)(c + 3)

When trying your amended looping bit just for one column (7) it debugs on this line

Code:
       For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            If UBound(csvData(i)) > 7 Then
                'Split this row (i+r+1) columns 3 and 4 into multiple rows
            
            .Cell(i + r + 1, 7).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
            End If
            For c = 3 To UBound(csvData(i)) Step 2
            
[B]                .Cell(i + r + 1, 7).Range.Text = csvData(i)(c + 2)[/B]
                
                r = r + 1
            Next
            r = r - 1
        Next
        
    End With


    MsgBox "Done!"
    
End Sub
 

John_w

MrExcel MVP
Slowly getting there trying to get an understanding of i + r + 1 is proving difficult, where is information for this looping command online?
That isn't a loop; it's a simple addition, accounting for the rows added when a row is split - the r variable is incremented in the For c loop.

so to confirm i have 10 columns, columns 8,9,10 can repeat so there may be further information for certain rows with data in 11,12,13 , 14,15,16 etc that need to be looped from if exist for that row so it creates one column for all collateral type, description and items.
But this part of your code implies that column numbers 9, 10, 11 in the .csv file can repeat:


Code:
    With csvTable
    
        .Cell(1, 1).Range.Text = csvData(0)(0)   'Fin
        .Cell(1, 2).Range.Text = csvData(0)(1)   'P
        .Cell(1, 3).Range.Text = csvData(0)(3)   'P2
        .Cell(1, 4).Range.Text = csvData(0)(4)   'Expiry Date and Time
        .Cell(1, 5).Range.Text = csvData(0)(5)   'Status
        .Cell(1, 6).Range.Text = csvData(0)(6)   'D
        .Cell(1, 7).Range.Text = csvData(0)(7)   'Secured
        .Cell(1, 8).Range.Text = csvData(0)(8)   'Collateral Type
        .Cell(1, 9).Range.Text = csvData(0)(9)   'Collateral description
        .Cell(1, 10).Range.Text = csvData(0)(10)  'Items
The csv rows and columns are indexed starting at 0. csvData(0)(0) is row 1, column 1 in the csv data, csvData(0)(1) is row 1, column 2, csvData(0)(8) is row 1, column 9 (not column 8). The Word table rows and columns are indexed starting at 1 - .Cell(1, 1) is row 1, column 1 in the table, .Cell(1, 2) is row 1, column 2 in the table. You have omitted csvData(0)(2) in the above code - is that intentional?

Can you clarify exactly what the repeating column numbers are in the csv data? The first column being column 1.
 

originalsauce

New Member
Thanks John, I have correct the columns like so:
Code:
        .Cell(1, 1).Range.Text = csvData(0)(0)   'Row 1 column 1
        .Cell(1, 2).Range.Text = csvData(0)(1)   'Row 1 column 2
        .Cell(1, 3).Range.Text = csvData(0)(2)   ' Row 1 column 3
        .Cell(1, 4).Range.Text = csvData(0)(3)   'Row 1 column 4
        .Cell(1, 5).Range.Text = csvData(0)(4)   'Row 1 column 5
        .Cell(1, 6).Range.Text = csvData(0)(5)   'Row 1 column 6
        .Cell(1, 7).Range.Text = csvData(0)(6)   'Row 1 column 7
        .Cell(1, 8).Range.Text = csvData(0)(7)   'Row 1 column 8
        .Cell(1, 9).Range.Text = csvData(0)(8)   'Row 1 column 9
        .Cell(1, 10).Range.Text = csvData(0)(9)   'Row 1 column 10
in Excel , Column 8,9,10 are always present, but the additional data for the same columns could be in 11,12,13.
I have amended as above , does that look ok?

I have adjusted this bit i believe would now be 10?

Code:
    Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), [B]NumColumns:=10,[/B] _
                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
but this section of code i think needs amendment?, not i have tried guessing the correct numbers but no luck ..
Code:
          r = 0
        For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            If UBound(csvData(i)) > 10 Then
                'Split this row (i+r+1) columns 3 and 4 into multiple rows
                .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
                .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
            End If
            For c = 8 To UBound(csvData(i)) Step 2
                .Cell(i + r + 1, 9).Range.Text = csvData(i)(c)
                .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 1)
                r = r + 1
            Next
            r = r - 1
        Next

Thank you very much for your help so far
 

John_w

MrExcel MVP
Try this macro:
Code:
Public Sub Create_Table_From_Csv_File()

    Dim csvFile As String
    Dim FSO As Object
    Dim csvTextStream As Object
    Dim csvLines As Variant
    Dim csvData As Variant
    Dim csvTable As Table
    Dim i As Long, r As Long, c As Long
    
    csvFile = "C:\folder\path\Your csv data.csv"   'CHANGE THIS
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set csvTextStream = FSO.OpenTextFile(csvFile)
    csvLines = Split(csvTextStream.ReadAll, vbCrLf)
    csvTextStream.Close
    
    ReDim csvData(0 To UBound(csvLines) - 1)
    For i = 0 To UBound(csvLines) - 1
        csvData(i) = Split(csvLines(i), ",")
    Next
    
    Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _
                              DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
    
    With csvTable
    
        .Cell(1, 1).Range.Text = csvData(0)(0)   'Row 1 column 1
        .Cell(1, 2).Range.Text = csvData(0)(1)   'Row 1 column 2
        .Cell(1, 3).Range.Text = csvData(0)(2)   'Row 1 column 3
        .Cell(1, 4).Range.Text = csvData(0)(3)   'Row 1 column 4
        .Cell(1, 5).Range.Text = csvData(0)(4)   'Row 1 column 5
        .Cell(1, 6).Range.Text = csvData(0)(5)   'Row 1 column 6
        .Cell(1, 7).Range.Text = csvData(0)(6)   'Row 1 column 7
        .Cell(1, 8).Range.Text = csvData(0)(7)   'Row 1 column 8
        .Cell(1, 9).Range.Text = csvData(0)(8)   'Row 1 column 9
        .Cell(1, 10).Range.Text = csvData(0)(9)  'Row 1 column 10

        r = 0
        For i = 1 To UBound(csvData)
            .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
            .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
            .Cell(i + r + 1, 3).Range.Text = csvData(i)(2)
            .Cell(i + r + 1, 4).Range.Text = csvData(i)(3)
            .Cell(i + r + 1, 5).Range.Text = csvData(i)(4)
            .Cell(i + r + 1, 6).Range.Text = csvData(i)(5)
            .Cell(i + r + 1, 7).Range.Text = csvData(i)(6)
            If UBound(csvData(i)) > 7 Then
                'Split this row (i+r+1) columns 8, 9 and 10 into multiple rows
                .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
                .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
                .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
            End If
            For c = 7 To UBound(csvData(i)) Step 3
                .Cell(i + r + 1, 8).Range.Text = csvData(i)(c)
                .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 1)
                .Cell(i + r + 1, 10).Range.Text = csvData(i)(c + 2)
                r = r + 1
            Next
            r = r - 1
        Next
        
    End With

    MsgBox "Done!"
    
End Sub
 

originalsauce

New Member
wow that's incredible thanks John. it works a treat and definately better than i managed by a mile!

It does seem to create 3 columns and rows extra for each cell after column 10, if theres a quick fix to only create those cells past column 10 if there is data inside the next column that would be amazing - is that possible?

if not, no worries at all, i am blown away by your excellent help.:)
 

montyfern

New Member
GENIUS! Thanks originalsauce & John_w! I had to tweak the code a bit, only have 10 columns, & kept bombing out on the last c = 7 To Unbound part, but modified to reflect splitting rows above from 11-13 & then it worked. Wow! Didn't spend too much time on this & your help's invaluable. GRAZIE
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top