Converting CSV data into multiple PDF files

What I am looking for boils down to being able to import customer order data in csv format, and be able to export individual orders into PDF file.

I currently have a bunch of data that is in cvs format, and I would like to be able to convert it into multiple pdf files. I would like each one of those files to have the same header and footer, but the information that fills the rest of the file would come from the csv format. Here is list of csv data that would make up 3 different files:

order #,date,name,address,state,zip,item #,unit price,quantity,shipping,total
1,3-Feb,Bob Smith,311 4th St,FL,34885,5461,0.33,5,7.99,20.44
1,3-Feb,Bob Smith,311 4th St,FL,34885,4451,0.66,7,7.99,20.44
1,3-Feb,Bob Smith,311 4th St,FL,34885,1126,0.42,9,7.99,20.44
1,3-Feb,Bob Smith,311 4th St,FL,34885,2254,0.12,20,7.99,20.44
2,9-Feb,John Anders,820 5th St,FL,32584,1258,5.55,10,4.99,116.14
2,9-Feb,John Anders,820 5th St,FL,32584,2459,7.95,7,4.99,116.14
3,22-Feb,Sue Doe,914 1st St,FL,34513,2148,0.54,9,10.99,187.08
3,22-Feb,Sue Doe,914 1st St,FL,34513,9751,9.85,3,10.99,187.08
3,22-Feb,Sue Doe,914 1st St,FL,34513,9765,8.64,11,10.99,187.08
3,22-Feb,Sue Doe,914 1st St,FL,34513,5472,4.68,4,10.99,187.08
3,22-Feb,Sue Doe,914 1st St,FL,34513,4590,5.68,2,10.99,187.08
3,22-Feb,Sue Doe,914 1st St,FL,34513,5489,1.84,9,10.99,187.08

I would have the header on all of them say something like "Please contact us anytime if you have any questions", and the footer for all three would be "www.example.com".

If anyone could help me solve this issue I would be most appreciative. I know that I'll probably end up having to get a program to do this, but I have looked for a long time and I cannot find anything to help me.

Hi Alf,

That is the closest thing to working that I have seen. Would it be possible to create something that looks like an invoice with the macros? I do not have much experience with macros, so I do not know what can and what cannot be done. I know HTML and CSS, but I'm not sure how to create the macro code. I have attached an example of how I would like the invoices to come out using order # 1 as an example. I would be very interested to know if this is possible.

invoice_example.gif

I've been messing with this for the last few hours, and I finally got it to export the files to look almost look the way that I like. If you run the macro 'csv_to_pdf' you will see the 3 files that are exported. The only problem is where the item numbers are located, there are extra lines with blank spaces in them for two of the orders, the order for Sue Doe looks about correct.

CSV_to_PDF_edit.xlsm

Hi Paul

My latest effort. You have to set currency format as I'm reluctant to do so. Local Swedish should convert to US format but I've had problems with this before.

If this clears up your problem could you please mark this post as "Solved"

Alf

The txt file has the data for some example orders on it. It looks a little bit different than it normally would because I had to load it into excel to change all of the personal information, then paste it back into the file.

Here is some information on the files. On the PNG file, the blue lettering refers to text that I would like to show up on the pdf file, the text in red signifies the particular row that the data would be drawn from, and the black text surrounded by a grey box would just be the row header for reference (but I don’t want everything surrounded in a grey box).

For the discount, that currently shows up in columns AB, AE and AC of this data. I have many discount codes, but only 3 appear on here. So theoretically my discounts could go from column AB to AR or farther. Customers can only use 1 discount per order. Discount will always be a negative number, so what I would like to have show up in the discount section is any negative number from column AB til some column at row 1 matches “Tax-A”.

The png file wouldn't load, so I changed it to bmp

Hi Paul

Your last example is quite different from what we started with. From about 11 columns with information it's now up to 58 or more?

At the moment I'm not sure if I can manage to solve that. Need to think a bit about that but I would like to have a go at it so this is what I've done so far.

I've tested a bit with importing your txt file to excel. I'm uploading an excel file with a macro called "Transform". In order to test this you must create a folder called "C:\Temp\" and add the file "order1.txt". This is the same text file as you uploaded but change name as folder and file name is hard coded in macro.

The output to same folder is a file named "order1.xlsx". Now the fun starts looking at the macro in file "Tester.xlsm" you decide which Array's should be deleted and remove them from the macro. The idea is just to import the column that you need for the form i.e. "order number", "order date", "Payment first name", "Payment last name" ........

Getting the "right" columns in the "order1.xlsx" file one can then start juggling columns and numbers to get the proper layout for the pdf file.

Alf

Hi Alf,

