Skip to main content
J
jade786
Known Participant
August 19, 2022
Question

NotePad file creating importing issues

  • August 19, 2022
  • 4 replies
  • 2503 views

A client sent me a NotePad file to be used as a newspaper legal for importing into InDesign. It has 27,000 delinquent tax entries and there are NO tab breaks for each column (TAX, YEAR, NAME, AMOUNT, COST, TOTAL), only a single space. I have tried every trick I know to turn this into tabbed entries, but nothing I do can differentiate between the single space and what should be a tabbed space.

 

I timed myself – to enter six tabs manually into 27,000 entries is 162,000 insertions, which takes about one minute each to insert – you literally have to read the whole stupid line to see where the tab should go. That will literally take me 18.75 days to import and create this legal!

 

I've asked them to send me the original spreadsheet as I could probably manipulate that into a tab delimited file or table, but so far all they sent me is NotePad. 😕😕

 

Does anyone know of a trick or script that could help? Please, I'm desperate!

This topic has been closed for replies.

4 replies

Robert at ID-Tasker
Robert at ID-Tasker
Legend
August 20, 2022

Or GREP will do the trick. 

Robert at ID-Tasker
Robert at ID-Tasker
Legend
August 20, 2022

Like others already mentioned - show us an example.

 

But - if you have access to WORD / Excel - it would be pretty easy to "fix" the data. 

 

1a) import your file into WORD - convert into table with space as a delimiter,

1b) import your file into Excel - convert text into columns - space as delimiter,

2) cut first two columns - TAX & YEAR - and paste into new document,

3) cut last three columns - AMOUNT, COST, TOTAL - into same new document - as additional columns,

4) in the new document - insert new column - after 2nd column - between YEAR & AMOUNT,

5) join columns you've left with in the source WORD (1a) / Excel (1b) file and paste it into 3rd column in the new document,

6) either convert into TAB delimited file or copy&paste directly to InDesign.

 

Or you can do all those steps in the InDesign itself - just import as plain text and convert into raw table and then above steps - creating extra table(s). 

 

 

See my next reply - sorry, should have gave it a bit more thought before posting - but still doable. 

 

James Gifford—NitroPress
James Gifford—NitroPress
Legend
August 20, 2022

OP notes they're used to working with crummy data. If all fields were the same length (in characters or words), it would be trivial to use Word or NotePad++ macros to delimit them. However, from the description of the info, most of those fields could have one to several words or word fragments.

 

Were it not for the sheer scale, I'd say do the best with macros and then hand-edit, but (1) as the OP notes, this could take tens of man-hours and (2) as others note, all this info comes from SOMEWHERE and it's very unlikely that it's not organized in a manner that would permit a CSV or tab-delimited output.

 

I agree that maybe the return request hasn't been clear; it's obviously going to someone who has access to a database or equivalent and doesn't understand that a raw text dump isn't usable. Make a very clear request for a comma or tab delimited file... and someone will understand that.

 

—

    J
    jade786Author
    Known Participant
    September 2, 2022

    Hi, everyone. I just wanted to thank you all for your help. I went to the treasurer's office and looked at their software. It's proprietary and unable to save or export in ANY format other than text. I even spoke with their IT consulting company and confirmed the inability to save in any other format. So we're stuck with what we have.

     

    Thanks again for all your help!

    Sherry

     

    JR Boulay
    Community Expert
    JR BoulayCommunity Expert
    Community Expert
    August 20, 2022

    Can you share about 10 (redacted or not) lines of this file?

    Acrobate du PDF, InDesigner et Photoshopographe
    James Gifford—NitroPress
    James Gifford—NitroPress
    Legend
    August 19, 2022

    I've done a lot of such conversions from clumsily exported data. Sometimes you can find a "key" that will let you put tabs in the right places, etc.

     

    At that scale, you will save a vast amount of time by going back to the client for ANY original version of the file or a new export using more standard separators. It sounds as if it was exported from Word, Excel or a database by someone who didn't really understand what needed to happen down stream.

     

    Explain to them clearly that you need records and fields clearly separated, and how something like CSV or tab-delimited files should be formatted. I'd ask exactly what they are exporting/retrieving the data from; that might help guide things to a more useful work file.

     

    —

    J
    jade786Author
    Known Participant
    August 20, 2022

    Thanks. I have years of experience dealing with badly exported files but this is the first time there's absolutely nothing I can do to fix it. I have asked – twice – for the original file and they keep sending me NotePad. I finally told my editor how long it will take me to do it manually and how much extra they'll owe me; I'm supposed to get the original file next week. Fingers crossed it actually happens.

    Thanks again! 

    N
    Nick Passmore
    Participating Frequently
    August 20, 2022

    Getting a properly delimited file would be best of course but if YEAR, AMOUNT, COST, TOTAL are all numbers or numbers preceded by a currency symbol, something like this —

       Tax Name 2019 Joe Blow $4500.00 $1200.00 $5700.00
       Another Name 2022 Fred Snurge $2300.00 $900.00 $3200.00

    would it not be possible to use a regular expression to insert the tabs?

    Can you show a few example lines?

     

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices