SOLVED

Notepad to Excel - Data Transfer

%3CLINGO-SUB%20id%3D%22lingo-sub-2395090%22%20slang%3D%22en-US%22%3ENotepad%20to%20Excel%20-%20Data%20Transfer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395090%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20import%20a%20very%20large%20amount%20of%20data%20from%20an%20Microsoft%20Notepad%20into%20an%20Excel%20file%20but%20the%20data%20set%20is%20too%20large%20for%20one%20Excel%20file%20(stops%20as%20about%201.4m%20rows).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20doesn't%20specify%20how%20much%20of%20the%20Notepad%20data%20did%20not%20import%20to%20the%20Excel%20file%20and%20I%20was%20wondering%20if%20you%20knew%20how%20to%20move%20the%20rest%20into%20a%20second%20(or%20third)%20Excel%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20done%20this%20under%20the%20data%20tab%20and%20'import%20from%20text'%20but%20as%20I%20mentioned%20I%20am%20unable%20to%20import%20the%20remainder%20after%20the%20initial%20data%20transfer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20suggestions%20welcome!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2395090%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395143%22%20slang%3D%22en-US%22%3ERe%3A%20Notepad%20to%20Excel%20-%20Data%20Transfer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065658%22%20target%3D%22_blank%22%3E%40alexdeeney%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20Power%20Query%20to%20import%20the%20data%20as%20it%20seems%20you%20did%2C%20but%20at%20the%20end%2C%20instead%20of%20loading%20to%20a%20table%2C%20select%20%22create%20connection%20only%22%20and%20%22Add%20to%20the%20data%20model%22.%20This%20way%20the%20data%20will%20not%20be%20visible%20in%20the%20worksheet%20but%20is%20available%20in%20the%20memory%20for%20data%20analysis%20using%20pivot%20tables.%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20tutorial%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D5u7bpysO3FQ%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D5u7bpysO3FQ%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395162%22%20slang%3D%22en-US%22%3ERe%3A%20Notepad%20to%20Excel%20-%20Data%20Transfer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395162%22%20slang%3D%22en-US%22%3EThanks%20a%20mill%20-%20I%20really%20appreciate%20it!%3C%2FLINGO-BODY%3E
New Contributor

Hi guys,

 

 

I need to import a very large amount of data from an Microsoft Notepad into an Excel file but the data set is too large for one Excel file (stops at about 1.4m rows).

 

It doesn't specify how much of the Notepad data did not import to the Excel file and I was wondering if you knew how to move the rest into a second (or third) Excel file?

 

I have done this under the data tab and 'import from text' but as I mentioned I am unable to import the remainder after the initial data transfer.

 

All suggestions welcome!

 

 

Thanks,

Alex

6 Replies
best response confirmed by alexdeeney (New Contributor)
Solution

@alexdeeney

You can use Power Query to import the data as it seems you did, but at the end, instead of loading to a table, select "create connection only" and "Add to the data model". This way the data will not be visible in the worksheet but is available in the memory for data analysis using pivot tables.

Here is a tutorial: https://www.youtube.com/watch?v=5u7bpysO3FQ

MoreThanMillionRows #ExcelInterviewQuestions #DataModelExcelCan you handle more than a million rows in Excel? If so, how?In this episode of Excel Interview ...
Thanks a mill - I really appreciate it!

@alexdeeney 

You are very welcome!

Please give it a try and if it solves your problem, please remember to mark my answer as a solution to your question. Thank you.

@Celia_Alves

Yes, that worked when importing the data to Excel using a 'Data Model' - thank you!

However, I am having difficulties manipulating the data in a Pivot Table to get the info I need - I am looking for sum of values however I can only get the count as "it is not a supported calculation for Text Data types"

Do you have any thought on how to overcome this issue?

Many Thanks,

Alex

@alexdeeney 

If any one cell within the column loaded to data model is not number, data type for this column will be Text and you can't change it on Decimal Number. If that's the case clean your data in Power Query before loading to data model. And on final step change in Power Query data type on Decimal number if that wasn't done.

@alexdeeney 

As @Sergei Baklan explained, you need to make sure that you define each column Data Type correctly on Power Query before importing your data into Excel, so that Excel knows what is a number and what is text.

For a thorough lesson about this matter, feel free to check this video: https://youtu.be/xbyN9iJhlso

How annoying is it when our VLOOKUP formulas do not work as we expect it to, or a Pivot table does not calculate the sum of the numbers that we can see in ou...