I added another row and labeled the columns that can be deleted or ignored as Ignore/Delete. I really wish my shopping cart didn't have 58 fields, but unfortunately thats how it spits it out. Please let me know if this is what you wanted me to do. Thanks,

Paul

Yes, this will do as a starting point. Will probably be back with more questions later on.

Alf

Ok, try this macro. As before you need to have your "order1.txt" file in the "C:\Temp" folder and delete any "ordet1.xlsx" that may be left there from the last run.

The macro "select_cols" should pick the columns you specified from the txt file. You better check that it does so. If everything ok I then need an Excel file based on the information in the "ordet1.xlsx". The Excel file should be build the way you want the pdf file to look like. With that information I can hopefully automate the building of the subsequent excel files.

The macro uses the import wizard in Excel 2007 and at the moment columns are imported or not based on their position in the txt file i.e. 1st column import, columns 2, 3, 4, 5, 6, 7 not import column 8 imported and so forth. Bad good? You tell me.

Another approach if column position can vary would be to loop through all the columns and deleting those with "not needed names". Tedious but possible.

Alf

I loaded the file, but I'm not really sure what I'm looking at. I ran the code, it was labeled "add_sheet", and it added a bunch of sheets to the file. I don't see the data from order1.txt in the file. Did I do something wrong?

I uploaded the file after I ran the export to show you what it looked like.

Paul

Did I do something wrong?

No you did not I did (Mea Culpa). Uploaded the wrong file.

Here comes the file you should test.

Alf

No biggie, but I'm pretty sure that's that same file. I loaded it and the code looks the same.

Paul

Here's the updated picture, hope it's less confusing. The letters represent the respective column that the date is pulled from.

Yes you are right again. I guess I need a vacation from the Excel forum as I'm getting files mixed up twice!!!

Try this one

Alf

It worked perfectly. I attached the outputted "order1.xlsx" for you to look at, with some edits.

Grabbing the data will work great until you get to the discounts. In the file "order1.xlsx" they were labeled "5%-A" and "Air-Tite Discount-A". There are only two there, but we have the possibility to have upwards of 15-20 different discount codes showing up on there, so 13-18 additional columns. To get rid of this issue, lets remove "5%-A" and "Air-Tite Discount-A", or anything that is between "Shipping-A" and "Tax-A". From there we can have a new column that calculates the discount: ("Total-A" - ("Subtotal-A" + "Shipping-A" + "Tax-A"))

I deleted the 2 discount columns in the "order1.xlsx", and threw in a new column labeled "Discount" that has a formula in it.

Let me know if this makes sense,

Paul

As I said in an earlier posting:

The macro uses the import wizard in Excel 2007 and at the moment columns are imported or not based on their position in the txt file i.e. 1st column import, columns 2, 3, 4, 5, 6, 7 not import column 8 imported and so forth. Bad good? You tell me.

Another approach if column position can vary would be to loop through all the columns and deleting those with "not needed names". Tedious but possible.

I think we are back to the second alternative in organizing / deleting unwanted columns. One could use part of the macro to get rid of the earlier columns starting with column "Form Name" up to column "Shipping-A" i.e. column "Referring URL" would be the last column deleted. The rest of the columns from the text file would be imported by the macro.

Then I need a list of all possible columns name based on the 15 to 20 discount types and the macro will no start at the last column used in the range and work its way towards the "Item quantity" column. No to complicate I think KISS is the principle we are looking for.

Or we specify all the possible allowed column names and let macro start at the last column and work its way to the first column. If a column name is not on the approved list it gets deleted.

Yes Paul I do think you problem got a bit more complicated! On the other hand it also got more interesting.

Have to examine your formula to see which columns are involved in your calculation.

Alf

Or we specify all the possible allowed column names and let macro start at the last column and work its way to the first column. If a column name is not on the approved list it gets deleted.

I think that this way would work best. We are constantly adding and removing discounts, so while there are consistently 15-20 discounts, the names change. Since the discount will just be calculated right before going to PDF there's no real reason to have them in there. Would you like me to make a list of all of the possible column names allowed?

Let me know your thoughts.

Paul

Just in case, here's the list of column names allowed:

Order Number
Date
Total
Subtotal
Item Number
Item Name
Item Price
Item Quantity
Shipping-A
Tax-A
Shipment-A State
Shipment-A Address-1
Shipment-A EMail
Shipment-A Address-2
Shipment-A Postal Code
Shipment-A Country
Shipment-A First Name
Shipment-A Day Phone
Shipment-A Last Name
Shipment-A City
Payment First Name
Payment Last Name
Payment Address-1
Payment Address-2
Payment City
Payment State
Payment Postal Code
Payment Country
Payment Email
Payment Day Phone
Payment Method
EDITABLE-32750

