Have you ever tried converting CSV files directly to PDFs using Excel or other tools? If so, you know this can be problematic. The problems include:
- ● No formatting
- ● No fancy graphs or advanced Excel functionality to display content meaningfully
- ● Column widths often aren't properly sized, resulting in truncated content and characters. This issue is illustrated below, where the output PDF suffers from cut-off content due to default column widths.
To overcome all these challenges, this blog post will guide you through converting a CSV file to a well-formatted PDF using the Softinterface app 'Convert XLS'. As motivation to keep reading, the final output.pdf will look like this when we're done:
NOTE: The final results of all the steps below, including input files, output files, and the conversion job required to reproduce the results are ALL included with the installation for 'Convert XLS'. Simply download, install, and go to this file location to try it out for yourself:
%appdata%\Softinterface, Inc\Convert XLS\Demo Conversion Jobs\CSV-to-PDF-Demo#OPEN.SII
If you need any assistance getting set up a similar operation, do not hesitate to reach out to by email to: .
The process requires some preliminary setup. We'll utilize an Excel worksheet, referred to as template.xlsx, as a pre-formatted template. You'll need to manually create template.xlsx within MS Excel, adjusting column widths, headers, and adding formulas and graphs as needed. Fortunately, this template only needs to be created once, as 'Convert XLS' can reuse it for future conversions.
The next 3 steps done within the 'Convert XLS' app are:
- Clear out a template.xlsx file. The template.xlsx can be continually re-used for similar jobs. Because of this, clearing the values within template.xlsx prior to filling it with new data (from the csv file) will ensure the output.pdf will only contain fresh data we want. In other words, clearing out template.xlsx ensures output.pdf will not have old data from previous operations.
- Copy the input.csv data into template.xlsx by using the app's 'Special Process 101'
- Convert template.xlsx to output.pdf.
Prepare a template.xlsx file
You may already have exactly what you want in terms of template.xlsx file. If so great, you can skip to the next section. Otherwise lets make one ourselves.
Pro tip: Use the File\Print\Show Preview\ menu within Excel to make final adjustments to page margins, vertical and horizontal dimensions, etc.
If you starting from scratch, run MS Excel, and load the CSV file in it (File\Open menu - select *.* all files). Adjust the columns, add formula's, graphs, formatting to make it exactly as you wish your output PDF to look like. Next, save the template.xlsx from the File\Save menu item.
If you are not starting from scratch, you'll want to open your template.xlsx file and import the csv file data.
- Open your MS Excel worksheet.
- Click on the "Data" tab in the ribbon.
- Click on "From Text/CSV" in the "Get & Transform Data" group.
- In the "Import Data" dialog box, navigate to the location of your CSV file and select it.
- Follow the prompts to select the delimiter and the cell to place to the data in.
Again, make the template.xlsx file exactly how you would like your output.pdf file to look.
Be sure to save template.xlsx to a known location, as we'll be referring to it later.
[1] Clear out template.xlsx values
The next steps will all occur within the 'Convert XLS' app.
1. Under "Select Action To Do", select "Special Processing on Files"
2. In "Special Processing", select [112] (*.XLS) Clear Cell Contents.
3.
Click on the input file and specify template.xlsx.
4.
Click on the output file and specify the same file.
5.
Click the Add button to add a task. The task list,
at the bottom of the user interface, should now have one entry as shown below.
[2] Copy the CSV data to the template file
1. Under "Select Action To Do", select "Special Processing on Files"
2.Select [101] (*.XLS) copy specified data from sheet to the same or
different XLS file. Note that typically special process 101 is
used with an Excel workbook file (XLS, XLSX), it works perfectly fine with CSV
files.
3.
Click on the input file and specify the input CSV file.
4.
Click on Output File and specify template.xlsx.
5. Click on the Click here for 'Copy Sheet Details' button to specify how special process will work. In this case, we want to skip the first line (the header) of the CSV file by specifying the range to copy from (A2:D100000), and we also want to place the data starting at cell A2.
6.
Click the Add button to add the task and confirm it has
been added to the task list.
[3] Convert template.xlsx to output.pdf
1. Under "Select Action To Do", select Convert Files
2.Select MS Excel as the conversion method.
3.
Click on the input file and specify template.xlsx.
4.
Select [-1] PDF (*.PDF) as the output file type.
Click on Output File and specify an output file name, in this case we chose
C:\In\AC-REPORT-60.PDF
5.
Click the Add button to add the task and confirm it has
been added to the task list.
6.
Click
the Convert button to begin the conversion job.
That is all, you are done! Converting from CSV to PDF with formatting remaining intact, is not so hard after all.
Additional notes
1. If you want to recall this conversion job at a later date, go to the File\Save Conversion Job menu item and save it. Note, this conversion job can be easily used from the command line.
2.There may be a case where you want to add several csv files to the template.xlsx file before converting it to a pdf. Do this by repeating special process 101. Note the settings in this case for special process 101 in the image below, where we tell 'Convert XLS' to place the data below the last used cell. You can adjust to your specific case.
Conclusion: 'Convert XLS' can automate your CSV to PDF conversion processes
'Convert XLS' can very quickly be set up to convert your CSV files to a highly formatted PDF file. A one time setup of a template.xlsx fle, and creation of the conversion job as shown above is all that is needed to automate this process. Once in a conversion job file, it can be easily recalled, or executed from the command line.
