Hello dear T-SQL community,
I am working on some exciting project but my problem is not exciting and I have not been able to find a valid solution... (all related fix are arround BCP or BULK INSERT).
The server where the DB is located is not allowing BCP / Bulk insert command ... this is why I need to use the OPENDATASOURCE command. Also, the PIPE symbole is something that is forced from our IT dep and I can't influence this...
My Problem:
How can I load a set of data (here in 5 collumns) when the each data is separated by a pipe symbole?
DETAILS
I am loading the data in the table variable @tablename that is created before the command and has bellow format:
Col1 nvarChar(50),
Col2 nvarChar(10),
Col3 nvarChar(10),
Col4 nvarChar(10),
Col5 nvarChar(10)
The text file I need to import look like this:
START|RF|
Z2R|255|001|004|AB7
where:
START|RF| is the header (discarted)
Z2R|255|001|004|AB7 is the data I want to load...
Here is the code :
SET @ls_SQL = 'INSERT INTO ' + '[dbo].[' + @tablename + ']' + 'SELECT * FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',''Data Source=D:\star\DropBox\;Extended Properties=''''Text;HDR=No;Format=^|;'''''')...[MyFileName#txt]'
SELECT @ls_SQL
EXECUTE sp_ExecuteSQL @ls_SQL
SQL is not able to load the data with the pipe and interprete that the whole line is basically for 1 collumn only...
If you have any idea how I can work with this...it will be appreciated :).
Kind regards,
Nicolas