Forum Discussion
Need to convert a text file into a CSV
I have a text file from payroll that includes all active employees and their relevant information such as Phone Number, Office Location etc. I need to convert this file into a CSV so I can more easil...
You then would have to create some logic that knows that if there are more than two spaces, for example, that that would be the next value. But some columns are nog that big... It's complex doing it like this, other option is to save to file to csv from Excel after you import it with text to columns.. A manual thing for something that you want to do automatically...
Converting the file manually just isn't an option. It gets updated daily so I need to be able to automate the conversion.
Fixing the data at source sounds like the best way forward, but this usually takes someone with a big enough stick to have the conversation with the business\system owner if the working relationship is not there.
We have ETL (extract\transform\load) software to do this type of thing, because although you can use PowerShell, it usually involves a lot of code (instead of point and click with the ETL software).
In this case, it sounds like a function is needed to count the hyphens to each space, to then use in the substring commands. This should in theory make it more robust if you can't guarantee the hashes are always going to be the same. But I'd still be fairly concerned about exports that have changing formats in general terms.- I understand that, the way of getting data out of the export is not great. Is there an option, and I guess not because it even lacks the possibility of exporting to a csv, to read from the payroll data using API requests?
