Insert .txt file Text to Columns in SQL

Other SQL Server Topics
1

Hello,
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!

2

Bulk load the entire file into a single column, then process that column into lines yourself. For that to work, the original file would have to be 2GB or less.

3

Once I insert it into a single column, what sql query can I use to split everything into separate columns?

I appreciate the help.

4

why is this file not delimited and do you have control over this file?

5

The data is coming from a syslog forward and is not properly formatted.

6

can you change the syslog forward to make the output comma delimited?

7

Just use looping code to:

  1. find the next row break; if string is now empty, exit code.
  2. put the row into a long string;
  3. use t-sql code to separate the string into columns;
  4. insert the columns into a temp/staging table.
  5. after however many rows you want to use as a batch size, copy the temp/staging table rows to the main table, then clear the temp table.
  6. back to step 1
8

if you just want to split on SPACE (or some other delimiter character/string) when you could use a splitter function

If it is something more complicated like "First space, then the 3rd space, then a '' ..." then you'll need to use Scott's method