Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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
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
#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.
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
