Bulk insert using a Flat File with space seperator

[manishsawant]

Hi guys,

I have to use bulk insert to load a flat file seperated by spaces into a table. The flat file is something like this.
000001 000001 LGS TRANSMISSION
000002 000001 LGS TRANSMISSION
000003 000001 LGS TRANSMISSION
000004 000001 LGS TRANSMISSION

Between first 6 charaters and the next character there are 2 spaces. Rest have 1 space. The file is too big which may have more than 2 spaces as well. If someone has done bulk insert like how i want to then please give me some information about it.

Thanks,
Manish

 

[EdwinGene]

I am importing several text files into SQL Server, at the moment. I use the DTS Text File (Source) connection, Transform Data Task, and Execute SQL Task.

In the Text File (Source) I select the text file format as delimited, and then set the column delimiter to tab (which I know the text file does not contain) and the row delimiter to {CR}{LF}. This effectively defines the text file as having single-column, variable-length records.

Then, using the Transform Data Task, I import the text file into a SQL table with a single varchar column wide enough to accomodate the longest line in the text file.

Then, using the Execute SQL Task, I call a stored procedure to parse the imported data from the single-column table into its final, multi-column, destination table. I use a Cursor to be able to access the single-column table records one at a time.

Hope this helps.