Anyone know excel?

[txcynna]

Many of our customers submit business card data in an Excel file. Normally I save it as a comma separated value file and just import that into Indesign and assign the fields. This one is special.

Visually I can see a frame on the spread sheet around the actual information that I need. I need to know how to get rid of this frame. Indesign will import the data fields on the OUTSIDE of this frame but nothing from the inside. I know nothing about how excel works so any help in getting rid of this frame or even telling me what it is so I can figure it out myself would be greatly appreciated.

 

[Shaja]

Without seeing your file, I'm guessing that your customer might have put the data in a text box instead of in a cell. Can you click on the frame and it highlights? If that happens, do you see dots and circles along the perimeter of the frame? And, when you rest your mouse on the highlighted frame, at the tip of your mouse pointer can you see a four-sided arrow?

_______________
PS - It's just a jump to the left......

 

[txcynna]

Without seeing your file, I'm guessing that your customer might have put the data in a text box instead of in a cell. Can you click on the frame and it highlights? If that happens, do you see dots and circles along the perimeter of the frame? And, when you rest your mouse on the highlighted frame, at the tip of your mouse pointer can you see a four-sided arrow?

_______________
PS - It's just a jump to the left......

lol!

I really didn't make the question clear did I... my bad.

What I have is a 7 column by 15 row section of the spreadsheet that has a visible 2 pt(ish) frame around it. It is not a single cell. I thought it might be a print area box but it's still there after 'clearing' the print area. I have not been able to click on it to select it.. I accidentally moved it once to include one cell more on the last row... I am not sure how I did that because I was wildly flailing the mouse around in frustration.

I have found a go around so I don't have to actually typeset all this info but it takes a little more time to do it this way. I would really like to know what this thing is so I can get rid of it if possible and not waste time 'fixing' it in the future.

I'm uploading a sample excel file with the frame (edited down to less rows). If I'm the only one that has this prob or can see the frame, I will chock it up to I need an upgrade.

As always, all help is greatly appreciated.

 

[Coolio]

It appears that the customer set a border around the cells. You can try two things.

1. Select a cell that touches the 2pt rule. Then right click to format the cell. Under the Borders tab you will find many options. Select "none" and save. This should remove the 2pt rule from just that cell.

2. Select all of the cells and follow the same procedure.

HTH

Greg

 

[KevinC@EFI]

There doesn't appear to be anything "special" about those cells in the sample you attached... they just have a border defined. This is visual only - the data is "normal".

I'm not sure why InDesign would have a problem with that - but try the attached version to see if it works any better.

If it works - the only thing I did was select the whole sheet (click the top-left corner of the active area, above the "1" number of the first row, and left of the "A" for the first column), then get rid of the borders by reformatting the cells. ("Format" / "Cells" / "Border" / None)

Kevin.

 

[Shaja]

Very quick reply because I have to step into a meeting. But, just wanted to confirm that I looked at your file, and I'm puzzled as to why you can't use the data either. It's not a text box, and it's not a question of merged cells.

