How do you deal with a limit of only 1 million rows in a worksheet or workbook? I have 5-7 million rows which I need to import from excel to a database

like
Posting as :
works at
You are currently posting as works at
Post Photo
like

Alteryx

like

Huh, but why would you use excel as the original file? Change it to .csv; excel doesn't support* more than 2^20 rows, if you save your file as one, you will lose the data.

I'm not sure if the file itself already exclude the data, or if it's done after - you may want to test it, but I think the file doesn't save the data itself, so your "excel file" would only have the first 2^20 rows

*It's not that excel doesn't support it, but the UI doesn't. You can still operate with more than +1MM rows if you use a virtual pivot table, although I don't recommend - above ~400k rows, you'd be better using an alternative; and I'm against the use of pivot tables, even the virtual one

like

Why do you need all your rows in one workbook? Why not loop through an array and do an incremental load

In one workbook so I can export all the rows to a sql table or import it into a sql table from one workbook instead of 7-8 of them

Read the Excel file with Python (pandas has performant support for xlsx) and ship the rows off to your SQL DB in chunks. Then ditch the Excel file in an archive somewhere and use your database moving forward

if your goal put data to SQL table, you can do without excel. you dont need it. Just use python or C# get from your source Sound like API put directly in SQL. If you really wanna use excel then use VBA script (sql connection) and get from source until some limit(ex 500k row) then insert into table.

Related Posts

“You have a good looking face. But your body is absolute junk."

funnylike

Have you ever been or currently in a relationship with someone of who’s political beliefs conflict with your own?

Not dealing with this currently have dealt with it in the past. My parents started out opposites but now believe similarly. I’m wondering if it is possible in this day and age.

like

Daily Dordle 0143 5&7/7
⬜⬜⬜⬜🟨 🟨⬜⬜⬜🟨
⬜⬜⬜🟨🟨 ⬜⬜⬜🟨⬜
🟨🟩⬜⬜🟨 ⬜🟨🟨⬜⬜
🟩🟩🟨⬜⬜ ⬜🟨⬜⬜⬜
🟩🟩🟩🟩🟩 ⬜🟨⬜⬜⬜
⬛⬛⬛⬛⬛ 🟨🟩🟩⬜🟩
⬛⬛⬛⬛⬛ 🟩🟩🟩🟩🟩

like

Thoughts on Chicago athletic club? Would likely be going to lincoln park / lakeview locations

like

Hello Verizon fellows, In Deloitte, there's a facility to claim a certain reimbursement for purchasing mobile devices through their portal or something. Similarly, do VDSI employees have any discounts or reimbursement for purchasing devices through Verizon? If so, kindly let me know how to check it out. Thanks in advance.

like

Have any of your managers ask you to track every single hour you spent? It’s been pain of me for a long time. 1) sometimes I forget what exactly have I done for the day to reach 8. 2) some tasks take more time than managers’ expectation due to follow up, investigating, etc. and if you put more hours than then expected, they will then ask why.
:/ (this is not a request for me only, it’s required for the whole team)

like
likeuplifting

What agencies are good for/ open to non-German speakers? Really interested in moving to Berlin but don't speak German :/

Any one like. to wear nosepin?

like

Question for senior tax managers, tax directors, and higher in public accounting. Have you seen managers or senior managers in PA hired in tax who you feel know very little for the position, or does this happen only very rarely or never in your opinion?

like

How’s everyone holding up?

like

Will join USI Anyone from this group on 27th June??

like

Looking for a job in GSK , as a technical Argus safety pharmacovigilance consultant with 9 years of experience. Thanks in advance .

like

So true 😅

Post Photo
funnylike

Hello guys is anyone living in chennai nearest to tril infopark tharamani? I am looking for a pg please do let me know if anyone vacancy is there

like

Hi Fishies, I was hired for wealth and asset management project EY . It is capital market domain. Any body have any idea about this project going on in EY and the tools used for it.

