-1

Im exporting some data to a .csv file.

.csv file:

Hotel Name;Street;Postal Code;City;Latitude;Longitude
Hotel X;Street 1;00000;City X;15.000000;15.000000

But if i open it in Excel, Excel will Format the Latitude and Longitude automaticly so it cannot be used for copy & paste.

enter image description here

Ignore the 0 at postal Code, i must get rid of the 1.000 at Latitude and Longitude

How can i prevent Excel from doing this?

It should be done in the .csv file and not in Excel.

The Export Code:

foreach(...)
{
    StringBuilder_Export.Append(DataRow_Temp[i] + ";");
}

StreamWriter StreamWriter_Export = new StreamWriter(
    SaveFileDialog_Geo_Export.FileName, 
    true, 
    Encoding.Default
); 

StreamWriter_Export.WriteLine(StringBuilder_Export.ToString());

EDIT: Im searching primarly for a solution of my Latitude and Longitude Problem.

Hille
  • 2,123
  • 22
  • 39

3 Answers3

1

Possibly a duplicate of Stop Excel from automatically converting certain text values to dates but if you want to generate an Excel file that looks exactly as you want when opened, do not use csv. Excel will always attempt to guess datatypes of csv columns by looking at the first (15 i think?) rows..

I use EPPlus to generate xlsx files from my apps, but there are many libraries you could use

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • i should only use libarys which are preinstalled. Im not allowed to donload stuff :/ – Hille Nov 08 '17 at 08:58
  • 1
    Find an open source one and download it at home, incorporate it into your app code as if you rolled your own.. or read the code and roll your own- its a relatively simple thing to do(theyre just xml inside zip files with an xlsx extension) – Caius Jard Nov 08 '17 at 09:01
  • 1
    `Im not allowed to donload stuff` Except off stackoverflow, apparently. :P – mjwills Nov 08 '17 at 09:30
  • what do i download on stackoverflow? @mjwills – Hille Nov 08 '17 at 10:24
  • Strictly speaking, every time you visit an SO page, you download from stack overflow.. What, you never copied anything off SO and pasted it into your code? – Caius Jard Nov 08 '17 at 10:27
  • I'm curious; what's the difference? – Caius Jard Nov 08 '17 at 10:45
  • Between: You cant get around to work properly and you download a file from a suspicios Homepage @CaiusJard – Hille Nov 08 '17 at 12:18
1

Change its extension to txt and use the text import wizard. Then use that to tell excel how the columns should be treated (text, currency, date, etc). The text import wizard will start automatically when you open a .txt file

  • I'm not sure this is an answer to the problem, as it essentially passes the inconvenience of avoiding an undesirable Excel behaviour, onto the user when it could be avoided by the developer. Would you be pleased if your online banking allowed you to "download your statements in excel here", but sent you a text file carrying the advice "after downloading, use the text import wizard in excel to import your statement data , otherwise the formatting will appear all messed up", simply because the dev team couldn't be bothered exporting it in a more appropriate format? – Caius Jard Nov 08 '17 at 09:22
  • Its an answer in so much as it achieves the desired result. Whether its the right thing for the OP to do is another matter imho –  Nov 08 '17 at 09:25
  • ist not what im searching for, but it works fine as Long as i cant fix it in Code @CaiusJard – Hille Nov 08 '17 at 10:11
  • @ojf by that logic the OP might as well just send the user any irrelevant file type and tell them it's their problem to get it into Excel? :) – Caius Jard Nov 08 '17 at 10:14
  • for the first customer i can do it by Hand and send him the edited Excel file. But the Problem isnt solved... i never said that! @CaiusJard – Hille Nov 08 '17 at 10:18
  • Would the customer accept an html file, they can open in any browser - you can format it as you want, and they get data they can copy/paste it fine.. Even into excel if they want to. Sending a CSV immediately causes a problem because people think it's an excel file, or it tries to open in excel and gets all munged up (Excel's fault). Send it as a file that isn't immediately obviously an excel file, set the customer expectation that it's not an excel file, don't put their brain into "this file must be opned in Excel" mode, and the problem goes away :) – Caius Jard Nov 08 '17 at 10:20
0

ensure the file is in UTF-8 format when you save it as a .csv

Then open excel, browse to select the file you want and it will automatically run the prompter.

Any other format than utf-8 and excel will try and auto convert.

Andrew Furness
  • 1