2

I am trying to import a large file. It is under the 10MB limit, however it has 44k rows, and the standard import excel function only allows for 5k rows.

Copying is limited to 500 lines.

Is there any way to import such a large file, or can new imports be appended to existing smartsheets?

Thanks,

Adrienn

Functionality

Comments

Adrienn,

 

One free way to get around the SS limits on data imports is to write an AutoHotkey Script to copy and paste 500 lines at a time.  It's not as elegant as a simple import, but I have been using it to work around the SS copy/paste limitations.

 

Attached is a script I have written that works well (it also performs a VLOOKUP).  You can modify it to meet your needs.  Although, 44k rows is one beast of a spreadsheet.  Sounds like you need a DB.

 

Sorry for the long post, but I could not attach a txt file to the forum Frown

 

#d::

 

;##########Customized for xx with 1100 lines

 

;Win+d copy bug numbers from SmartSheet, paste it into Excel and do a VLOOKUP on the bug status.

;Then paste the bug status back into SS 500 rows at a time 

;(because SS is limited to how many rows you can paste at one time)

;!!!!!!!!!!!!!!!!

;Make sure that all of the rows are expanded in SS BEFORE running this script, 

;Make sure that the browser window and Excel windows are both at full screen sizea

;Ensure that you start with the program in Excel, and that when you hit Alt+Tab once you go to SS

 

;At the point below, where you are copying the information back into SS

;You will need to customize the rows so that Excel copies back just what you need

;The default for the script is to copy less than 500 rows of bug infomation from Excel to SS

 

 

;Start in the excel tab labeled ###.b and clear out the old data

 

   SoundBeep

   Sleep, 100

   Send, {CTRLDOWN}aa{CTRLUP}

   Sleep, 300

   Send, {DELETE}

   Sleep, 400

   Send, {ALTDOWN}{TAB}{ALTUP}

   Sleep, 500

 

 

;Switch to the Test Matrix Page in Smartsheets and copy all the data

 

   SoundBeep

   Sleep, 100

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 200

;1100##############

   Send, {SHIFTDOWN}{DOWN 1100}{SHIFTUP}

   Sleep, 2500

   Send, {SHIFTDOWN}{RIGHT 16}{SHIFTUP}

   Sleep, 2000

   Send, {CTRLDOWN}c{CTRLUP}

   Sleep, 1200

 

 

 

;Automatically shift back to Excel and paste in the data

   SoundBeep

   Sleep, 100

   Send, {ALTDOWN}{TAB}{ALTUP}

   Sleep,1500

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 300

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 300

   Send, {CTRLDOWN}v{CTRLUP}

   Sleep, 4000

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 300

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 300

 

;Copy the vlookup into the correct column in Excel and save the worksheet

 

 

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 5}

   Sleep, 100

   Send, {F2}=IFERROR(VLOOKUP(F1,Table_bugzilla_Trento_bug_severity1[[bug_id]:[bug_status]],2,FALSE),"")

   Sleep, 600

   Send, {ENTER}

   Sleep, 200

   Send, {UP}

   Sleep, 100

   Send, {CTRLDOWN}c{CTRLUP}

   Sleep, 900

   Send, {DOWN}

   Sleep, 100

;1100##############

   Send, {SHIFTDOWN}{DOWN 1100}{SHIFTUP}

   Sleep, 2500

   Send, {CTRLDOWN}v{CTRLUP}

   Sleep, 900

   Send, {CTRLDOWN}s{CTRLUP}

   Sleep, 8000

   SoundBeep

   Sleep, 100

 

 

;!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!

;This is where you do a customization of row count

;Copy the bug status column back into Smartsheet 

