How do I save data I'm collecting in Python for Excel.

[tebbens]

Hi and Help!

I'm VERY new to Excel, but recently have a need to save data I'm collecting from a Python script to Excel so I can easily review, search and display information.

I'm collecting data on 100,000+ 'systems' where each 'system' has a serial number, model, date and country, also each system has a number of components (component number and related description)

Would it be best to save each 'system' in a row?
example:
[serial1] [model] [date] [country] [com1 num] [com1 desc] [com2 num] [com2 desc] ....
[serial2] [model] [date] [country] [com1 num] [com1 desc] [com2 num] [com2 desc] ....

Would I be able to search ANY cell and display all systems that match, 1 system per row, or in sheets?

Can I 'nest' or 'hide' system information OR display system information in a more readable format after a search?
example:
[serial] [model] [date] [country]
[com1 num] [com1 desc]
[com2 num] [com2 desc]


Appreciate any help, thanks !

[rkraynak]

Would it be best to save each 'system' in a row?

This should work, along with adding column names.

Would I be able to search ANY cell and display all systems that match, 1 system per row, or in sheets?

The basic control-F would allow you to search across all cells, but it might be cumbersome for such a large data set.

If you're familiar with SQL, OpsLab let's you import CSVs into a database - then you can search using SQL. From there, you can export the search results into a CSV file.

Can I 'nest' or 'hide' system information OR display system information in a more readable format after a search?

It sounds like this would require a macro for custom search that produces a new sheet with the results formatted that way. In OpsLab, you'd be able to query for an individual system.

[Norie]

Where are you currently storing the data generated by the Python script?

[dflak]

You would need a macro to transform your existing data into a normalized format (I am assuming that each system has a varying number of com numbers and com descriptions). Then you can use a pivot table to order up the data as you described.

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[buran]

I'm collecting data on 100,000+ 'systems' where each 'system' has a serial number, model, date and country, also each system has a number of components (component number and related description)

This simply screams for proper DBMS. That number of records for systems + records for components - it will turn into nightmare very soon if dumped in an workbook.
You can choose from number of database systems for back-end and [eventually] use the Excel with ADO for queries, reports, etc.
The choice of a DBMS will depend also on your Python script and your ability/desire to develop it, rather than introduce excel too early if at all in the work-flow.

[dflak]

@buran - you are probably right. What tebbens is probably looking at is a cross-query output. The data does exist in the correct format in the database and is "stretched out" for displays to humans. A straight query to get the data in normalized format is probably what is really needed here.

I pull data from Oracle all the time in a normalized format and use the Excel table it comes into to do some computations and to play with pivot tables and charts. Database + Excel is a winning combination if done right.

[buran]

I also have projects - both for my personal needs and for clients that use MySQL and MSSQL as backend and use Excel for front-end. In other cases I use python scripts that work with DB and produce reports in Excel. Python also has nice tools - Pandas, Numpy, number of packages to work with Excel, etc. and depending on data/transformations/calculations it could be easier to do the data heavy-lifting in Python