Skip navigation
Currently Being Moderated

How do I insert a CSV file that has blank fields? (Some  fields need to be blank) CS4

Dec 11, 2012 1:49 PM

Hi, this is the first time I have done a data merge in InDesign. I am trying to insert an address list that often has blank fields in it. The fields are blank since some fields are not relevent to some people (i.e. some of the people don't have cell phones others don't have email addresses) Therefore these people would obviously leave those field blank. The trouble is when I go to do a data merge I am told that the CSV file won't import because some fields are blank. I don't know why InDesign is telling me this, or course some fields are blank they are not relevent to that person. Can someone please tell me how I can get around this problem?

 
Replies
  • Currently Being Moderated
    Dec 11, 2012 1:52 PM   in reply to pik80

    Sounds more like you have a blank cell in the column header row, but there is data in that columns somewhere below...

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 11, 2012 11:59 PM   in reply to pik80

    Are  you saying that you have (for example) this:

     

    ex1.png

    …with two numbers in one field? I think the best way to work with data is to split it up into Phone 1 and Phone 2 in the data source, and when there is no Phone 2, you won't get the gap.

    ex2.png

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 12, 2012 3:53 AM   in reply to pik80

    pik80 wrote:

     

    Great,  it allowed me to import that CSV file. The problem now is that it will leave a blank gap in InDesign if the field was left blank. For an example I had one line dedicated for the phone number and then used a paragraph return to put in the cell phone number below that.

    You CANNOT have any sort of line breaks in your cells in the Excel file. It's just not supported in the .csv and it will cause you to have extra records with the wrong lengths and totally screw up everything. You should have separate columns for the land line and cell numbers, and as long as there is nothing else (including whitespace or punctuation) on the line that contains those placeholders you can use the "remove blank lines" feature to remove a line for a missing phone number. This might mean you need to add the text prefix that identifies the type of phone number in Excel. You can do that by adding a few more columns, one for each prefix, and one with a formual for each type of number. The formula should check to see if there is actually a number in the cell and if there is it should concatenate the prefix and number, if not it should leave the cell blank. You then use those columns insted of the actual number columns.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 12, 2012 3:56 AM   in reply to Peter Spier

    And if that's too complex for you, you can always set up a find/change query that looks for lines that don't end in a digit (you should assign a differnet paragraph style to phone numbers to make it easy to control the scope) and remove them.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 15, 2012 4:12 AM   in reply to pik80

    Might be the document setup. Multiple record merges can only be done on a single-page merge template, and you should have no more than one set of placeholders, whcih should be in the upper left postion. Also make sure there is nothing else on the page that would be duplicated and move off the page in a multiple record merge. The merge is going to run as if everything inside the rectangle that encloses all page elements needs to be duplicated, so stray objects or large frames can casue a problem when there is not enough space.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 3:34 AM   in reply to pik80

    I notice that's page 4. Are there pages 1-3?

     

    A multiple record merge template must contain exactly 1 page. If you set up as facing pages, your merged file will use only the  pages that ae on the side of the spine that holds the placeholders. You can fix that by going to Document Setup and unchecking facing pages, close the dialog and reopen, then recheck facing pages, or you can convert the merged doc from a non-facing template to facing pages the same way.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 10:21 AM   in reply to pik80

    Multiple record merges can't handle mirrored layouts directly. If you design carefully, though, you can create a symmetrical layout, or you can make sure everything snaps to guides and use Layout Adjustment to shift objects when you apply a two-page master after the merge. You als will have to add your non-merged pages after the merge.

     

    Data Merge is a very dumb (unsophisticated) module, sedigned for doing things like sheets of address labels. As your project gets more complex you have to start thinking about ways to work around that or consider a commercial plugin.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 3:10 PM   in reply to pik80

    You can't merge two data files into one document, so the answer is probably that you'll have to do something like that.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 8:38 PM   in reply to pik80

    Maybe irrelevant, but I just enter a space where I need a blank field - and InD ignores the space entry when using the csv.

    Always worth checking the csv in a text editor too - Excel is notorious for changing code based on hidden formatting.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 19, 2013 1:02 AM   in reply to pik80

    Don't know for sure, but I do see that the page should populate rows first before columns according to your settings, but there's only one record per row which makes me think ID doesn't know there is enough space. Try reducing the size of the right and bottom margins.

     

    Also check to be sure there isn't some other extraneous object that is taking up space, and do the merge without previewing (it's OK to preview as long as you UNDO before you hit the merge button itself).

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 31, 2013 5:07 AM   in reply to pik80

    Looks to me like there are spaces typed between your fields. Those are characters, and the lines are not blank.

     

    You can clean it up using find/change.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 1, 2013 3:12 AM   in reply to pik80

    This is a bit confusing to users (and it bit me, too, the first time I used Data Merge). Whitespace, or punctuation, you type into a line with a field placeholder renders that line non-blank no matter if all the fields for that record are null or not. Depending on the use case, sometimes you can build the punctuation into the spreadsheet, and sometimes you can create a new field by doing a concatenation of other fields in the spreadsheet to combine |FirstName|LastName| into "FirstName LastName" or "LastName, FirstName" so you only have a placeholder on the line in ID, but that isn't always practical or even possible.

     

    I'm doing a directory right now that suffers from this issue, and I've saved a group of find/change queries that I run after data merge to remove things like lines with an "email: " tag but no merged address or lines that have nothing but spaces or punctuation in them.

     

    I don't see a line in your sample that has punctuation, but you have two, or perhaps three, cases for white space that needs to go.

     

    Find ^\s+\r and replace with nothing will remove the "blank" lines

     

    Find ^\s+ and replace with nothing will remove whitespace at the beginning of a paragraph

     

    Find \s+$ and replace with nothing will remove blank space at the end.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 1, 2013 12:13 PM   in reply to pik80

    pik80 wrote:

     

    I couldn't get the find change operations to work so I don't know if I am doing something wrong. I get the message "search is complete. 0 replacement(s) made."

    When that happens to me it's usally because I entered the GREP code into the plain text search tab.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 1, 2013 1:37 PM   in reply to pik80

    Jongware might have a clever way of not removing deliberate blank paragraphs, but the real answer, I think is that you shouldn't be using them. If you want space between groups you should be using either separate frames or space before to push the first paragraph in the group down (presuming that one will never be blank) or space after on the last paragraph in a group if it will never be blank.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 1, 2013 4:38 PM   in reply to pik80

    I've never seen that, so I think, yes, it's cause for concern. Do you see a problem anywhere with the merge? Do you, perhaps, have some sort of master page object in your template that you overrode onto the document page?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 2, 2013 4:04 AM   in reply to pik80

    I haven't looked closely at what changes might have been made to Data Merge recently, but back in CS4 when it worked fairly well (i.e the preview wasn't broken) when doing a multiple-record-per page merge you would want either to put your merge fields on the document page, and use a blank master, or put them on teh master page and group them with anything else that needed to be duplicated with them. If they were on the master page other master objects would be ignored unless grouped with the field placeholder frames.

     

    Putting the fields on the master page is supposed to allow you to update the merged file if the data source changes. I've not found that to be reliable, especially with photos, nor particularly necessary. Most merges are so fast it's not a big deal to simply run the merge again if the data changes, so I mostly use the document page for setup and apply the None master to the template, then apply the real master to the pages inthe merged document.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 18, 2013 10:59 PM   in reply to pik80

    I have two projects that I merge each year, and I use the built-in feature. I've never used any of the third-party catalog plugins, which I presume would give you more flexibility. XML might be another option, but I know nothing at all about XML workflows

     

    What sort of editing are you doing? My larger project is a directory of around 200 names, and all of the formatting is built into the template for that. I need to run a couple of find/change operations to remove lines that are nothing but punctuation or white space as described above, and I manually stitch together all of the records into a single story, but that can be handled by the Text Stitch script from Rorohiko.com if you prefer. I rerun the the merge each year rather than trying to update.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 19, 2013 11:01 AM   in reply to pik80

    That's waht I meant about threading the the records (I work from the back to the front and click the inport of the last record, then inside the next to last, and so forth). I think the threading is faster than cut/paste, and as I mentiuoned Rorohiko has a script that will do it for you. Once it's threaded into one story, I find you can do pretty much anything you want -- export as text (RTF or Tagged Text) or copy/paste. I do the latter for my directory to get a running thread from a single-record per page merge that I use to make an alphabetical TOC showing the listing numbers (my names are styled as a numbered list, so the numbers remain the same after they flow together, but there's no easy way to generate the TOC unless I have one record per page. I copy that story into my new directory file, too).

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points