Rick on 13 Feb 2016 02:47:37
How is it in 2016 we still struggle in Excel with flat txt files, delimited, etc?
At a MINIMUM - have a way to "guess" and generate a schema.ini file as a DRAFT.
provide a way you can input things like
mm/YYYY as text into a date field using Schema.ini
Provide ON LINE help for constructing/editing a Schema.ini that contains FULL DOCUMENTATION. Here, alas is just a start:
https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx
READ THIS GREAT ARTICLE!!! YES, not all flat files are "ready to go". Most times on large files we just want a slice. We need SQL between those files and the Excel Data Model. The LESS we load in the DM the faster it runs.
https://brentpearce.wordpress.com/2013/12/04/powerpivot-csvs-the-sql-way/
DON'T GET ME WRONG I LOVE Schema.ini!!! Its great that is a separate file, easy to read, easy to edit, easy to understand, does what it supposed to. I like the fact its NOT embedded in .xls or .xlsx, its not hidden. KISS (keep it simple)
BTW, regular Excel should USE Schema.ini for imports too. The wizard is HORRIBLE for tables with scores of columns.
ULTIMATELY it might be best to use the XML import facility for this. Let Excel XML facility think a .txt, .csv flat file is XML and "guess at" the .XSD. Then use the Import maps to clean all that up. Again, leave the .xsd thats "generated" outside, in the clear, where we can "have at it", not embedded in .xlsx