Bulk Insert Text files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Insert Text files

Author

Topic

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 07:54:42

Hi Guys and Girls,

Tough one for me this, Im trying to bulk insert quite a big file, iv taken a small snippet of the file so you can slowly understand what im trying to get at! Please see below

24/06/2010 19:18:23 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)
24/06/2010 19:18:23 /IServerManager.Login (timings: authenticate=0 authorise=0 execute=4524029 logError=0 teardown=468003 total=4992032)
24/06/2010 19:20:31 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

I have created a new database within SQL Server with the column names as follows

CREATE TABLE [dbo].[MedWayFiles](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Date and Time] [datetime] NULL,
[Commands] [nvarchar](50) NULL,
[Timings] [nvarchar](50) NULL,
[Authenticate] [nvarchar](50) NULL,
[Authorise] [nvarchar](50) NULL,
[Execute] [nvarchar](50) NULL,
[LogError] [nvarchar](50) NULL,
[TearDown] [nvarchar](50) NULL,
[Total] [nvarchar](50) NULL,
[SLA Breach] [nvarchar](50) NULL,
CONSTRAINT [PK_MedWayFiles] PRIMARY KEY CLUSTERED

As you can see the column names reference words that are within the Text file! Can some one please help me identifying the keywords within the text-file and assigning them to the
Relevant column! i.e bulkinsert! As I have 47 Text-Files and they all hold about 300+ lines of information and doing them one by one, well we all know will take many years!!!

Thanks in Advance!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:07:22

I'd probably BULK INSERT this into a staging table first with only one varchar column that's wide enough to fit the entire row. The I'd write a select from that table in to the MedWayFiles table, should be quite a bit easier.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 08:18:18

Staging table? do you mean a normal table? and then just Select what i need out of it and assign to the relevant column in the medwayfiles?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:25:26

Exactly, sort of like this:

CREATE TABLE tmpMedWayFiles (
ID int IDENTITY(1, 1), --> In case you need to identify each row of data
AllData varchar(2000)
)
...
INSERT INTO MedWayFiles (...)
SELECT [Date and time] = SUBSTRING(AllData, 1, ...),
FROM tmpMedWayFiles

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 08:42:07

CREATE TABLE tmpMedWayFiles (
ID int IDENTITY(1, 1), --> In case you need to identify each row of data
AllData varchar(2000)
)

So when the above is done i then Bulkinsert the text file to that,

Then Select bits i need and assign to the relevant table?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:47:42

Yes

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 09:05:06

Im just trying to insert the Medwayfile.txt and its gives me this error??

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).

the code im using is off this forum
BULK INSERT TMPMEDWAYFILES FROM 'C:\MEDWAYFILE.TXT' WITH (FIELDTERMINATOR =',')

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-10 : 09:19:27

You need to omit identity column
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 09:30:41

Hi, iv looked at your link, but im curious how can i reference bits within the text file for example as you can see this is a bit of information from 1 of 47 iv got

24/06/2010 19:18:23 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)
24/06/2010 19:18:23 /IServerManager.Login (timings: authenticate=0 authorise=0 execute=4524029 logError=0 teardown=468003 total=4992032)
24/06/2010 19:20:31 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

so i cant seem to get my head around it, the reponse before yours i understood create a new table which has now been done, and then select bits of information from it that i require and insert it in to the correct Table! which would work out alot easier....but the only problem im having is the bulk insert situation, i just want to be able to insert all of the information from the text file and go from there.......

and even if i did create a new table from the link you have provided above how can i pick individual bits of information from the textfile to make sure SQL picks it up?

Thanks in advance

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-10 : 11:24:40

madhivanan i have just gone through the link you had shown me, but same again i have the above error message popping up.....

Would you be able to help me please?

thanks in advance.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-11 : 02:41:40

I just copied the rows of data you posted in to a file on my computer and ran the following script:

CREATE TABLE tmpMedWayFiles (
  AllData varchar(2000)
)
BULK INSERT tmpMedWayFiles 
FROM 'c:\temp\file.txt'
WITH (
  DATAFILETYPE = 'char', 
  ROWTERMINATOR = '\n'
)

ALTER TABLE tmpMedWayFiles ADD ID int IDENTITY(1, 1)

SELECT * FROM tmpMedWayFiles

My apologies for misguiding you about the identity-column being present before the bulk insert...it's been a while since I've done this and my memory was a bit rusty.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Scott_Atkins
Starting Member

9 Posts

Posted - 2010-08-11 : 10:27:18

not a problem at all, thanks for the example its appreciated :) i managed to do that now :) another question if you wouldnt mind helping me on, within the txt.file there is a word called "SLA Breach" which currently doesnt have any value but that will change in the next few weeks, i have written an application within VB.Net that allows me to open txt.files and then upload them, but what im looking to do now, is within SQL write a procedure and has a Flag? if thats correct, in it so when a txt file gets uploaded it will then flag that column can this be done? iv been told about the guid? would you be able to provide another small example?

Thanks in Advance!!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-12 : 03:03:52

I'm sorry but to be honest I have no clue what you're talking about, you'll have to explain a little better. Is this problem in relation to the bulk insert?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources