Leading Zeros

Jeff,

I'll bet there's a solution of some sort out there short of reverting to Office. If the total number of digits is constant in this field we're talking about the Custom Format option will work for you.

It would look like this:



Regards,

Jerry

Jeff,

Would you mind doing a Copy/Paste from Pages rather than opening a CSV file? If you do a Find/Replace to add the single-quote character after every Return and to substitute TAB-Single-Quote for every comma, you can copy the result and Paste into a Numbers table, with everything converting to text.

Jerry

bainjeff wrote:
But how do I open the .csv file to get the info. Is there a way to import data into pages from a .csv file?

To open a csv file in Pages, change the suffix to txt and Pages will open it with no problem. You may have to use Get Info to set the default app to Pages for .txt files, but it's an easy thing to do.

I tried to do the find "," and replace with ",'" but then the already quoted text in my .csv file turned my quotes into text so all my discriptions for my parts looked like "Truck Part" (with the quotes).
bainjeff wrote:

I never said to use the quote symbol, I said Single-quote, by which I meant an apostrophe. Sorry if I wasn't clear. Adding an apostrophe after every Return character and substituting a TAB followed by an apostrophe for every comma-space directs Numbers to treat every piece of data as a text string. One little detail is that the first row in the first column will not be treated so you need to begin with an empty row.

Don't sound so ready to give up. This isn't that difficult.

edit: So, once you get your file edited in Pages, select the entire text block and Edit > Copy.

In Numbers, select a cell in the table where you want your data to begin; i.e., upper left corner. Then, Edit > Paste. Your data will move into this table. If the table isn't large enough, no matter, it will expand.

Good luck,

Jerry

Message was edited by: Jerrold Green1

Data as you say it occurs:





The Returns signify the end of a row of data.











This must be some sort of record.

Jerry

Hi Jeff,

I'm late to the party, but may be able to answer the specific questions in your most recent post.

bainjeff wrote:
1. I start with a .csv file. How do you suggest I retrieve this data? You mention Pages but pages will not open a .csv file. However I was able to open the .csv file in textedit and work with it.

If you can open the file in TextEdit and do the Find/Replace without problems, there's no need to open in in Pages.

If you need to open the file in Pages, the method is simple:
• Locate the file in the Finder.
• Click on the file's name, pause, then click again to make the name editable.
• Place the insertion point at the end of the filename and press delete (backspace) three times to remove the csv extension.
• Type txt to replace the file extension.
• Click outside the filename to leave editing mode.
• The Finder will probably present an alert warning that changing the suffix may result in the file being opened by a different application. Click to confirm that you want to change the suffix.

Right click (or ctrl-click) on the file's icon and choose Open with > Pages.

2.Do I understand you to say to replace the returns with a return and a apostrophe? If so how do you search for a return in find and replace? I tried option return but it just filled the whole filed with green. Is this a "return"?

In Pages, you can place a return in the Find or Replace boxes by pressing option-return. Also works for tab (option-tab).

This also works in TextEdit, but the Find/Replace dialogue does not display the symbols for these invisible characters.

3. I completely understand that your saying to replace all commas with a tab and an apostrophe.

However, last night I opened the .csv in textedit and simply replaced all commas with just a "Tab" and it seemed to separate all my data correctly. The only error was that any commas in my text had been converted into a tab. Once I had the Number sheet open I just found and replaced every "Tab" with a comma.

The commas in your data are the reason the CSV file included quotation marks to enclose each piece of data. Without the quotes, the data commas would be treated as separators.

To prevent changing data commas to tabs, change your search to find the three character string "," and replace it with tab'
You will also need to search "return" and replace with return'
And individually remove the quotation marks before the first piece of data and after the last piece(NOTE: tab and return above refer to the tab character and the return character, not the words "tab" and "return".)

I just want to make sure that I'm doing this as "correctly" as possible because I can not afford to mess up the data and find out about it later.

The best way to prevent messing up the data is to START by making a duplicate of the data file. Put the original in a safe place and work with the copy. Then if you do screw up the file you can go back to the original, copy it again, and continue to work with the (new) copy.

Regards,
Barry