Importing Database to Excel issues

Comments

• Switchfoot55 Member Posts: 3,475

  October 2020

  Perhaps I'm under thinking it, but it appears to just be a cell formatting issue. The cell is formatting those numbers as dates. You just need to reformat them to numbers with no decimals. At least, that's what I would think.  

  0
• Huw Administrator Posts: 7,116

  October 2020 edited October 2020

  Yes it sounds like you just need to highlight the column(s) then select 'text' from the cell format drop-down in the toolbar.

  0
• oin Member Posts: 4

  October 2020

  Thanks for the suggestions...
  I did try that at first and it seems to 'mutate' the number
  
  eg:
  Airport Set#3182 imports as Jan-82 to a clean new Excel sheet.
  If I highlight the cell the input box shows 3182-01-01
  If I change the formatting to 'Text' (or any other format not a date) the result is: 468243
  
  So I am not sure what is happening there - for reference I am clicking the "Export" button and then "As CSV" on the following page (I get the same result if I use "Tab Separated" )
  
  The file is downloaded as a .csv
  
  I then open it directly in Excel with all the anomalies
  
  In case it makes a difference I am running Windows 10 Pro on a PC and downloading from a Microsoft Edge Browser and opening into MS Office/Excel Home and Student 2016
  
  Do you think a different Browser might help?
  I could change all the set numbers manually but I have close to 230 4 digit sets...
  
  Open to any suggestions...
  
  Again thanks for your help so far.
  
  

  0
• benbacardi Member Posts: 712

  October 2020

  This won't be about the browser or the CSV—the file you get is a perfectly valid CSV format, it's Excel that's messing up the data by assuming that everything that looks like a date should be formatted as a date, which obviously isn't true. It's made worse by the fact that you can't then easily retrospectively undo that auto-formatting, as you found out above. 

  
  

  It would be great if there was a "don't auto-format my data" option when importing CSVs, but there doesn't seem to be. 

  
  

  There may be some workarounds you could use here, though: https://www.winhelponline.com/blog/stop-excel-convert-text-to-number-date-format-csv-file/

  0
• oin Member Posts: 4

  October 2020

  Thanks benbacardi - I'm off to read your article now!

  

  1
• oin Member Posts: 4

  October 2020

  Success!
  Thanks benbacardi - much appreciated!
  
  Although its not a one click import it sure beats manually changing over 200 of those set numbers one-by-one.
  I appreciate it.
  
  Cheers
  
  
  

  

  2
• benbacardi Member Posts: 712

  October 2020

  No worries, glad you got it sorted. 

  0