I driving myself crazy and have not found a solution. I have tried SSIS but its very picky since my source data changes a lot. I am looking for a way using a .txt file as the source for my data to import all its data into separate columns in SQL.
For example, my source .txt file has random data separated mostly by spaces, so I can use this information to break each data into separate columns. What SQL query can I use to pull the data from the .txt file on the local machine's hard drive and randomly grab all the data and insert them into a table but in separate columns?
I've used a Row delimiter of Comma and a Column delimiter of Tab while attempting this on SSIS and it formatted the data really well, but have issues with truncation and doesn't work well. This is why I would like to use a query instead.
Thank you and I would really appreciate the help!