Export Excel CSV to MS word with specific table format

O

originalsauce

New Member
Joined
Jun 27, 2019
Messages
5
[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]
FPofRQk.jpg
[[/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]
FPofRQk
[/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]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
originalsauce said:
Slowly getting there trying to get an understanding of i + r + 1 is proving difficult, where is information for this looping command online?
Click to expand...
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.
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Copying whole word document, into excel + formatting columns and cells
Replies
1
Views
259
Macropod
Macropod
H
  • Question Question
Excel to Word table creation - formatting issue
Replies
0
Views
402
hannerz78
H
R
  • Question Question
Word Mail Merge - Using specific tables in Excel rather than the whole worksheet
Replies
5
Views
595
RichCowell
R
A
  • Question Question
MS Word macro to find text from Excel list in Word doc, then copy and paste chunks from Word doc to new Word doc
Replies
3
Views
2K
amaneta67
A
P
  • Question Question
Copy an excel table and paste it into word
Replies
2
Views
961
Paskie_EU
P

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top