Please advise if anyone have idea about it.

Just want to know any global tool they use for wealth and asset management, so it's a plus for me to join.

Thanks in advance

like

What is the salary range for Technical Lead role in Infosys ? I have a HR round coming up and don't want to get lowballed into that one.
Exp: 5+ yrs

like
like

Thoughts on recruiting firms in LA? Looking to freelance beginning in Oct / Nov!

like

Hi everyone. I know you are the WLB in consulting in the world is bad but in general in Europe it is better than other parts of the world. So does the WLB in consulting in Italy slightly better than US or UK?

Additional Posts in Excel Genius

Hello. Quick question about formatting. I have an excel sheet with bunch of negative numbers, but some are percentage values and some are dollar values. Is there a way to make all of them have a parenthesis for being negative while keeping them still percentage and dollar value separately or do I have to target them separately?

like

Hey fishies, looking for some help with the below

I have 4 lots of data categories which are assigned a code in the adjacent cell
Eg:
Cell A2= 'xyz' Cell B2 = 'w1'
Cell C2= 'ghi' Cell D2 = 'x1'
Cell E2= 'plm' Cell F2 = 'y1'
Cell G2= 'mhn' Cell H2 = 'z1'

The combination of the 'W','X','Y' and 'Z' codes identity a specific trend
I am extracting data which includes multiple combinations of these codes. What is the best way to show frequency of the codes against the category it is assigned to

like

Anyone else have a formatting pet peeve and get annoyed when people don’t have a blank first column and row in tabs? I.e headers/data in cell A1

My first manager told me this, and I’ve followed it to a T since.

likesmart

How do I look up values from a column in a row? E.g. Values in A1:A6 in B2:F2?

like

Would anyone be willing to teach me vlookup?

like

This is dumb, but. How can I pull and average the numbers in column b associated with the 4 values in column a? So, (sum of all As/total number of As) without filtering?

Post Photo
like

Anybody know if there is a way to automate a Gantt chart in excel when building out a roadmap? Essentially want to recreate the functionality of ms project because my client does not have project and wants a nice visual for the roadmap

like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

Does 2-finger touchpad scroll not work on Excel sheets anymore? I used to be able to swipe quickly on my laptop's touchpad to navigate sheets but now it doesn't work. Based on the handful of posts I've read online about it, the problem seems unresolved. Has anyone dealt with the same issue and fixed it?

I created a macro to help me save time on this onetime task and now whenever I open my excel, a blank excel opens with the name of the macro. How do I get rid of it?

like

Does anyone know what purple tables mean in Excel?

I know about green and orange, but never saw purple before and trying to figure it out.

likefunny

Anyone have an app or Excel spreadsheet they use to keep track of utilization? We just switched systems and our new system doesn’t give us a real time balance of our utilization rate (cont)

What’s the symbol that you can type in a cell so you can input a text, but that text would not appear in the spreadsheet? I want to describe the formula without using a text box, which uses more space.

The equivalent of this is in SQL, you can use the apostrophe in the beginning of and end of a text. That text will not affect the function of the query, but that text can be seen if you proofread the query.

like

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

I’m calculating a bunch of totals and averages by category on one worksheet by filtering data in different categories from another worksheet. However, when I change the filters, the previously..(cont)

I am trying to recreate this without the numbers at the end. Any advice?

Post Photo
likehelpful

Hi All,
Looking to automate a manual process for the client and thinking of creating macros for with VBA. Has anyone done anything similar?

In the process of learning but wanted to reach out for any tips, tricks and advice anyone may have.

Thanks I’m advance.

like

Any book recommendations on excel modeling for real world business applications?

like

I’m verrrrrry new to excel and am going crazy because is there not a way to “clip” text in cells like you can in google sheets? I read about a “fill” option, but then noticed that cells that aren’t filled just repeat text. 😂🤯

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal