November 15, 2012 at 7:41 am
Hello there,
I want to create a script, which loads a flat file in a table.
The flat file look like this:
65262891001000100000
571719690010001000000
538999100010082201673
with \CR\LF at the end.
Here is my table:
CREATE TABLE [dbo].[CTITEL](
[Col001] [char](8) NULL,
[Col002] [char](21) NULL)
So I would like to import from 0-7 (Col001) and 8-29 (Col002).
Could you tell me, how this work? I have no practices on SQL with Microsoft. At the moment I think, that "Bulk insert" should be the way it works.
Perhaps someone of you could help me.
Thank you.
Kind Regards,
RolfW
P.S: I hope thi sis the right forum for this.
November 15, 2012 at 1:49 pm
Hi there,
BULK INSERT works fine, see the details here: http://msdn.microsoft.com/en-us/library/ms188365.aspx
To split the strings, you can help yourself with a temporary table.
What confused me as a newbie was the file path:
"
data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt.
"
Here is a basic example:
CREATE TABLE #tmp (Col NVARCHAR(32))
BULK INSERT #tmp FROM 'C:\PathTo\flatfile.txt'
INSERT [dbo].[CTITEL]
SELECT
SUBSTRING(Col, 1, 8),
SUBSTRING(Col, 9, 21)
FROM #tmp
DROP TABLE #tmp
November 16, 2012 at 2:22 am
Hello Arthur,
thanks a lot. Worked fine.
Kind regards,
RolfW
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply