I have two large employee detail text files (300+ data columns, 12,000 + records for each) generated daily from an HRIS program, one for active personnel and one for terminated personnel effective within in a rolling two year period. I want to automatically open/save/import each automatically into .xlsx format, then combine them, whenever I need to, so the result is a single Excel file, that I can open and check/use directly or have dynamically linked in an Access database for querying.
I've been doing this manually (opening/importing each text file, saving as Excel, then combining) but this is tedious and effort intensive. I know it's fairly simple to auto-import text files but the problem is, I want to specifically set the data types in various columns, instead of letting Excel automatically default according to it's own import logic. I've got multiple columns I need to define as text so as to preserve leading zeros (SSN, zip codes, various others), and others I want to specifically define a date format m-d-yyyy. I haven't found a good way to do this other than using the file import wizard and specifically selecting the data types I need in the specific columns, but as noted, this is highly manual (I'm also manually combining them as the last step). So I'm looking for the best solution - I suspect it might possibly need to involve VBA, although I don't know VBA but would be willing to learn and use for this application. In any case, what's the best technical solution? All ideas welcome - thanks.
