Bulk Insert a text file into Sql Table

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

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

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?

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..

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'
      )

Select allOpen in new window

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?

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...

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.

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

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.

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

Select allOpen in new window

How many records in #files?

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??

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?

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

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.

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.

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))??

but use it directly....


declare @str varchar(8000)
select @str = isnull(@str,'') + char(13)+char(10)+ isnull(column1,'') from tmp_mail_import
print @str

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.

Thanks again.