Forum Discussion

Mark Habert's avatar
Mark Habert
Copper Contributor
Jul 23, 2018

Need best way to automatically transform text file to Excel with specific data types

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.

  • Hi Mark,

     

    If your text files structure is always the same it'll be one time job to adjust/transform your columns and generate the resulting file with Power Query, after that only refresh the result with each new pair of text files.

    • Mark Habert's avatar
      Mark Habert
      Copper Contributor

      Will definitely explore that option - thanks Sergei!

Resources