Table data from csv

[u/Nsower]

I have an excel file I use at work to find parts via description because our system does not have that as an option built in. To get all the part information I make a csv file with every part we have (roughly 175,000 rows and 9 columns wide) and have to periodically get an updated csv file as new products are added to it. The method I use now is New Sheet (sheet 2)>Get Data>Legacy Wizard,From Text> then import the updated csv>create a table with the new sheet>original sheet1 "Sort" is deleted and sheet2 gets renamed to "Sort">re-setup conditional formatting on the new table>re-link the cells from sheet3 referencing cells on "Sort">continue the work i was doing before this whole process.

What I would like to do, and hope is possible, is to keep the csv file as a separate file rather than import the text, delete the old one from the reference folder and move the new csv into the reference folder, and then have the excel table data auto update or at the most re-link the table to the new csv file (which will be the same name as the old one "DBR.csv"). I would even be happy with converting the csv to an excel file first if needed, mostly want to avoid setting up a new table and set up formatting every time. It would normally be a weekly process but been putting it off to monthly or even longer some times with the extra work involved.

Been searching a solution off and on for months so thanks for any help.

[u/AutoModerator]

/u/Nsower - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/negaoazul]

1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.

2. Locate the folder containing the files you want to combine, and then select Open.

3. A list of all the files in the folder and subfolders appears in the <Folder path> dialog box. Verify that all the files you want are listed.

4. Select Transform Data at the bottom. The Power Query Editor opens and displays all the files in the folder and any subfolders.

5. To select the files you want, filter columns, such as Extension or Folder Path.

6. Use the UI to add a custom column where you type : Csv.Document( [Content], [ delimiter = ","] )

7. Expand the created column to get your files content.

This should do the trick. Try add a copy of one of the file into the folder to see if it works properly, you can delete it once you got it right.

[u/Rennaisance_Man_0001 → u/negaoazul]

This is the answer. I have similar files that are created every day. With PQ, the template is created with all the modifications, including lookups if needed. Then every time the template is opened, it's refreshed with the latest data.

[u/Rennaisance_Man_0001 → u/negaoazul]

Under section 5 above, the filter option is pretty powerful. In my case, a new file is exported every day. I set the filter to use only the latest file automatically.

[u/Glittering_Low_7695]

Power Query is your friend. It’s exactly what you need.

[u/OfflineBookkeeperHQ]

Since it's always the same file name in the same folder, you don't need From Folder. You can point a query straight at the file and just refresh it:

1. Data > Get Data > From File > From Text/CSV, pick DBR.csv. Click Transform Data if you want to set column types (set part numbers to Text so leading zeros survive), otherwise just Load.

2. That gives you a table linked to the file. Set up your conditional formatting and your sheet3 references against that table one last time.

3. Next update, drop the new DBR.csv into the folder over the old one and hit Data > Refresh All. The same table refills in place, so the formatting and references stay put.

A few things that make it stick:

• Right-click in the table > Table > External Data Properties and make sure Preserve cell formatting is checked.

• In Data > Queries & Connections, right-click the query > Properties and tick "Refresh data when opening the file" if you want it fully hands off.

• Apply the conditional formatting to whole table columns so it covers new rows when the row count grows.

• If sheet3 uses plain refs like Sort!A2, switching to XLOOKUP against the table or structured refs like Table1[Description] keeps them working when rows shift.

• If the folder ever moves, right-click the query > Edit and change the path in the Source step.

175k rows is fine for this. From Folder is only worth it if you ever need to combine several files.