Problem importing a CSV file with forward slashes in a column

Question

Level 1

8 points

Problem importing a CSV file with forward slashes in a column

I have an Excel csv file of a product database (contains about 6500 products) that contains product codes with forward slahes such as 499/1, 499/3, 499/5.

These are different sizes of a product and as such have different prices etc.

When I import the file into Numbers these numbers appear as 499, 166 1/3, and 99 4/5 respectively.

What seems to be happening is that Numbers is interpreting the forward slash (/) as a divide by command and subsequently performing a calculation on that number on import and hence totally changing the value of the cell so that it is impossible to look up the price related to a product as the product code no longer exists.

Excel can import these files with no problems, why can't Numbers treat each cell as text and leave it alone on import.

Is there any way round this or do I have to revert to using Excel for the import of csv files.

Thanks

Steve

MacBook Pro, Mac OS X (10.7.5), 17" - Last of the Big Boys

Posted on Feb 26, 2013 8:37 AM

Reply

Answer 1

Feb 26, 2013 8:44 AM in response to SteveB_UK

I think you will have to change the "/" into some other character in a text editor. You could try opening the csv in TextEdit, then select all, copy, then create table in Numbers, paste (this will make the table large enough), then select all in the table, delete.

Now select all in the table and convert the table to text (using the toolin the menu bar):

Now paste into the table.

Reply

Answer 2

SteveB_UK Author

Level 1

8 points

Feb 26, 2013 10:13 AM in response to Wayne Contello

Thanks, Wayne.

I tried this and although it works as a workaround it seems a long way round to what seems to be a simple process.

I'll probably go back to using Excel for the csv files until a Numbers can import them without meddling with the data.

Cheers,

Reply

Answer 3

HD

Level 5

4,945 points

Feb 26, 2013 2:19 PM in response to SteveB_UK

I tried this with a CSV file that happened to contain data like 22/5. It converted those elements into dates 😟. And like yours, anything that couldn't be interpreted as a date was turned into a calculation. Libre Office also does the trick if you don't want to use Excel.

Reply

Answer 4

Feb 28, 2014 2:32 AM in response to HD

I know I'm a bit(!) late (a year) coming to this party, but there is a simple solution, that worked for me, enclose the field in double quotes, and add a single quote before the number:

Instead of

499/1,"Super Widget 3",12.34

do

"'499/1","Super Widget 3",12.34

In fact the single (unclosed) quote without double-quotes works as well:

'499/1,"Super Widget 3",12.34

I've always found it better to enclose strings with double quotes. This works on loading the file into Mac Numbers and should work with Excel too if that helps. It opens with OpenOffice 4 on the Mac too - if you select "comma" in the "Separated by" checkbox

I can't remember where I picked this info up....

Hope this helps someone, albeit late.

Andy

Reply

Answer 5

Badunit

Level 7

30,720 points

Feb 28, 2014 4:08 AM in response to AndyBJetNet

But how do you get Excel (or other apps) to export the field with quotes or a leading apostrophe? That part of the process is out of our control and editing in a text editor after the fact would be very tedious.

CSV is not a good format for anything but text. If it includes other data types, a value such as 1/28 could be a fraction or a date. Nothing in the file tells you. Quotes around it would indicate text but the spec for CSV doesn't call for a quotes around text except when it contains a comma.

We now have a good solution in the form of an AppleScript. It will turn a CSV file into tab delimited text on the clipboard. You can then paste that text into a pre-formatted table and, as far as I can tell, it works great with all data types. The pre-formatting of the table determines how the pasted in data will be interpreted.

https://discussions.apple.com/thread/5477003?answerId=24788847022#24788847022

Reply

Answer 6

SGIII

Level 8

37,562 points

Feb 28, 2014 8:12 AM in response to Badunit

We now have a good solution in the form of an AppleScript.

https://discussions.apple.com/thread/5477003?answerId=24788847022#24788847022

The link to the script (Badunit made it a simple double-click to install) and usage instructions are in Badunit's post at the bottom of that thread (for some reason the link above goes to the top of the thread).

SG

Reply