Import flat file data

Hi,

I wanted to know if there was a way to import data from a flat file without specifiying the delimiters. I want to import each line in one row so that i can use the substring function to break of the data as an when i want and not as per the delimited format file or the wizard.

i.e if row one had "abc"|"1453"|"Jack"|"Smith"| etc.... rather than importing these as different columns and rows. I want this all in one row, one column.

Is it Possible?

Hi,

You should use the bcp utility with the default -t switch. The default field terminator is the tab. I did not try this, but it should work.

Regards,

Janos

|||

You want to concatenate the data then - just use a "fixed width" as long as the string you want to make. You'll get the quotes as well.

To remove the quotes and add a new delimter at the same time, you could use the "sed" program or something like it to form the text file the way you want it.

|||

what's the "sed" program? and also if i use the wizard and it didn't have a CR or LF then it would throw an error which why I wanted to know if i could import the data via a script? if so how?

|||

"what's the "sed" program? and also if i use the wizard and it didn't have a CR or LF then it would throw an error which why I wanted to know if i could import the data via a script? if so how?"

The sed program is an open-source string processor. There's stuff about it all over the web. I didn't realize that you were using a wizard. There are other methods for import, and you might start here for more ways to do that:

http://search.live.com/results.aspx?q=import+data+%22SQL+Server%22&src=IE-SearchBox

|||

Use BULK INSERT statement to insert each line as one column for one row on temp table.

And then split each row data by the pipe delimiter for separate column.

Doing that you can verify the column data before insert into data table