Create Word table with Excel data

Topic

New Reply

We download a report with hospital patient information every morning in .CSV format – see attached file (I had to change the file type to .txt to upload – original file extension is .csv) – and need to convert the data into a Word file with the data in tables as shown in the attached Word document. Note – examples have fictional data. I know how to store data in a text file. Is there an easy way to open a Word Document store the first patient name and then store the patient info in a table – one table per patient, each table with one row and three columns. And then repeat the pattern for each patient in the same file. End result is one file with one ‘header’ row and one table per patient. (note that the bolded titles in the Word file are the column headers in the .csv file.

Thanks!

Larry

Reply | Quote

Replies

Is there an easy way to open a Word Document store the first patient name and then store the patient info in a table – one table per patient, each table with one row and three columns.

No, there is no easy way to do what you want, because you want to change the order of the field information in the text file.
That being said, since your data is in the same order all the time, you could write a VBA routine to reorder the information in Excel, so that you could copy and paste into Word.

Reply | Quote

Is there an easy way to open a Word Document store the first patient name and then store the patient info in a table – one table per patient, each table with one row and three columns. And then repeat the pattern for each patient in the same file. End result is one file with one ‘header’ row and one table per patient. (note that the bolded titles in the Word file are the column headers in the .csv file.

Thanks!

Larry

Larry,

The short answer is NO…sorry. What you want can be accomplished with some VBA programming but the formatting you require to parse the single line per patient into two different cells of a table and each patient into a different table with a header will require some programming.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Larry,

From the forum/lounge in which you have posted your question, I’m guessing that the .csv file is an export from an Excel spreadsheet? Do you have access to the original Excel file from which the .csv was exported? If yes, then you should be able to construct the tables within this excel file from the raw data, using some formulae. If not, then you will first need to import the .csv file into an Excel file. Once you have the tables in Excel, putting them into Word will be a simple copy and paste (which could be automated). The only difficulty I can see is that you have a mixture of bold and plain fonts in some of the cells of your table. With Excel formulae it is not possible to switch between bold and plain fonts within a cell, mixing the fonts can be achieved using VBA macros.

I’ve made a stab at setting up the formulae in the attached Excel workbook. Sheet “Raw” is the raw data, in the attachment I set up the raw data by reading in from the .txt (.csv) file you provided. While doing this I found that some of the data in the text file is not in accord with some of the table entries in the Word document. I’ve also produced a VBA macro to do the bold formatting and to select the range which holds the report, ready to be copied and pasted into word. To run the macro just click on the button “Report” on sheet “Raw”.

Hope this helps,

RAK.

Reply | Quote

Larry

Do you have Microsoft Access? Your csv file would import straight into a table in Access without problems (although Disch Date appears to be missing in the sample provided) and the output could be produced in a simple Access report or, if you must have it in Word, via a Word mail merge. No VBA required.

David

Reply | Quote

We download a report with hospital patient information every morning in .CSV format – see attached file (I had to change the file type to .txt to upload – original file extension is .csv) – and need to convert the data into a Word file with the data in tables as shown in the attached Word document. Note – examples have fictional data. I know how to store data in a text file. Is there an easy way to open a Word Document store the first patient name and then store the patient info in a table – one table per patient, each table with one row and three columns. And then repeat the pattern for each patient in the same file. End result is one file with one ‘header’ row and one table per patient. (note that the bolded titles in the Word file are the column headers in the .csv file.

Thanks!

Larry

Hi Larry,

You could do this quite easily via a Catalog/Directory mailmerge in Word, using the raw txt/csv file as the data source. Basically all you need to do is to create the mailmerge main document with a line for the patient name, followed by a 1-row, 3-column table for the other data. When you execute the row, Word will then generate the desired layout from the data.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Good catch Paul – I had my blinders on.

Reply | Quote