Paul

Yes I think we are getting somewhere now.

Your excel templates are fine now I know what we are aiming for i.e. output wise.

Your list of allowed columns names is also good to have because I can now test an idea or two.

What we are aiming for I think is a macro that when it runs does the following:

1. Opens the "Explorer Window" and let you choose a text file in a pre determined folder (easier to build, free choice of folder could be a possibility as well)

2. Imports chosen file into Excel 2007 where all columns from text file is imported into excel.

3. Loops through all column names i.e. all names in row 1 and delete columns not on "allowed name list"

4. Insert a new column named "Discount" between "Shipping-A" and "Tax-A" with a calculating formula based on your example.

5. Macro loops through "order number" and find all unique numbers using this as the base for auto filtering of data.

6 Extract filtered data to an excel template (the one you build) sheet adjusting for things like "discount" or no "discount" and so forth.

7. Saving the excel template to a pdf file with your proposed naming convention.

8. Macro loops between point 6 and 7 until all data is processed.

Have a look at this and think if this is what you need. In the meantime I'll start building along these lines and unless you have any other special requests I think I have all the information I need for building a macro.

Alf

Couldn't have said it any better myself. I did have two other special requests though. I'll try to explain them below.

#1

I have some products whose item numbers end in x25, x100, x36, and etc. I need to have those x#'s removed, and have a few other things changed. Here are two example products:

Original:
Item Number,Item Name,Price,Quantity,Total
4890 x10,500 Saflips: Mylar flips for 2 .5" x 2.5 "s,89.99,1,89.99
4895 x50,50 Safgard Sleeves for small currencies,35.99,1,35.99
4895 x50,50 Safgard Sleeves for small currencies,35.99,3,107.97

Work to do:
1)- Remove the "x#" from the item number
2)- Take the # from the "x#" (so in this example 10 and 50) and divide the first number in the item name by it
A)- If the resulting number is 1, I would like to have it removed
3)- Take the # from the "x#" and divide the price by it (to two decimal places)
4)- Take the # from the "x#" and multiply to quantity by it

After Edit:
Item Number,Item Name,Price,Quantity
4890,50 Saflips: Mylar flips for 2 .5" x 2.5 "s,9.00,10
4895,Safgard Sleeves for small currencies,0.72,50
4895,Safgard Sleeves for small currencies,0.72,150

**The only issue with this is the rounding to two decimal places. In the first example the actual per item cost is 8.999, but that rounds to 9.00. That is ok as long as the total stays the same, or at 89.99. If it shows up as 90.00 (9.00x10) then the total will be a little bit off.

#2
Original:
Item Number,Name,Price (Order Rule 48 States-USD)
0642-Q,Half Cent 1794-1837 - CW Slab # 1,1.85
0644-Q,Half Cent 1840-1857 - CW Slab # 2,1.85
0644-R,Two Cent Piece - CW Slab # 2,1.85
0646-Q,Large Cent 1793 - CW Slab # 3,1.85
0646-R,Large Cent 1837-1857 - CW Slab # 3,1.85

Work to do (This one should be easier)
1)- On some of my products there is a -"Letter" (ex: "-Q" & "-R") in the item number. I would like to have that -Letter removed, thats it.
2)- I do have some products that end in -#, but I don't want those edited, just the ones that end in -Letter

After Edit:
Item Number,Name,Price (Order Rule 48 States-USD)
0642,Half Cent 1794-1837 - CW Slab # 1,1.85
0644,Half Cent 1840-1857 - CW Slab # 2,1.85
0644,Two Cent Piece - CW Slab # 2,1.85
0646,Large Cent 1793 - CW Slab # 3,1.85
0648,Large Cent 1837-1857 - CW Slab # 3,1.85

I attached a file with all the possible combos that this currently has. There are two tabs, one for the "x#" and one for the "-Letter".

Paul

Have an updated file for you to test.

Still need a text file named "order1.txt" in folder "C:\Temp" to test it. Run macro "create_pdf" and check if results on sheet “Import” are ok.

Macro starts by clearing sheet "Import" of all values. It then imports the "order1.txt" file. Removes all columns not specified on "Spec_sheet". Inserts column and add heading "Discount". Writes formula in cell below "Discount" and copies formula down to the end of data.

I've increased the number of arrays in the macro so it can now handle a text file with 75 columns without falling flat on its feet.

Had to build a two version of the model since Sweden uses "," where you use "."

With this macro I feel that point 2, 3 and 4 are fixed.

Alf

Tested all 3 text files. Two runs ok but 3 is not working properly since column named "Tax-A" is missing.