The "frame" is actually a border that's been applied directly to the cell itself, and as it's just a formatting preference it should not have any impact on using the data. (Right click on one of the cells with a frame and pick "Format Cells." On the box that appears, click on the Border tab. This is where you can get rid of the lines, if you'd like.)

The other thing I looked at is what kind of data is in the cells. If you click on the Number tab in the same box, it appears that they've all been given a "General" format, which again means that your data ought to be coming over cleanly.

Maybe another forum member can try doing a merge with your file and see how it works for them. I'll be free again later this afternoon and can try it myself then if still needed, although I don't use InDesign (I do use PrintShop Mail).

 

[Steve_S]

It has to be something quirky with the data merge in In Design reading the file. I get the same thing in ID but I can access all the fields in PrintShop Mail, which leads me to believe the database is not corrupt.

 

[txcynna]

Thanks for all the suggestions. Knowing nothing about excel I was and am still completely boggled.

I tried removing the border and it did get rid of the frame. I had hoped that would work but it actually had no effect. Sadness. I really wanted to blame it on something I could see.

I did find that removing the 3 columns prior to the frame DOES work... so I'm assuming there is something in those columns (or just the last one) that Indesign sees and decides there is nothing useful after that point. Of those 3 columns, the only fields that Indesign acknowledges are 'template' and 'appt'.

Since those columns are not necessary to the job I have no problem deleting that info and that should work for the future. Still don't know what it is making it happen but I have a work around for now.

Thanks again for all the suggestions. It's a puzzle. Normally I like those. lol!

 

[Shaja]

I like puzzles, too.

Two more thoughts.

When you said "clearing the print area" in your second post, did you mean that you were using Excel to specify the cells, columns and rows within the spreadsheet that should be printed? (Not simply deleting the contents of the cells or deleting cells.) I'm wondering because your spreadsheet shows that the defined print area is columns D-J, which would match up to the columns that you deleted. I'm wondering if InDesign is having a problem getting past columns that are outside of the defined print area.

There also might be something with the formatting of the cells themselves. I found a discussion on an Adobe forum similar to this topic at Adobe Forums: Excel and Data Merge. Scroll down near the bottom where they talk about "wrap text." I looked at your spreadsheet, and sure enough, just like in that discussion several of the cells in your spreadsheet are formatted with "Wrap Text." Try removing that format (highlight the cell or column, right click, Format Cells, Alignment tab, uncheck the wrap text box).

 

[Stephen Marsh]

I generally save as .csv in Unicode format, which removes most issues with source files.

Sometimes I re-import the .csv back in as a spreadsheet, clean it up, then save another .csv file.

Sometimes the issue is with the .csv, a text editor usually helps in these cases.

DataMerge can sometimes have weird bugs, it helps if you can search for reported error messages as listed in InDesign when one attempts to create a merge, in these cases a Google search is your best tech support!


Stephen Marsh

 

[otherthoughts]

I believe that @Shaja narrowed down very nicely, the issue to being "Word-Wrap" related.

But this leaves us with the question. "Why is it that @txcynna was able to delete the first three columns, and then successfully import the remaining "Word-Wrap" formatted cells into ID?

"I did find that removing the 3 columns prior to the frame DOES work... so I'm assuming there is something in those columns (or just the last one) that Indesign sees and decides there is nothing useful after that point. Of those 3 columns, the only fields that Indesign acknowledges are 'template' and 'appt'.

Since those columns are not necessary to the job I have no problem deleting that info and that should work for the future. Still don't know what it is making it happen but I have a work around for now."
​

I suspect the reason is because the only cell that was actively being "Word-Wrapped" in the excel file, was the Heading cell, B-1.

If you were to widen the B-column to accommodate the words "on Reverse" in cell B-1 on the same line, then the Word-Wrap at cell B-1 would be eliminated. If you then save the file out, as a comma separated value file and then are able to import the comma separated value data normally into ID. You would know that only cells that are actively being "Word-Wrapped" are problematic.

Best Regards
OtherThoughts

 

[Stephen Marsh]

Hmmm....you raise many questions OtherThoughts!

As mentioned, the header at B1 is the problem.

If one looks at the .csv exported out of the original .xls file, one will see that there is a line break in the problem header B1. If one removes this line break in the text editor, the .csv imports correctly.

It just goes to show that source data needs to be really "clean" before performing a merge (either in the source file or in the exported .csv).

Screen shot attached indicating the text edit in question. I agree, this is a tricky one!


Regards,

Stephen Marsh

 

[gohdan]

HI, I see why your excel file don't work. Problem is the number column. You need to split the office no into a new column and the direct no into a new column and the fax into another new column. Then it will work . That mean each column only contain one field only.
So when you import the data file it will see it as 3 different field.
Hope this will help

 

[Stephen Marsh]

HI, I see why your excel file don't work. Problem is the number column. You need to split the office no into a new column and the direct no into a new column and the fax into another new column. Then it will work . That mean each column only contain one field only.
So when you import the data file it will see it as 3 different field.
Hope this will help

gohdan, the issue that you mention is different to the other word wrap related issues mentioned earlier in this thread, that were causing problems as they were in the first row, or "header".

I agree with you that the multi-line cells in the F and G columns are a separate issue for InDesign data-merge though!

This .xls file is a good example of a "visual" spreadsheet, that looks/prints fine - but is a nightmare to use for data merge if there are more than an handful of names.

I would be sending the file back to the customer for them to fix if this contained many cards, or charging them like a wounded bull if they wanted me to fix their file.


Best,

Stephen Marsh

 

[txcynna]

Thanks everyone.
Luckily this client rarely sends in more than about 10 cards at a time so fixing is not a serious issue. IF this was one of our 'we need cards for 130 people' clients I would send it back and make them fix it.
I will be sending an email to this client though and see if we can't get them to correct their excel file but I'm afraid that this will be one of those "But we've always done it this way I can't change it nao raarrrar raaarrrrrr!'" situations.

Thanks again to everyone that dove into my problem and found the root cause!

 

[pcmodem]

From briefly looking at the file. Someone forced line returns (Alt + Enter on Windows). When you export to a csv file and open with a text editor, you can see the rectangle characters showing this.