B4Print.com

Applications => The Rest... => Topic started by: Joe on March 16, 2018, 07:21:35 AM

Title: Excel stuff
Post by: Joe on March 16, 2018, 07:21:35 AM
This site looks like a good place for Excel info should you need it.

https://chandoo.org/wp/
Title: Re: Excel stuff
Post by: DigiCorn on November 06, 2019, 01:02:56 PM
Where the fuck does the damned PDF go when you print to PDF? Can't find it anywhere and save to PDF doesn't work - file is all FUBAR

on Mac OS
Title: Re: Excel stuff
Post by: DigiCorn on November 06, 2019, 01:34:48 PM
figured out a work around. that was stressful - had a guy waiting on it and nothing else was working for what he wanted other than the print to pdf. I did all the same procedures, but instead of print to pdf, i hit preview in preview, and then exported the pdf from there. it got what we wanted. save as pdf was making weird page breaks and not doing what was intended.
Title: Re: Excel stuff
Post by: Tracy on November 07, 2019, 10:27:51 AM
under view select normal
and under page setup select fit to 1 page
Title: Re: Excel stuff
Post by: DigiCorn on November 07, 2019, 10:43:17 AM
yeah - unfortunately for this project, that was not working. Even zamzar messed it up... I got an 11" wide x 1000" long document, 1 single page. It was a long-ass spreadsheet that was supposed to be 118 pages, 11" x 8.5".
Title: Re: Excel stuff
Post by: Tracy on November 07, 2019, 11:01:07 AM
Ah! well that doesn't work then
did you try to change under view?

excel is something I have to fight too sometimes!!
Title: Re: Excel stuff
Post by: DigiCorn on November 07, 2019, 12:55:13 PM
The document had "defined" page breaks, for whatever that is worth, and I selected and defined the print area, but it kept redefining and splitting up the document, so instead of 118 pages, I'd get like 600+ pages or something. The only way it would run 118 pages as defined was printing as a pdf... then it would write a file 118 pages long, but I don't know where the file ends up. That's why I clicked preview in preview and then exported from there.
Title: Re: Excel stuff
Post by: Tracy on November 14, 2023, 02:18:45 PM
Does anyone know how to get to this on a Mac
I'm looking to get to info/Check for Issues/Inspect Document

I have seen this view on a PC but not on a Mac 
Title: Re: Excel stuff
Post by: Joe on November 14, 2023, 02:20:18 PM
Excel has no business in prepress! (I have no idea how to get to that)
Title: Re: Excel stuff
Post by: Tracy on November 14, 2023, 02:38:29 PM
I have been in variable data hell this week, very complex jobs
sheesh, I was in the bathroom yesterday taking to myself "I Hate this job"!!
I have so much going on personally right now, I don't feel like being this intense at work.
Title: Re: Excel stuff
Post by: David on November 14, 2023, 02:42:34 PM
I believe it's only on a PC, since the Mac version hasn't been updated since dinosaurs roamed the earth.


