Link to home
Start Free TrialLog in
Avatar of kpi_it
kpi_it

asked on

Bulk Insert a text file into Sql Table

I need an example of how to Bulk Insert from a text file into a temp table.
I have it working now. But I get 30 something rows in the sql table. As there are 30 lines in the text file.
I need to treat the entire text file as a whole. The entire text file ends up in a sql table with a field data type of text.
Avatar of RDWaibel
RDWaibel
Flag of United States of America image

How are you attempting to import it now?  With the Import Wizard?
Avatar of kpi_it
kpi_it

ASKER

Trying to do this without DTS.
I get a list of *.txt files from a known directory path.
Then Bulk Insert into a temp table.
Need to go from the mutiple records in the temp table, where each record is one line from the text file.
Massage it somehow so I end up with a single variable / record, so I can import it into a notes table in Sql.
The notes record records the datetime and the entire note ends up in a field of text data type.
Are you intending to make this an automated process?
Avatar of kpi_it

ASKER

Yes
The following statement will import the whole contents of a file into a single record

--create table tmptest (test varchar(8000) null)

BULK INSERT dbo.tmptest
   FROM '<input file>'
   WITH
     (
        ROWTERMINATOR = '-1'
      )

If you are on SQL Server 2005 or above, define the field as varchar(MAX) to accomodate bigger sizes..
Avatar of kpi_it

ASKER

Better but with rowterminator set to -1 I still end up with 5 records.
What does the -1 signify?
The -1 is the notional end-of-file and is used to rule out any valid ASCII characters being detected as a rowterminator, thereby lumping the whole contents of a file in a single field/record.

Try this instead to see if it works -