;1100##############

 

 ;Copy column 18 cells 1-500 rows in Excel (or 1->n, where n < 500)

 

 

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 5}

   Sleep, 100

   SoundBeep

 

 ;If you have less than 500 rows in SS, edit the number next to {DOWN 499} to match your humber of rows +10

 

   Send, {SHIFTDOWN}{DOWN 499}{CTRLUP}

   Sleep, 100

   Send, {CTRLDOWN}c{CTRLUP}

   Sleep, 100

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   SoundBeep

   Sleep, 100

 

 ;Move back to Smartsheet and paste cells 1->500 (or 1->n, where n < 500)

 

   Send, {ALTDOWN}{TAB}{ALTUP}

   Sleep, 2000

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 4}

   Sleep, 100

   Send, {Right 5}

   Sleep, 100

   Send, {CTRLDOWN}v{CTRLUP}

   Sleep, 100

   Send, {CTRLDOWN}{HOME}{CTRLUP}

   Sleep, 100

   SoundBeep

   Sleep, 100

 

  ;Move back to Excel and copy column 18 cells 501->1000 (or 501->n, where n < 1000) 

 

  ;If you have less than 500 rows, put a semicolon in front of all of the code rows below this line

 

 

      Send, {ALTDOWN}{TAB}{ALTUP}

      Sleep, 2000

      Send, {ESC}

      Sleep,100

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 5}

      Sleep, 100

      Send, {DOWN 500}

      Sleep, 100

      SoundBeep

      Sleep, 100

 

  ;If you have less than 1000 rows, edit the number next to {DOWN 499} to match your total humber of rows +10

 

      Send, {SHIFTDOWN}{DOWN 499}{CTRLUP}

      Sleep, 100

      Send, {CTRLDOWN}c{CTRLUP}

      Sleep, 100

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      SoundBeep

      Sleep, 100

 

  ;Move back to Smartsheet and paste cells 501->1000 (or 501->n, where n < 1000)

 

      Send, {ALTDOWN}{TAB}{ALTUP}

      Sleep, 2000

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 4}

      Sleep, 100

      Send, {Right 5}

      Sleep, 100

      Send, {DOWN 500}

      Sleep, 100

      Send, {CTRLDOWN}v{CTRLUP}

      Sleep, 100

      Send, {CTRLDOWN}{HOME}{CTRLUP}

      Sleep, 100

      SoundBeep

      Sleep, 100

 

   ;Move back to Excel and copy column 18 cells 1001->1500 (or 1001->n, where n < 1500)

 

   ;If you have less than 1000 rows, put a semicolon in front of all of the code rows below this line

 

         Send, {ALTDOWN}{TAB}{ALTUP}

         Sleep, 2000

         Send, {ESC}

         Sleep,100

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         Send, {Right 4}

         Sleep, 100

         Send, {Right 4}

         Sleep, 100

         Send, {Right 4}

         Sleep, 100

         Send, {Right 5}

         Sleep, 100

         Send, {DOWN 1000}

         Sleep, 100

         SoundBeep

         Sleep, 100

 

   ;If you have less than 1500 rows, edit the number next to {DOWN 499} to match your humber of rows -1000 and +10 for a safety margin

 

         Send, {SHIFTDOWN}{DOWN 99}{CTRLUP}

         Sleep, 100

         Send, {CTRLDOWN}c{CTRLUP}

         Sleep, 100

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         SoundBeep

         Sleep, 100

 

 

   ;Move back to Smartsheet and paste cells 1001-1070

 

         Send, {ALTDOWN}{TAB}{ALTUP}

         Sleep, 2000

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         Send, {Right 4}

         Sleep, 100

         Send, {Right 4}

         Sleep, 100

         Send, {Right 4}

        Sleep, 100

         Send, {Right 5}

         Sleep, 100

         Send, {DOWN 1000}

         Sleep, 100

         Send, {CTRLDOWN}v{CTRLUP}

         Sleep, 100

         Send, {CTRLDOWN}{HOME}{CTRLUP}

         Sleep, 100

         Sleep, 100

         SoundBeep

         Sleep, 100

         SoundBeep

         Sleep, 100

         SoundBeep

         Sleep, 100

         SoundBeep, ,1000

         Sleep, 100

 

 

 

Return

 

Hi Adrienn-- Just to confirm, our current sheet size limit is 5,000 rows, so it will not be possible to have this sheet in Smartsheet in any capacity in its current form. If there is a way to break up the sheet into 5,000 row chunks, you might consider this.