Shopping at LEGO or Amazon?
Please use our links: LEGO.comAmazon
As an Amazon Associate we earn from qualifying purchases.

Importing Database to Excel issues

oinoin LegolandMember Posts: 4
Apologies if this has been answered before.  I just spent the last week updating my collection into the Brickset database.  Then I attempted to export it to Excel.

I found an unusual anomaly that I do not know how to fix...

Any set with a 4 digit number came out as a date under the SetNumber column:

eg: 4195 appeared as Jan-95

3 digit number and 5 digit numbers were no problem:

eg: 628 appeared as 628-1 and 10194 appeared as 10194-1

I'd like to get the 4 digit numbers reading correctly...
Any Excel experts out there that could help?

Additional information:
The 5 digit numbers appear as 10194-1 in the input box when the cell is highlighted
The 3 digit numbers also appear similarly: 628-1
The 4 digit numbers have an extra bit that is probably causing the issue: 4195-01-01

Many thanks in advance!


Comments

  • Switchfoot55Switchfoot55 The Northwest, USAMember Posts: 2,308
    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.  
  • HuwHuw Brickset Towers, Hampshire, UKAdministrator Posts: 6,914
    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.
  • oinoin LegolandMember Posts: 4
    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.

  • benbacardibenbacardi EnglandMember Posts: 703
    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. 

  • oinoin LegolandMember Posts: 4
    Thanks benbacardi - I'm off to read your article now!
    benbacardi
  • oinoin LegolandMember Posts: 4
    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


    Switchfoot55benbacardi
  • benbacardibenbacardi EnglandMember Posts: 703
    No worries, glad you got it sorted. 
Sign In or Register to comment.

Shopping at LEGO.com or Amazon?

Please use our links: LEGO.com Amazon

Recent discussions Categories Privacy Policy Brickset.com

Howdy, Stranger!

It looks like you're new here. Sign in or register to get started.

Brickset.com is a participant in the Amazon Services LLC Associates Program, the Amazon.com.ca, Inc. Associates Program and the Amazon EU Associates Programme, which are affiliate advertising programs designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

As an Amazon Associate we earn from qualifying purchases.