Propose a small change in macro to test for precence of "Tax-A" if missing it will be incerted by macro to get right martix formate.

Alf

I ran the macro on the "order1.txt" file and it worked great.

I tried to create a formula to avoid the whole missing "Tax-A" issue. Unfortunately when I thought I had created one that should have worked, I got an error saying that I entered too many argurments.

Propose a small change in macro to test for precence of "Tax-A" if missing it will be incerted by macro to get right martix formate.

As a result, I think that is would work best.

Paul

I think that I found a way to fix the issue. Instead of calculating the discount with this

=IF(SUM(C2-(D2+I2+K2))=0,"",IF(SUM(C2-(D2+I2+K2))<0,SUM(C2-(D2+I2+K2)),""))

I changed the 0 to -0.005, and that got rid of those rediculously small numbers:

=IF(SUM(C2-(D2+I2+K2))=0,"",IF(SUM(C2-(D2+I2+K2))<-0.005,SUM(C2-(D2+I2+K2)),""))

Paul

Hi Paul

Not to happy with my last updated file (Tester5_US.xlsm) so I changed the macro a bit and I also increased the number of rows in the “Template” sheet.

When the macro runs it works as before except it now makes a copy of the “Template” sheet. Copy is named using unique order number. All relevant information is copied to this sheet.

A specified range on same sheet is then saved as your pdf file (out put folder is still “C:\Temp”).

The macro then uses the next unique number makes a copy of Template sheet adds information and saves the specified rang as your pdf file.

After looping through all unique numbers the autofilter is removed from the import sheet and macro stops.

This gives you the possibility to check the excel sheets and make manual adjustments to each order if you whish and save the modified excel sheet as your new pdf file. At the moment this is a manual operation but it could be automated if needed.

You also have a chance to see/check all the imported data on the “Import” sheet.

When you do new macro run macro starts by deleting all sheets except for “Spec_sheet”, “Import” and “Template”. Then all “order number” and unique “order number” is deleted from the “Spec_sheet” and all information is cleared from sheet “Info”.

Alf

Hi Alf,

It's definitely getting really close to being finished. I noticed a few things on "Tester6_US.xlsm" and I'll just give them to you by number.

1. If you load "order1_tax.txt" it will output the file, but unfortunately the tax does not show up, and it isn't included in the calculations either. If you look at "Prego 2008091413.pdf" (I tried to attach it here, but it wouldn't) you will see that the total is listed at $38.39, but the actual total is suppose to be $41.08, with $2.69 added from tax.

2. On the output pdf file, there is the last name and order number that shows. Currently the first 10 digits of the order number show, but I would like that to change to the last 10 digits.

3. I don't know if you can make the last name shipping show, and then have the last name billing show up in () if it is different than the last name shipping. If you can't do that, I would like to have the last name shipping show in the .pdf file, right now it shows as last name billing.

I know that you're probably getting ready for your trip, so I'll try to work on this stuff while your gone.

Paul

Tax problem will have to wait.

File name fix is change this line

Range("N2") = Range("W60") & " " & Left(Range("C3"), 10)

to :

Range("N2") = Range("W60") & " " & Right(Range("C3"), 10)

Alf

Hi Paul,

Last go at this macro before I’m off on vacation.

Got the tax thing fixed also updated the file naming structure. Can’t do the
“file name*.pdf” as “*” is a no no in file naming. Instead you got “cc”.

Used both the “order1.txt” file and the “order1_tax.txt” to check my latest macro and found one problem.

In order number 200809141120079482 to Fred Mehe the subtotals (txt file value) is 18.34. Calculating the subtotal I only find 14.84 and of course the total also become different 24.62 (txt file) and 21.12 (Excel calculation).

In both files something is wrong with the “Fred Mehe” data. A deleted line perhaps?

Alf

Hi Paul

Back to rainy Gothenburg after my vacation and it’s time to finish this project.

Hopefully this will be the final version.

Changes from previous version are as follows:

Sheets are now named as the corresponding pdf file. Better or would you rather have the last 10 digits of the order number as sheet name?

Sheets are sorted alphabetically.

The macro now checks the item number and changes it as follows:

If name like “0264-Q” it will be changed to 0264

If name like “4720 x2”, 0714 x25 or “0635 x500” name will be changed to
4720, 0714 or 0635 and the 2, 25 and the 500 will be used in calculating unit quantity and unit price.

The only question I do have is the item number 7547A. Should the A be removed or?

You do some testing on this and see it works. The Swedish version looked ok to me when tested.

Alf

Sorry about this delay but did have a number of other priorites.

This is my latest effort. Again Swedish version runs ok hopefully the US version will also work.

Alf