(I'm using the text datatype in table tmptest for SQL 2000, but you can replace with varchar(MAX) if on SQL 2005 or above)
--create table tmptest (test text null)
 
BULK INSERT dbo.tmptest
   FROM '<input file>'
   WITH
     (
        ROWTERMINATOR = '\0'
      )

Open in new window

Avatar of kpi_it

ASKER

Ok here goes,
Here is the original text file:

Adams Bandwidth Test
Ticket: 29
Name: Todd Friye
Address: 1301 Kochs LN
City: Quincy
State: IL
Zip:

Kbps: 395.2
Mbps: 0.39
Upkbps:
Upmbps:
IP Address: 66.226.110.113
FQ Name: qncy-900qu-wrls-110x113.adams.net
Tech: Greg Boll
Tested From: Tech Laptop
Type: nlos
Operation: Trouble

900MHz NLOS Wireless Trouble

Equipment Removed
Radio Model:
Radio SN:
Antenna Type:
Router Model:
Router SN:

Equipment Installed
Radio Model: EUM3005
Radio SN: 61301a
Antenna Type: Indoor
Router Model: custsupp
Router SN:

NLOS Signal Levels
RSSI: -67
SQ: 5
RNA/RNB: 32/32
1Ok: 98.3
HCRC: 0.0
FCS: 0.0

Optional Equipment Installed
Additional PC Configured: No
Wired Network Card:
USB Wireless Network Adapter:
PCMCIA Wireless Network Card:
USB Wired Network Adapter:
3-Foot Tripod: No
5-Foot Tripod: No
Mast: No
4-inch Standoff: No
Powerstrip: No

Comments:
QU North sector 1 - 9184. Upon arrival customers signal was at a -80. Readjusted indoor antenna and moved it to the west a little and got customers signal to a -67. IP address in customers radio was wrong upon arrival. Switched it to right ip''s and now customer is back up and surfing.

If I set the bulk insert to: Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email1.txt" With (Firstrow=1, ROWTERMINATOR = "-1")

The following this is what I get:

Adams Bandwidth Test
Ticket: 29
Name: Todd Friye
Address: 1301 Kochs LN
City: Quincy
State: IL
Zip:

Kbps: 395.2
Mbps: 0.39
Upkbps:
Upmbps:
IP Address: 66.226.110.113
FQ Name: qncy-900qu-wrls
10x113.adams.net
Tech: Greg Boll
Tested From: Tech Laptop
Type: nlos
Operation: Trouble

900MHz NLOS Wireless Trouble

Equipment Removed
Radio Model:
Radio SN:
Antenna Type:
Router Model:
Router SN:

Equipment Installed
Radio Model: EU

(2 row(s) affected)

If I set Bulk Insert to: Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email1.txt" With (Firstrow=1, ROWTERMINATOR = "\0")

This is what I get:

Adams Bandwidth Test
Ticket: 29
Name: Todd Friye
Address: 1301 Kochs LN
City: Quincy
State: IL
Zip:

Kbps: 395.2
Mbps: 0.39
Upkbps:
Upmbps:
IP Address: 66.226.110.113
FQ Name: qncy-900qu-wrls-110x113.adams.net
Tech: Greg Boll
Tested From:

(1 row(s) affected)

Neither one get me the entire file.
What is the structure of ##MyTable?
Avatar of kpi_it

ASKER

create table ##MyTable (cBody Text)
I also tried varchar(8000) with the same results
Can you attach your 'Email1.txt' here? I copied the text from your previous post and the BULK INSERT with \0 returns the complete text for me..

It looks like the 'Maximum characters per column' setting (Menu -> Tools -> Options -> Results tab)  in your SQL Query Analyzer is set to 254. Change it to 8000 and you should see the whole file being imported..
Hi can we simply try :

create table #t (col1 varchar(max))
bulk insert #t from 'J:\Mitg\Email_Attachments\Email1.txt' WITH (ROWTERMINATOR ='\n')
select * from #t
or varchar(8000) if you like...
Avatar of kpi_it

ASKER

I am force into Sql7. So I cannot use the Max function.
Also I wasn't aware of a setting in the analyser to set the results values.
But in looking I don't find where your pointing at to change it.
Maybe it isn't an option in Sql7?
I attached the original text file.
Avatar of kpi_it

ASKER

I am force into Sql7. So I cannot use the Max function.
Also I wasn't aware of a setting in the analyser to set the results values.
But in looking I don't find where your pointing at to change it.
Maybe it isn't an option in Sql7?
I attached the original text file.
Email1.txt
Well, I do not have SQL7.0 anymore, but this does load :


create table #t (f1 varchar(8000))

bulk insert #t from 'c:\ee\email1.txt' WITH (ROWTERMINATOR ='\n')
select * from #t


You might need to specify a format file if the above is not understood.
"Also I wasn't aware of a setting in the analyser to set the results values.
But in looking I don't find where your pointing at to change it."

In SQL Query Analyzer (even in 7.0), do the following -

1) Goto to Menu Option Tools
2) Select Options in the context menu  (Shortcut <CTRL> + <SHIFT> + 'O' can be used instead of step 1 and 2)
3) In the dialog displayed, select the Results tab (should be the third tab on the top)
4) Change the value agains 'Max Characters per Column' to 8000 (it is likely to be 254 or 255)

The result you are seeing is truncated for display purposes by SQL Query Analyzer, but the backend table does contain the whole text. To verify this, run the following command -

SELECT LEN(cBody) FROM ##MyTable

You will see that the value returned is 1203, which is the count of all characters in Email1.txt
Avatar of kpi_it

ASKER

Ok found what you were talking about.
But just as an FYI in Sql 7.
It is Meny / Query / Current Connection Options / Advanced / Maximum Character per column
It is set at 256. I will bump it up and see if there is a difference.
Avatar of kpi_it

ASKER

Ok all is well so far but I am only getting one iteration from my loop, what am I overlooking?


-- Step through each record and pull in the data from the email
declare @cFname char(200)
declare @RowNum int
declare @InsNote Varchar(3000)
declare @Tid Char(10)
declare fNameList cursor for select * From #files
 
OPEN fNameList
 
FETCH NEXT FROM fNameList
INTO @cFname
 
