csv formatting problem

Cem_Karaer

Hi everyone!

My problem can simply be put: Excel formats differently some expressions in a csv file than what they really are. Here is an example.
Say that I have a csv file with one as follows:

22205E1;1;1

But this line is shown as

222050 1 1 (think that values are in seperate cells)

22205E1 is an item number and it is critical to represent it in the Excel application as it really is because there will be some editing in the file using Excel. I don't want Excel to interpret it in scientific format.
I know this is not a NAV question. But I have had no clue about it.

Malajlo

There is a list separator in reg. settings. If it is not semicolon, but comma, your csv file will be not as you think it should. And also opening with excel does not automaticaly take semicolon as field separator.

Your question is hard to understand.
You are saying that you have semicolon separated line in csv, but you don't have it. ???
What is the real problem? You don't see semiclon separated data in excel in (your example) three cels but only in one?

Cem_Karaer

No I use ; (semicolon) as it should be. The problem is not about the location of values in the Excel sheet. Everything stays where they should. The problem is 222050 is shown for the value 22205E1.

Malajlo

OK, resize first column in excel, format column as text, not number. It is represented with exp1...

Cem_Karaer

Nop! Resizing, formatting, nothing brings my 22205E1 back to Excel. Because Excel thinks that 222050 is there. The content of the cell is wrong.

Malajlo

I managed to see
2,22E+05 1 1
Change column format to General or Text. It works at me (Excel 2007)
222050 1 1

Cem_Karaer

Wonderfull! But I don't want 222050 but want 22205E1

Malajlo

If you save file as CSV, then excel formats cells in a way that can not be controlled.
Opening txt file in excel, you have option to define column type. In your case text.
Other possibility is to change field start/end into something not used (÷×¤ß or ') and then run macro that deletes those chars.

And ultimate solution: use excel buffer.

Cem_Karaer

Thank you for your efforts Malajlo. In that case, Excel Buffer seems a practical solution. But my clients want to do some editing on that csv file and import the changed file using the same dataport. If only I don't have to use a dataport

Malajlo

They are using excel, not text editor.
Export to excel directly, then import from excel as well. It should work and also it is more "professional"

Stardust77

Add the sign ' before exporting the item number, so the value exported from Navision will look as '22205E1. That will make Excel to consider it as a text instead of a number.

Malajlo

I did a test using that but you have to define filed end delimiter. And it does not help using '/". Excel opens file with all ' and ".
Maybe excel 2007 does that way...

Stardust77

Malajlo, you are right, Excel is doing this always. Sorry, my suggestion is not working.

kine

When you open the .csv file from excel, and the import wizard is running, you need to set for correct data type for the column. In last step of the wizard, when you can just go back or finish import, select the column and set "Text" instead "General" type...

Cem_Karaer

When opening a csv file using Excel, no wizard runs first. How can I force the wizard to run? I am sorry for revolving the discussion around Excel.

kine

cemkaraer wrote:

When opening a csv file using Excel, no wizard runs first. How can I force the wizard to run? I am sorry for revolving the discussion around Excel.

Which version of excel?

SLF25

When opening a csv file using Excel, no wizard runs first. How can I force the wizard to run? I am sorry for revolving the discussion around Excel.

You need to rename the file to .txt or sth. Sometimes excel is just to smart.

Cem_Karaer

It's Excel 2003.

kine

see http://office.microsoft.com/training/tr ... 1801181033 for how to import text fiels.

Base is to use File-Open menu and not opening the file by clicking on it...

Cem_Karaer

Thank you kine. The point I got from the link is:
(within Excel menus) Data -> Import External Data -> Import Data
and finally choose the desired csv file. But this technique will not open the csv file but copy the contents of it into a blank sheet.
There is no way in Excel to run the Text Import Wizard by using the open menu.

kine

There is a way, but we do not know about it yet. I do not have access to office 2003 and I am unable to test it, but it must be somewhere in options or on the open dialog (some switch or option to use the wizard...)