Extracting Text Data to Excel File for Import into ASC

[Christopher Wilson]

Hello everyone. I'm looking for an automated method to extract financial account activity data from a *.txt file and import it into an Excel file so we can import it into ASC. I'm sure it can be done via AI. I've tried to use Copilot with limited success. Python seems to be a possible solution. Thank you.

[C. B. Zeller]

ASC can natively import txt files. Is the file structured in any reasonable way? Have you tried using one of ASC's import wizards or their predefined importer specs, if one exists for the data source you're using?

If you want to take the approach of using code to extract/transform data before loading it into ASC, then a chatbot could probably help you write the code. I'd recommend that you know enough Python (or whatever language) to be able to read and understand the bot's output before relying on it in production. People on the internet seem to like Claude for that kind of task these days, but I can't offer any personal experience.

[justanotheradmin]

I agree with C.B. Zeller. 

Is the issue the file type? Because ASC takes .txt files for import just fine. 

Is the issue how the data is in the file? 

What import function in ASC are you trying to use? there are a variety of ways to import things into ASC. For example, investment imports where ASC has pre-programmed to work with certain major recordkeeper files. And then there are ASCRIPT custom imports which are super easy to use as well once you know what you are doing. Plus wizards and such. 

If the issue is how the data is in the file, and you don't want to manually edit the file then a macro or code to do that as CB suggests. 
You can just open the txt file in Excel, and then save it as an excel file type if you really just want to change the file type. 

[mbvs]

Ascript is ASC's import utility that can import just about any data from txt, csv, xlxs.  If you are working with a recordkeeper data file - it may be you can import using one of their pre-programmed routines.   You can submit a sample file to their support team as well and get some guidance so you aren't spinning wheels. 

[Christopher Wilson]

Thank you everyone for your input. I understand and use ASC's predefined asset import routines. What I'm specifically referring to is the *.txt account activity files provided to us by ADP and Paychex when we takeover one of their plans. The information cannot be imported into ASC in its current format. I don't have Python or Claude programming skills, nor the time to learn, so I'm probably going to have to outsource this project to someone who can do it for me. I would appreciate any recommendation you have. Thank you.

[Bri]

I'd be using Excel's "text to columns" feature to be able to generate a CSV file from the original TXT, but that's because I prefer delimiters to fixed-width.