set @RowNum = 0 
WHILE @@FETCH_STATUS = 0
BEGIN
  set @RowNum = @RowNum + 1
  --print cast(@RowNum as char(1)) + "' " +  @cFname + "'"
  print @cFname
	
  Delete From ##MyTable
 
  -- Put the magic here
  exec(@cFname)
 
  Select Top 1 @Tid = SubString(cBody,CharIndex(' ',cBody),Len(cBody)-CharIndex(' ',cBody)+1) from ##MyTable
	
  Select @InsNote = cBody From ##MyTable
  Select @InsNote
  insert into table_notes (Note    ,TicketId,DateCreated,WrittenBy,PrintableYN,CustViewYN,BillingYN) 
	            Values(@InsNote,@Tid    ,GetDate()  ,22       ,'Yes'      ,'No'      ,0)
 
  -- End Put the magic here
 
  FETCH NEXT FROM fNameList
    INTO @cFname
END
 
CLOSE fNameList
DEALLOCATE fNamelist

Open in new window

How many records in #files?
Avatar of kpi_it

ASKER

3 and I verified them:

files
---------------------------------------------------------------------------------------------------------------------------------------
Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email1.txt" With (Firstrow=2, ROWTERMINATOR = "\0")
Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email2.txt" With (Firstrow=2, ROWTERMINATOR = "\0")
Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email3.txt" With (Firstrow=2, ROWTERMINATOR = "\0")

(3 row(s) affected)
I've scanned through your cursor code a couple of times and can't see anything wrong with the loop construct..

Does it just print and insert Email1.txt and then exit the cursor??
Avatar of kpi_it

ASKER

The print was just for debugging purposes.
But after looking more closely, nothing is inserting into the sql table_notes table.
The one record I did see I had done earlier from one line of test script.
Not sure what the issue is here.
Can you check if the EXEC statement succeeded in Line 23 by doing a select cbody from ##mytable in line 24, just for debugging purposes?
Avatar of kpi_it

ASKER

It must be failing for some reason. Whe I run the Select I get nothing with 0 row(s) affected in the results tab
Avatar of kpi_it

ASKER

This is what @cFname reads just before the exec() command:
Bulk Insert ##MyTable From "J:\Mitg\Email_Attachments\Email1.txt" With (Firstrow=2, ROWTERMINATOR = "\0")
OK.... Please COPY and PASTE do not retype.

STEP 1

Just go into query analyser and simply run this :

create table #t (f1 varchar(8000))

bulk insert #t from 'J:\Mitg\Email_Attachments\Email1.txt' With (Firstrow=2, ROWTERMINATOR ='\n')
select * from #t


STEP 2 (if STEP1 produced some results)

replace that ##mytable create with: create table tmp_mail_import (column1 varchar(8000))

then replace the setting of @cFname  with : set @cfname = 'bulk insert tmp_mail_import from ''J:\Mitg\Email_Attachments\Email1.txt'' With (Firstrow=2, ROWTERMINATOR =''\n'')'

NOTE: they are two single quotes inside there - they are NOT double quotes.

STEP 3

Replay back to us exactly what you did and the outcomes.
Avatar of kpi_it

ASKER

Ok, did the above.
After the bulk insert I end up with a table with 57 rows.
As stated above I am looking for one row with the entire text file in a field.
If we are headed in that direction.
Please let's continue.
There are other ways of combining rows...

OK, now we have a table

declare @str varchar(8000)
select @str = isnull(@str,'') + ';'+isnull(column1,'') from tmp_mail_import
print @str
You do not need the ';' delimiter - just want visibility for now.
Avatar of kpi_it

ASKER

Beautifull! Now I need to break the string up so it reads like the email does. So when they scroll up and down in the note it reads easily. Would this be done with the replace command? Replace(@str,';',chr(13))??
ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
but use it directly....


declare @str varchar(8000)
select @str = isnull(@str,'') + char(13)+char(10)+ isnull(column1,'') from tmp_mail_import
print @str
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kpi_it

ASKER

I got all to work, I thank you very much.
You went above and beyond.
So I will jack up the points!!
Have a great evening.
Avatar of kpi_it

ASKER

Thanks again.