Announcement

Collapse
No announcement yet.

CSV Format Issue with UPCs

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • CSV Format Issue with UPCs

    Hi everyone,

    I battle this issue all the time, but it seems that no matter what I do, some alwasy slip through. Do any of you have problems with digits of long strings of numbers (like UPCs) being dropped or switched to zeros when uploading? It seems that Excel automatically puts long strings of numbers into scientific notation when saving as a CSV file, which causes a problem when uploading. I try to be very careful and reformat the Excel column just before saving and uploading, but I am constantly finding products with incorrect UPCs. For instance, it will change 661120409076 to something like "6.6112E+11" and it seemly randomly uploads as 661120000000.

    Do any of you have this issue? I guess the most frustrating thing is that I cannot pinpoint exactly what is causing it. Most of the time, they upload fine with the full string of data, but I do stll often find errros and I do not know why some work and some do not. Is it just me, or are you having the same issue?

    Thanks!

    David Cox
    www.totaloutdoorsman.com - Your Total Choice for the Outdoors

  • #2
    Originally posted by dcox View Post
    Hi everyone,

    I battle this issue all the time, but it seems that no matter what I do, some alwasy slip through. Do any of you have problems with digits of long strings of numbers (like UPCs) being dropped or switched to zeros when uploading? It seems that Excel automatically puts long strings of numbers into scientific notation when saving as a CSV file, which causes a problem when uploading. I try to be very careful and reformat the Excel column just before saving and uploading, but I am constantly finding products with incorrect UPCs. For instance, it will change 661120409076 to something like "6.6112E+11" and it seemly randomly uploads as 661120000000.

    Do any of you have this issue? I guess the most frustrating thing is that I cannot pinpoint exactly what is causing it. Most of the time, they upload fine with the full string of data, but I do stll often find errros and I do not know why some work and some do not. Is it just me, or are you having the same issue?

    Thanks!

    David Cox
    We also originally had problems with the leading zeros being dropped off UPC's, so after we got the ones we wanted in 3d, we just delete that column/field from any imports in order to not overide what we already have.

    Here is the workaround I use specifically for UPC's:

    Separately, I've created a product custom export set for my upc's when I want to work on them, which includes the catalogid, name and the field I use for UPC's. After I export, I open up the file with Wordpad and then save as txt. I then go to excel and open the text version and in Step 2 of excel's text import wizard I change the delimiter form tab to comma and then step 3 I highlight the column for my UPC field and change the data set to text and then finish. I re-highlight the colum once the spreadsheet opens and change the format once again :( to text and after I work on the file I save as csv and then reimport to 3d and select UPDATE. Again, I do this only for UPC's.

    I'm not sure if this is the best way to do it, but it's the only one I've found that works .
    Last edited by cosmic; 07-10-2011, 08:54 PM.

    Comment


    • #3
      (I generally hate MS software ... but) A few things I myself have noticed:
      1. The "TEXT" issue that cosmic points to - sometimes I cannot get my copy/paste to work unless I save "as special- text" for very long dta in a cell. Many times excel will shorten the data unless I do the save as dance!
      2. Make sure you set your "Decimal Places" correcly for columns that are set to NUMBER, CURRANCY, ACCOUNTING, etc. Sometimes it is a problem for me, sometimes not!
      3. I have also noticed that my "Column Width" of my original .xls file will sometimes affect the "save as .csv file". If the column in the .xls file is not wide enough, the transformed data may be truncated OR as you are seeing, shown as a formula of sorts!

      Maybe it's just ghosts in my machine!?!?!? Or is it just MS software!?!?
      (Enter chilling halloween organ music)
      BTW: I tried cussing, yelling and offering goat entrails, to no avail!

      Comment


      • #4
        3. I have also noticed that my "Column Width" of my original .xls file will sometimes affect the "save as .csv file". If the column in the .xls file is not wide enough, the transformed data may be truncated OR as you are seeing, shown as a formula of sorts!
        .csv does not retain formatting of column or rows; therefore, it will always set to default.
        If you cells are set to "Text" it will show you the formula not the content.

        For UPC, you can set the column to custom format like 0000000000000 that will retain the format of that column in xls. Once you upload the correct upc code no need to download and reupload. Another way of retaining the digits of the UPC is enclosing the UPC code in quotes ("661120409076")
        Elegant Weddings +
        www.elegantweddingsplus.ca
        www.elegantweddingsplus.com

        Comment


        • #5
          Try inputting an apostrophe (') before the UPC number. That apostrophe tells Excel that you want to keep that field exactly like it is entered (don't drop leading zeros, don't reformat), but that apostrophe shouldn't show up on the cell data itself.

          Comment


          • #6
            Originally posted by dcox View Post
            Hi everyone,

            I battle this issue all the time, but it seems that no matter what I do, some alwasy slip through. Do any of you have problems with digits of long strings of numbers (like UPCs) being dropped or switched to zeros when uploading? It seems that Excel automatically puts long strings of numbers into scientific notation when saving as a CSV file, which causes a problem when uploading. I try to be very careful and reformat the Excel column just before saving and uploading, but I am constantly finding products with incorrect UPCs. For instance, it will change 661120409076 to something like "6.6112E+11" and it seemly randomly uploads as 661120000000.

            Do any of you have this issue? I guess the most frustrating thing is that I cannot pinpoint exactly what is causing it. Most of the time, they upload fine with the full string of data, but I do stll often find errros and I do not know why some work and some do not. Is it just me, or are you having the same issue?

            Thanks!

            David Cox
            I get this and no longer use a file to update UPC numbers. It goofs them up and if there is a zero at the beginning, it messes them up too.

            Comment

            Working...
            X