Title: Re: Excel stuff
Post by: DigiCorn on November 14, 2023, 03:00:24 PM
Maybe Microshaft  Office.com (https://www.microsoft.com/en-us/microsoft-365/buy/compare-all-microsoft-365-products?tab=1&OCID=cmmruikv4ct&rtc=1)?
Title: Re: Excel stuff
Post by: Tracy on November 14, 2023, 03:19:55 PM
Quote from: David on November 14, 2023, 02:42:34 PMI believe it's only on a PC, since the Mac version hasn't been updated since dinosaurs roamed the earth.



I was wondering about that
Title: Re: Excel stuff
Post by: scottrsimons on November 15, 2023, 07:08:15 AM
Wonder if "Error Checking..." is the same thing you are looking for Tracy?
Title: Re: Excel stuff
Post by: Tracy on November 15, 2023, 08:57:35 AM
OOH! Thanks Scott!
I will check the error checking

Not it, I think I might have to look into Macros as this stuff gets so complex, I just kicked the crappy file back
and had them fix it.
There seems to be more in Windows excel than Mac
That's why you have to buy Macros!
Title: Re: Excel stuff
Post by: pspdfppdfxhd on December 22, 2023, 10:13:06 AM
Quote from: Tracy on November 14, 2023, 02:38:29 PMI have been in variable data hell this week, very complex jobs
sheesh, I was in the bathroom yesterday taking to myself "I Hate this job"!!
I have so much going on personally right now, I don't feel like being this intense at work.
Been there, got another one coming in the new year. Spike up the egg nog!
Title: Re: Excel stuff
Post by: Tracy on December 22, 2023, 10:23:24 AM
I'll skip the Egg Nog, just give me the spike part  ;D
Title: Re: Excel stuff
Post by: Tracy on January 23, 2024, 09:50:24 AM
Variable Data question, but it is more of an excel question I think.
I already told them I couldn't do it the way the data was sent, but they were able to do it in Word
so I'm thinking it's my skill level that couldn't do it.

Excel file had 30,000 records there were duplicate rows with different "gift" amounts and different "dates"
so there really was only like 6000 records but the data was needing info from the duplicate rows
the only way I could do this is with columns

Is there a way for this to be done with InDesign?

There is no way I would fix 30,000 records, if it was small I would fix it.
Title: Re: Excel stuff
Post by: Joe on January 23, 2024, 01:29:04 PM
Not sure if this would work:

Transpose (rotate) data from rows to columns or vice versa (https://support.microsoft.com/en-au/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744)
Title: Re: Excel stuff
Post by: Tracy on January 23, 2024, 03:14:07 PM
Thanks! I will try it, I'm sure it won't work for me  ;D
Title: Re: Excel stuff
Post by: Tracy on January 24, 2024, 12:34:09 PM
That actually kind of worked, I would of still had to go through the 30,000 records
and select the ones I wanted and copy/transpose the duplicate names separately

This will work for me with small batches, to me the customer should supply the data correctly.
can someone let me know if this is wrong thinking?
Sometimes I get an attitude about customer files  ;D
Title: Re: Excel stuff
Post by: DCurry on January 24, 2024, 01:10:58 PM
I agree with you that the customer should supply the data however you need it. Because if you alter the data and something goes wrong and isn't caught til after it prints (and mails!), then you're on the hook. I certainly wouldn't mess with that many records!
Title: Re: Excel stuff
Post by: Tracy on January 24, 2024, 01:45:38 PM
Thanks D, oh yeah I know I would screw it up  ;D

good to know it's ok to kick back bad data, not sure how they did it in word
they had to of fixed the data
Title: Re: Excel stuff
Post by: Possum on January 24, 2024, 02:04:03 PM
Nobody is smart enough to ask the printer how they want art or data supplied, are they? I never had the pleasure of working for a place that charged extra for the extra work to straighten crap out, but I bet the customers would have howled if they were billed for what it actually costs for their laziness.

I have had to spend over four hours on a tee shirt design, first trying to get it so that it could be delineated in Illustrator, then cutting it down from six colors to three because they didn't want to pay for six.

It's always fun trying to decide what color to make people's faces when your only choices are red, green and white. Boy, I was tempted....
Title: Re: Excel stuff
Post by: Joe on January 24, 2024, 02:07:13 PM
Quote from: Tracy on January 24, 2024, 01:45:38 PMThanks D, oh yeah I know I would screw it up  ;D

good to know it's ok to kick back bad data, not sure how they did it in word
they had to of fixed the data
This file they sent sounds like the classic example of the customer thinking "I will do it like this to help the printer out". :o

Always ends up like this:

(https://commonwealthherbs.com/wp-content/uploads/2015/11/4146904909_a8bfa761ef_z.jpg)
Title: Re: Excel stuff
Post by: Tracy on January 24, 2024, 02:28:45 PM
right on the nose Joe - Train Wreck is right
6289 Letters 6289 Statements and 6289 Envelopes 
XMF did not like their word pdf that I put into indy with their Letterhead
so I had to multipage import this piece of @$% R#$ into indy for 2-up on the letters and statements
just found out they put the wrong date on the letters
The date on it is January 26 (that's when they want it)
The bindery has to put stamps on 6289 pieces and it still has to run
and I still need to fix their letters, You want it by the 26th?
:lmao:
Title: Re: Excel stuff
Post by: DCurry on January 24, 2024, 03:42:08 PM
Quote from: Possum on January 24, 2024, 02:04:03 PMI have had to spend over four hours on a tee shirt design, first trying to get it so that it could be delineated in Illustrator, then cutting it down from six colors to three because they didn't want to pay for six.

And then they send a new file.  
Title: Re: Excel stuff
Post by: DigiCorn on January 24, 2024, 07:51:47 PM
Quote from: Tracy on January 24, 2024, 02:28:45 PMright on the nose Joe - Train Wreck is right
6289 Letters 6289 Statements and 6289 Envelopes
XMF did not like their word pdf that I put into indy with their Letterhead
so I had to multipage import this piece of @$% R#$ into indy for 2-up on the letters and statements
just found out they put the wrong date on the letters
The date on it is January 26 (that's when they want it)
The bindery has to put stamps on 6289 pieces and it still has to run
and I still need to fix their letters, You want it by the 26th?
:lmao:
Is this a VDP job? Are you using XMPie or FusionPro?
Title: Re: Excel stuff
Post by: Tracy on January 25, 2024, 10:15:20 AM
InDesign
I may have to look into VDP Programs, not sure if I want to tho
Title: Re: Excel stuff
Post by: DCurry on January 25, 2024, 06:40:55 PM
When I first started doing VDP over 15 years ago, I hated it. But as I've done more and more of it I really came to enjoy it. It helps immensely if you have a decent software for it. My last job about half of what I did was variable. Some was very complex and while sometimes it can be frustrating it's just cool when you figure out how to do something tricky. 

FusionPro would be something to look at. It's probably the best bang for the buck and is extremely powerful, but downside is the interface kinda sucks and you have to work in the PDF. 

I'm using XMPie currently. Still learning how to do some things but it is very tightly integrated with InDesign. 
Title: Re: Excel stuff
Post by: DigiCorn on January 25, 2024, 10:35:14 PM
FusionPro is good if you don't have a large number of records... say less than 10,000. XMPie is better for large quantities. FusionPro has some limitations, like having to set up in PDF only (you can create "placeholders," in ID or Illy, but it really doesn't work very well), and sometimes the programming can be overly complex.

Using ID's mail merge for VDP works for simple stuff, but at my last place we would only output 500 records at a time. It *can* do more, BUT we found that there were fewer issues by simply breaking it up into chunks, plus the Fiery can process the merged file faster. We would typically print masters offset, and then only impose the VDP in mail merge in ID and imprint the masters, but I have also done it 4-color on the digital press by creating a master within Command Workstation and then an imprint file that references the master.

In all instances, having a good excel data file is crucial to start with.
Title: Re: Excel stuff
Post by: Tracy on January 26, 2024, 08:36:52 AM
I had dabbled with Fusion pro a bit, it was awhile back and it seemed clunky.

I may have to look into XMPie, but for what we do Indesign is working fine
I can do large (for us) jobs but it seems things might be getting more difficult
as files get larger, I got a really fast computer last time, but I'm thinking I may 
need to go bigger!!  ;D

I think the main problem might be the customers  ;D as when I have clean data
it's a smooth as Buttah this last one I ended up with merged word docs that needed
a lot of cleaning up, frustrated the heck out of me, and then it wouldn't go in my work flow.
had to multi page import for a cut and stack in indy.

one thing I may look into is quite imposing for jobs like this.
Title: Re: Excel stuff
Post by: DCurry on January 26, 2024, 09:20:20 AM
I don't miss using FusionPro at all, just saying it's a lot of biliary for the price compared to alternatives. It used to be even cheaper but I think they moved to a subscription model nowadays. 

You're right about the data, though - bad data is bad data. You just might have more options with rules and such to deal with bad data than you currently have. 
Title: Re: Excel stuff
Post by: Tracy on January 26, 2024, 11:28:27 AM
yeah it helps to talk to people, because I work alone, I don't know what the rules are!  ;D
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 03:17:32 PM
Speaking of excel problems... has anyone ever run into having to create a csv text file with all cells having double qotes around the data? Even the blank cells will have them as well.

Example: "John Smith","123 Main Street"," ", The last one there was from a blank cell. I know, this is a prepress forum, but some of the stuff i have to deal with nowadays is ridiculous. I googled it to death but couldn't figure it out. Figure the best program may not even be excel to deal with this, maybe a text editor? I found one that would put quotes but only if there was content, but not with the blank cells.

Title: Re: Excel stuff
Post by: DCurry on February 04, 2024, 03:39:19 PM
So you need to put the quotes in? Try BBEdit. You can search for comma and replace with quote-comma-quote and it should get most of them. Won't get the first and last of each line so you can search for comma-\r and add them that way.

Feel free to give me the list and I can do it if you hit trouble.

There may be instances where you cause damage, like if there is a comma in the data. There are probably ways to handle that too.
Title: Re: Excel stuff
Post by: DCurry on February 04, 2024, 03:45:10 PM
To prevent changing the good commas you could save as tab-delimited and in BBEdit search for \t and replace with quote-comma-quote
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 04:05:50 PM
Thanks David, that was sort of the direction i was going to today. Downloaded BB edit, text wrangler and Libre office and tried and tried to figure something out. Libre office will export with quotes but only where data is present, not blank cells. Soooo... i put "oh crap" in all the blank cells in excel, opened it up in libre office, find and replace that with double quotes then said fkit. Need to get some beer, it's Sunday.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 04:08:44 PM
I appreciate the offer for help but i have had it with this for today. There is a couple of people i can reach out to tomorrow for assistance, hope they aren't too busy, this job is already a week late!
Title: Re: Excel stuff
Post by: DCurry on February 04, 2024, 04:16:40 PM
Who or what is telling you that you need every cell wrapped in quotes?

I'm used to seeing it that way because at my old shop most of the data came out of Redpoint that way, but none of the VDP software I've ever used requires it. 
Title: Re: Excel stuff
Post by: DCurry on February 04, 2024, 04:21:39 PM
BTW, TextWrangler used to be the free version of BBEdit. Now it's just BBEdit but if you want all the features you have to pay. The free version is plenty powerful for our needs. I'm not writing code. The GREP search and replace abilities are great. 
Title: Re: Excel stuff
Post by: Joe on February 04, 2024, 04:43:24 PM
Quote from: pspdfppdfxhd on February 04, 2024, 04:05:50 PMThanks David, that was sort of the direction i was going to today. Downloaded BB edit, text wrangler and Libre office and tried and tried to figure something out. Libre office will export with quotes but only where data is present, not blank cells. Soooo... i put "oh crap" in all the blank cells in excel, opened it up in libre office, find and replace that with double quotes then said fkit. Need to get some beer, it's Sunday.
The source file probably has a space in the blank cells causing it to include the blank cells so they might have thought the needed the blank cells included and they weren't so they added a space. Or they are using some stupid way to create the csv file. Try do a search and replace for " " and replace with nothing. Also that is Dan....not David. ;D
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 04:55:37 PM
Not too sure what you mean, but it's for a scanline reading of a 44 digit code on a donation reply coupon and the data input from our file has to go in like a grid so even the empty cells have to fit in there. 
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 04:59:38 PM
Quote from: DCurry on February 04, 2024, 04:16:40 PMWho or what is telling you that you need every cell wrapped in quotes?

I'm used to seeing it that way because at my old shop most of the data came out of Redpoint that way, but none of the VDP software I've ever used requires it.
The company that loads the data to lookup from their scanner reading of the 44 digit scanline i had to create from various columns in the file that is printed on the bottom of the reply coupon.

Like this, sorta: 000000000000000 000000000000 0000000000 0000
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 05:22:23 PM
Once again, we seem to be catering to other vendors that should be doing this from our finished data output file. What else is new.
Title: Re: Excel stuff
Post by: Joe on February 04, 2024, 06:24:43 PM
Quote from: pspdfppdfxhd on February 04, 2024, 04:55:37 PMNot too sure what you mean, but it's for a scanline reading of a 44 digit code on a donation reply coupon and the data input from our file has to go in like a grid so even the empty cells have to fit in there.
Well I thought you were just talking about a regular csv file with text since your example read as:

Quote from: pspdfppdfxhd on February 04, 2024, 03:17:32 PMExample: "John Smith","123 Main Street"," ", The last one there was from a blank cell. I know, this is a prepress forum, but some of the stuff i have to deal with nowadays is ridiculous. I googled it to death but couldn't figure it out. Figure the best program may not even be excel to deal with this, maybe a text editor? I found one that would put quotes but only if there was content, but not with the blank cells.
So now I have no idea what you are talking about so I will just stay out of the conversation.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 04, 2024, 06:43:53 PM
Okay, thanks for the feedback, i am not sure what i am talking about either actually, i retire from this conversation too.
Title: Re: Excel stuff
Post by: DigiCorn on February 04, 2024, 07:30:39 PM
I thought it was Dan and not David.

Anyway, I know you can do a tab delimited and a comma delimited; is there a quote delimited csv option to write a file to? Otherwise, I echo the earlier post that says to do a search and replace with nothing.
Title: Re: Excel stuff
Post by: DCurry on February 04, 2024, 07:45:14 PM
Quote from: DigiCorn on February 04, 2024, 07:30:39 PMis there a quote delimited csv option to write a file to? Otherwise, I echo the earlier post that says to do a search and replace with nothing
I don't believe so. 

I don't think the problem is spaces in empty cells. I think his problem is he needs to supply a csv to the other vendor where every cell is wrapped in quotes, whether it has data or is null. 
Title: Re: Excel stuff
Post by: Joe on February 04, 2024, 10:33:19 PM
The only way I can export a .csv file from Libre Office and have the empty cell is to put a space in it. My Calc document has a space in it to get the results below. If it is null or empty it doesn't include that cell in the .csv file.

Screenshot 2024-02-04 at 10.27.05 PM.png

Then exporting like this:

Screenshot 2024-02-04 at 10.28.56 PM.png

Which gives me this:

Screenshot 2024-02-04 at 10.29.56 PM.png
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 03:03:48 AM
That might do it, i will have to see if there is a way to get spaces in all the empty cells.
Title: Re: Excel stuff
Post by: DCurry on February 05, 2024, 06:20:43 AM
Not sure why you want or need a space in an empty cell. If it's empty and wrapped in quotes it should just read "" when you open it in a text editor. 

Maybe I'm missing something. 
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 06:37:32 AM
Quote from: DCurry on February 05, 2024, 06:20:43 AMNot sure why you want or need a space in an empty cell. If it's empty and wrapped in quotes it should just read "" when you open it in a text editor.

Maybe I'm missing something.
No I don't think you are missing anything. I filled all empty cells with a space then saved out of LibreOffice and it came out like this:    " " and it should be "".....I COULD do a find and replace to change them all but I really don't know if that's the right thing to do. I tried that and it looks like it worked. Could have screwed up something else though.
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 07:05:10 AM
Quote from: DCurry on February 05, 2024, 06:20:43 AMNot sure why you want or need a space in an empty cell. If it's empty and wrapped in quotes it should just read "" when you open it in a text editor.

Maybe I'm missing something.
If the cell is just blank it doesn't come out as "". It comes out with the ,, where the cell for "Something Else" is blank. No quotes. Like this:
Screenshot 2024-02-05 at 6.55.35 AM.png
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 07:31:09 AM
OK to get it to look like this:

Screenshot 2024-02-05 at 7.29.49 AM.png

In the blank cells enter this formula: =""
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 08:18:16 AM
Hey Joe, I thought you retired!!
You just never stop trying.

Where are you putting this ="" formula in? LibreOffice?
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 08:20:59 AM
Yes in Libre Office with the empty cell selected in the formula field as shown here:

Screenshot 2024-02-05 at 8.20.09 AM.png
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 09:03:07 AM
Initial testing seems to show that worked like magic!

My only issue is going through and finding all the blanks, there are 15,000 addresses in there.


Title: Re: Excel stuff
Post by: DCurry on February 05, 2024, 10:16:51 AM
At my old shop if all the fields were wrapped with quotes an empty value showed as ,"",
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 10:24:25 AM
Quote from: pspdfppdfxhd on February 05, 2024, 09:03:07 AMInitial testing seems to show that worked like magic!

My only issue is going through and finding all the blanks, there are 15,000 addresses in there.
Yeah that is going to be a problem since you can't do a search for an empty field and replace it with something else in LibreOffice. Probably the best option would be go ahead and make the .csv file and then open the .csv file in a text editor and search for ,, and replace with ,"", assuming of course there aren't any fields that have a double comma in it.
Quote from: DCurry on February 05, 2024, 10:16:51 AMAt my old shop if all the fields were wrapped with quotes an empty value showed as ,"",
You mean if you entered "" in the field? If so he would still have to manually enter it in all blank fields. Or do you mean empty fields would export as ,"",? If so were you using Excel or something like LibreOffice?
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 10:28:52 AM
I am filtering out each column for blanks and putting the +"" formula in the blanks. Seems ok so far.
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 10:33:02 AM
Quote from: pspdfppdfxhd on February 05, 2024, 10:28:52 AMI am filtering out each column for blanks and putting the +"" formula in the blanks. Seems ok so far.
What formula is +""  .

Or do you mean =""  ?
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 10:47:35 AM
Looks like it's working. Thanks for all this help.

If it's not right they tell me and I will have to go to plan B. Get an expert to do it.

If I can't find an expert I will go to plan D. Retire. I would be pretty poor but it's a new challenge!
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 10:48:56 AM
Quote from: Joe on February 05, 2024, 10:33:02 AM
Quote from: pspdfppdfxhd on February 05, 2024, 10:28:52 AMI am filtering out each column for blanks and putting the +"" formula in the blanks. Seems ok so far.
What formula is +""  .

Or do you mean =""  ?
Yes, my bad. Typo, I meant ="". (hit the shift key by mistake).
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 11:13:54 AM
Where did you find out all this Joe, I thought you were a straight up prepresser.

I googled this thing to death and couldn't find an answer.

Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 11:27:58 AM
Just google searches and finally ran across the ="" formula. Didn't save the website and now can't find it again. ;D

I did some VDP in my prepress life. Not a lot but some.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 11:40:10 AM
Quote from: Joe on February 05, 2024, 11:27:58 AMJust google searches and finally ran across the ="" formula. Didn't save the website and now can't find it again. ;D

I did some VDP in my prepress life. Not a lot but some.
Well you must be a better "googler" than I. 

"I did some VDP in my prepress life. Not a lot but some." (some is enough).
Title: Re: Excel stuff
Post by: Joe on February 05, 2024, 01:01:32 PM
Quote from: pspdfppdfxhd on February 05, 2024, 11:40:10 AM
Quote from: Joe on February 05, 2024, 11:27:58 AMJust google searches and finally ran across the ="" formula. Didn't save the website and now can't find it again. ;D

I did some VDP in my prepress life. Not a lot but some.
Well you must be a better "googler" than I.

"I did some VDP in my prepress life. Not a lot but some." (some is enough).
Do you have Excel? I did find something that says Excel outputs blank cells as ,"", instead of the way LibreOffice does it as ,, with the double comma signifying an empty cell.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 03:10:37 PM
Never had any luck getting excel to do these sorts of things. I think you might have to get into macros or scripting... way beyond my pay grade.

I was just wondering, for what purpose did libre put that strange little invisible formula in for =""?

For this specific purpose? It's strange, but from what i can see in the text file it worked. I sent it off for testing so i will know soon.
Title: Re: Excel stuff
Post by: Ear on February 05, 2024, 04:12:10 PM
I hear that... I thought a Pivot Table was millennial speak for a Lazy Susan.  :P
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 04:52:28 PM
Dunno, but i have just started getting pretty ok with the barre chords after one year. Pivot tables are even scarier. Hard to budge that wall of frustration.
Title: Re: Excel stuff
Post by: Ear on February 05, 2024, 05:07:23 PM
I've seen the Excel groupies... not much incentive compared to the barre chords. 
Title: Re: Excel stuff
Post by: born2print on February 05, 2024, 05:12:14 PM
With practice, the pain of forming those shapes will ebb...
so don't fret! lol
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 05, 2024, 06:23:33 PM
;D
Title: Re: Excel stuff
Post by: DigiCorn on February 05, 2024, 07:40:14 PM
Quote from: Ear on February 05, 2024, 04:12:10 PMI hear that... I thought a Pivot Table was millennial speak for a Lazy Susan.  :P
Try going to google.com and type, "Ross Friends," in the search window. Then click on the couch icon. Make sure you have your speakers on.
Title: Re: Excel stuff
Post by: Ear on February 06, 2024, 06:11:45 PM
:))  PIVOT! I wear that one out whenever I help someone move.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 06, 2024, 06:26:58 PM
Quote from: Joe on February 05, 2024, 11:27:58 AMJust google searches and finally ran across the ="" formula. Didn't save the website and now can't find it again. ;D

I did some VDP in my prepress life. Not a lot but some.
Good news Joe, the =" worked. Bad news.... i screwed something else up. Don't you love it when you are flying by the seat of your pants and supposed to know everything about everything but you are really many mistakes away from being good? I will have to deal with it tomorrow, they are waiting for me to fix it. >:(
Title: Re: Excel stuff
Post by: Joe on February 06, 2024, 06:44:41 PM
Isn't that the way it usually goes? Pull a miracle out to be screwed over by something else. Been there...done that!
Title: Re: Excel stuff
Post by: DigiCorn on February 07, 2024, 02:23:53 AM
Don't they have tech support for a reason? I once had a job... The Polar Express. It's a special event every Christmas season by the CSRM (California State Railroad Museum). They run an old train in honor of the movie (and play the movie on screens inside) for November and December 4x a day, 4 days a week. It's the hottest ticket in town; you have to be a member of the museum society for at least a year to get an advanced purchase. It usually sells out for the season in the first 5 minutes. Once upon a time, it was my job to make the tickets. The tickets CANNOT AND MUST NOT EVER be resold and if they find out you bought them from someone else, the tickets are immediately voided and unusable. There were 6 cars (IIRC). Each car's ticket had a different image. There were 4 runs per day. Each run had a different color. Each car had an assigned seat with a unique code/number. Each car had a different number of seats. Each ticket was imprinted with a different date and time. Then, we had a hologram sticker printed that had to be manually applied to each ticket to prevent counterfeiting. Essentially, IIRC, there were something like 56,000+ tickets to be printed, and each ticket was unique.

I did this in FusionPro, and before I even started the job, I had to program an Excel file - they didn't supply one. I usually got the job at the start of September, and the tickets went on sale the first of October, so it had to be deliverable after that. It usually took a day to create the Excel file, and then 2 days to program the code in FusionPro for the color background swap, the car image swap, the dates, times, car numbers and unique barcode. It ran digital on 120# stock 4/4 and had to back up perfectly with no skew. After approved, it took about 2 days to run, and about 2-3 days in bindery because they always miscut and we had to rerun random tickets.

Originally it was like 768 separate files (6 cars x 4 times a day x 4 times a week x 8 weeks) because I wasn't smart enough to figure out all the proper programming. Then I enlisted the FusionPro guys to help, and we got it down to a single file, but had to export it in 768 files, but luckily, that's one export. I don't even remember if these are the correct numbers, but I'm using it as an example of how complex the job was. Until you deal with something like this, I am the VDP God.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 07:59:16 AM
Quote from: Joe on February 06, 2024, 06:44:41 PMIsn't that the way it usually goes? Pull a miracle out to be screwed over by something else. Been there...done that!
The damned wall of frustration.

Think I fixed it this morning. (pretty sure). They will let me know soon. They didn't complain about the double quotes so at least we have that going for us!
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 01:05:04 PM
Congratulations Joe! The data house approved the text file with the double quotes!!

Another fine job by our prepress Guardian Angel!
Title: Re: Excel stuff
Post by: Tracy on February 07, 2024, 01:40:10 PM
Holy cow! why could't the data house do it?
That would make me crazy
Title: Re: Excel stuff
Post by: Joe on February 07, 2024, 02:44:36 PM
Quote from: pspdfppdfxhd on February 07, 2024, 01:05:04 PMCongratulations Joe! The data house approved the text file with the double quotes!!

Another fine job by our prepress Guardian Angel!
How long before the check arrives? >:D
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 03:25:01 PM
Quote from: Tracy on February 07, 2024, 01:40:10 PMHoly cow! why could't the data house do it?
That would make me crazy
No, they really couldn't, it was 44 digit scanline with leading zeroes and stuff, one of the numbers which was 10 digits started counting up where it should've been constant throughout. Like 1000000001 on the next record became 1000000002. My mistake for using the auto fill in excel. Sometimes it does that and i don't know why.

Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 03:29:10 PM
Quote from: Joe on February 07, 2024, 02:44:36 PM
Quote from: pspdfppdfxhd on February 07, 2024, 01:05:04 PMCongratulations Joe! The data house approved the text file with the double quotes!!

Another fine job by our prepress Guardian Angel!
How long before the check arrives? >:D
Well i really should you know. Let me know if you become really down and out and i will see what i can do. But you can probably imagine how little i make in this racket. And don't tell me you're down and out now, i know you had money to buy a new Mac!  ;D
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 03:36:13 PM
And besides, Guardian Angels aren't supposed to ask for payment. It's in their code of conduct book. I saw it once.
Title: Re: Excel stuff
Post by: born2print on February 07, 2024, 03:56:04 PM
Pound of flesh?
Eye for an eye?
Title: Re: Excel stuff
Post by: Joe on February 07, 2024, 05:00:09 PM
Quote from: pspdfppdfxhd on February 07, 2024, 03:36:13 PMAnd besides, Guardian Angels aren't supposed to ask for payment. It's in their code of conduct book. I saw it once.
Was kidding.
Spoiler
Not needed with the billions of dollars I earned in prepress over the years.
[close]
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 07, 2024, 05:33:13 PM
You accept PayPal?
Title: Re: Excel stuff
Post by: Joe on February 07, 2024, 05:44:35 PM
Quote from: pspdfppdfxhd on February 07, 2024, 05:33:13 PMYou accept PayPal?
Nope.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 08, 2024, 06:59:58 AM
Bitcoin?
Title: Re: Excel stuff
Post by: Joe on February 08, 2024, 07:03:01 AM
Quote from: pspdfppdfxhd on February 08, 2024, 06:59:58 AMBitcoin?
Nope.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 08, 2024, 07:03:30 AM
Quote from: DigiCorn on February 07, 2024, 02:23:53 AMDon't they have tech support for a reason? I once had a job... The Polar Express. It's a special event every Christmas season by the CSRM (California State Railroad Museum). They run an old train in honor of the movie (and play the movie on screens inside) for November and December 4x a day, 4 days a week. It's the hottest ticket in town; you have to be a member of the museum society for at least a year to get an advanced purchase. It usually sells out for the season in the first 5 minutes. Once upon a time, it was my job to make the tickets. The tickets CANNOT AND MUST NOT EVER be resold and if they find out you bought them from someone else, the tickets are immediately voided and unusable. There were 6 cars (IIRC). Each car's ticket had a different image. There were 4 runs per day. Each run had a different color. Each car had an assigned seat with a unique code/number. Each car had a different number of seats. Each ticket was imprinted with a different date and time. Then, we had a hologram sticker printed that had to be manually applied to each ticket to prevent counterfeiting. Essentially, IIRC, there were something like 56,000+ tickets to be printed, and each ticket was unique.

I did this in FusionPro, and before I even started the job, I had to program an Excel file - they didn't supply one. I usually got the job at the start of September, and the tickets went on sale the first of October, so it had to be deliverable after that. It usually took a day to create the Excel file, and then 2 days to program the code in FusionPro for the color background swap, the car image swap, the dates, times, car numbers and unique barcode. It ran digital on 120# stock 4/4 and had to back up perfectly with no skew. After approved, it took about 2 days to run, and about 2-3 days in bindery because they always miscut and we had to rerun random tickets.

Originally it was like 768 separate files (6 cars x 4 times a day x 4 times a week x 8 weeks) because I wasn't smart enough to figure out all the proper programming. Then I enlisted the FusionPro guys to help, and we got it down to a single file, but had to export it in 768 files, but luckily, that's one export. I don't even remember if these are the correct numbers, but I'm using it as an example of how complex the job was. Until you deal with something like this, I am the VDP God.
I hear ya, had a few doozies over the years but not as challenging as this. If you ever get back into it and they ask you for a backfield file if the job has a scanline on it, Joe (and now me) could help you.
Title: Re: Excel stuff
Post by: pspdfppdfxhd on February 08, 2024, 06:27:50 PM
Quote from: Joe on February 08, 2024, 07:03:01 AM
Quote from: pspdfppdfxhd on February 08, 2024, 06:59:58 AMBitcoin?
Nope.
Carrier pigeon dropping off a bag of cash?
Title: Re: Excel stuff
Post by: DigiCorn on February 10, 2024, 12:04:13 AM
Quote from: pspdfppdfxhd on February 08, 2024, 06:27:50 PM
Quote from: Joe on February 08, 2024, 07:03:01 AM
Quote from: pspdfppdfxhd on February 08, 2024, 06:59:58 AMBitcoin?
Nope.
Carrier pigeon dropping off a bag of cash?
My old roommate owned a baseball card store back in the day. The property owners he leased the building from kept blocking the entrance to the store with dumpsters, and he kept calling and complaining and they never took care of the issue. One day he snapped and went around town to like 15 different banks and cashed in something like $1000 cash for rolls of pennies. Then me and him dumped pennies into trash bags and delivered it to the property management company to pay his rent on the shop.

The dumpster was immediately removed.