Welcome!

Trouble importing large excel file

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

Find more posts tagged with

Importing and Exporting

Comments

Brad Jones

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

#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

SoundBeep

Sleep, 100

SoundBeep

Sleep, 100

SoundBeep

Sleep, 100

SoundBeep, ,1000

Sleep, 100

Return

Kennedy Stomps

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.