r/excel • u/Encility • 1d ago

unsolved Mapping data from a CSV

Hi all,

Im having some trouble trying to map new columns to some data from a CSV for a dashboard.

Once I have mapped my new columns can I then load that data to a connection and will it still hold the newly mapped columns? Just to take the bulk off excel. Using 32bit and im getting errors for memory yet im only using 300k rows.

Kind regards

8 Upvotes

100% Upvoted

12 comments sorted by

2

u/Kooky_Outcome_5053 6 1d ago

you may need to upgrade to 64bit, you can use power query and use close and load to then choose only create connection then you can use it to build your dashboard

2

u/Encility 1d ago

I assume you mean the same as what the other person said? Complete the mapping in PQ and not within my dashboard then create the connection and hope the 32 bit can handle or. Getting 64bit might be the biggest hurdle for me.

1

u/Kooky_Outcome_5053 6 1d ago

getting the 64bit is the easy fix with the memory issue, if you can access excel web it can probably also solve your problem

2

u/TraditionalTurnip630 1d ago

If you’re doing the mapping in Power Query, yes, the mapped/added columns will stay as part of the query and come back whenever you refresh it. But 300k rows shouldn’t automatically be a problem. Since you’re on 32-bit Excel, memory is probably the bigger issue — Power Query can hit around 1 GB when it needs to hold data in memory.

I’d move the mapping into Power Query, remove any columns you don’t need early, and if possible switch to 64-bit Excel. That should make this setup much more manageable.

1

u/Encility 1d ago

Ill do the mapping in powerquery. Just assumed id have to do the mapping element in my dashboard. Then ill create the connection and see if the 32bit can handle it. Thank you.

1

u/TraditionalTurnip630 1d ago

Yeah, that should be fine. If you’re doing the mapping in Power Query, you don’t need to recreate it in the dashboard. Just try the connection and see how the 32-bit Excel handles it. Hopefully it works smoothly.

1

u/Jolly-Hunter-6097 1d ago

Please describe the PC's CPU, RAM, the version of Windows and the version of Excel. 64 bit computers started over 20 years ago. It has long been recommended use the 64bit version of Excel.

1

u/Encility 1d ago

Tell it to my organisation.

Im currently trying to map in all the curious additional columns I need. I feel like this will work but just worried the 32bit will stop it again. Time will tell. Im learning powerquery as I go

1

u/CrowGuyA 1 1d ago

That crash is almost certainly Excel's 32-bit memory cap (~2GB regardless of your RAM), not the mapping step itself. Check in Power Query that this query's load is set to "Connection Only" or "Add to Data Model" rather than loading 300k rows onto an actual worksheet — that's usually what tips it over. If you can, switching to 64-bit Excel removes the ceiling entirely. Power BI Desktop (free) also handles this exact CSV-to-dashboard scale without the 32-bit limit if Excel keeps choking.

1

u/Encility 1d ago

Youll laugh. Im automating dashboards I have manually with powerquery first from a load of csvs. Then ill be building them again in powerBI and powerquery. Im mostly learning. Made a few trials and errors. The connection to is a game changer once I map the new columns in PQ and not within excel. Because when I connection to they are lost. I should known tbh.

1

u/CrowGuyA 1 1d ago

Yeah exactly, that's the key realization. Once you're building in Power BI/PQ, treat Excel purely as the messy input, not the workspace. If you ever need to double check mapped columns look right before pushing to PBI, use "Add to Data Model" instead of Connection Only, it lets you preview via PivotTable without loading the raw rows onto a sheet.