[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

How to import a text file into the SQL SERVER db, and thereinto there are some values of the fields have over several rows.


I have some records need import to the SQL server DB,
the data have two field:


there is a record such below:

....

"1999-00001","COMMENT:
SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL
      SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL .
5) RE-ATTACH OD.  
5) SERVICE ENTRANCE.
6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE.  

 "

....



How to import such this records into my Sql Server DB?
I would like to use the Sql sentences to implement it. could you help me figure out the problem?
Thanks much~

Any comment would appreciated!
0
skydaivy
Asked:
skydaivy
  • 7
  • 4
  • 4
1 Solution
 
RejojohnyCommented:
what r the 2 fields and which field would u like to  import .. and is point 1 to 7 one record or 7 records and do u have more than 1 row
0
 
skydaivyAuthor Commented:
the above is the singlehanded record, the "1999-00001" is one field, and the rest is another filed.  and the second field has over so many rows. but I would like it be loaded into the DB in a field.

more of the data like :


"1999-00001","aaa:"
"1999-00002","ggg:"
"1999-00003","test:"
"1999-00004","comment:
SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL
      SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL .
5) RE-ATTACH OD.  
5) SERVICE ENTRANCE.
6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE.  

 "

"1999-00001","COMMENT test:"
"1999-00001","COMMENT A test:"




0
 
skydaivyAuthor Commented:


Also , for importing the data, I wrote a schema.ini file to defind the property below:

-- schema.ini  

[test.txt]
Format=CSVDelimited
ColNameHeader=false
MaxScanRows=50
Col1=id1 Text Width 30
Col2=THE_TEXT Text Width 4000
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
r_a_j_e_s_hCommented:
I have tried with DTS with the same data samples u have given. Table get updates with 6 rows.

So use DTS (Text to SQL Server) with delimited option.

Hope u have used DTS, if any issues pls revert back.
0
 
skydaivyAuthor Commented:


use DTS (Text to SQL Server) with delimited option?

I had tried  to use to do that , but the result return the  data  was not my hope.


for example:

I wannt to the  "1999-00004"  be loaded the first field,
and the data of
 "comment:
SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL
     SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL .
5) RE-ATTACH OD.  
5) SERVICE ENTRANCE.
6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE.  

 "
would be loaded into the second field.  

Hi, r_a_j_e_s_h , would you mind let me know  how do you Use the DTS  tool to import such these data? thanks..

I will wait the detailed on line..





0
 
r_a_j_e_s_hCommented:
This is the result i have got on ur sample data


Col001     Col002                                                                                                                                                                                                                                                    
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1999-00001 aaa:
1999-00002 ggg:
1999-00003 test:
1999-00004 comment:SUBJECT:  INVESTIGATION1) REMOVE .2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL     SPACE TO STREET SIDE OF VALVE.3) RE-ATTACH .4) INSTALL .5) RE-ATTACH OD.   5) SERVICE ENTRANCE.6) SECURE . 7) INSPECT & SERVICE MAIN SERVICE.  
1999-00001 COMMENT test:
1999-00001 COMMENT A test:

(6 row(s) affected)

*******************************************************************************************

i have copied ur sample data in a text file as

"1999-00001","aaa:"
"1999-00002","ggg:"
"1999-00003","test:"
"1999-00004","comment:SUBJECT:  INVESTIGATION1) REMOVE .2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL     SPACE TO STREET SIDE OF VALVE.3) RE-ATTACH .4) INSTALL .5) RE-ATTACH OD.   5) SERVICE ENTRANCE.6) SECURE . 7) INSPECT & SERVICE MAIN SERVICE.  "
"1999-00001","COMMENT test:"
"1999-00001","COMMENT A test:"


*************************************************************************************

then i have imported tht text file using DTS..............

Hope this is explanatory...............  still have queries pls revert back

0
 
RejojohnyCommented:
I had thought about DTS earlier, but the problem here is the line sperator in the second field .. i.e. after every point ..
SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL
     SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL .
5) RE-ATTACH OD.  
5) SERVICE ENTRANCE.
6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE

"r_a_j_e_s_h" seems to have removed this
"comment:SUBJECT:  INVESTIGATION1) REMOVE .2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL     SPACE TO STREET SIDE OF VALVE.3) RE-ATTACH .4) INSTALL .5) RE-ATTACH OD.   5) SERVICE ENTRANCE.6) SECURE . 7) INSPECT & SERVICE MAIN SERVICE.  

and if that is not a proob, then the import is pretty straight forward ...
0
 
r_a_j_e_s_hCommented:
As Rejo told, i simply copied the data as sky given in a text file

1999-00001","aaa:"
"1999-00002","ggg:"
"1999-00003","test:"
"1999-00004","comment:
SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL
     SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL .
5) RE-ATTACH OD.  
5) SERVICE ENTRANCE.
6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE.  

 "
********************************
STEPS to import...

IN DTS, give the path of text file and select delimited and press next ----> In comma delimiter select comma-----> then give the destiantion and proceed.

i got the datas..

Hope u got

0
 
skydaivyAuthor Commented:

Thanks much you two.

Yeah, Use the DTS is a way.  
and during my test,   I  found what problem it is, In fact,  there are some Double Quote (") in my current real data, so , when I try to import them into the DB, the error occur.

for example, some records like(below is 3 records):

"1999-00003","test:"
"1999-00004","comment:SUBJECT:  INVESTIGATION
1) REMOVE .
2) INSTALL NEW COLD WATER GROUND LOCATED IN UNIT F CRAWL     SPACE TO STREET SIDE OF VALVE.
3) RE-ATTACH .
4) INSTALL , it took 15" running
5) RE-ATTACH OD.    it took 1'30" running
6) SERVICE ENTRANCE.6) SECURE .
7) INSPECT & SERVICE MAIN SERVICE.  "
"1999-00001","COMMENT test:"


See the  above 15"   and the  1'30"  ,  However, the Double Quote is the Text_qualifier.


and so, I hope could have a Sql sentence  import them in my Sql DB,
Any comments would be appreciated. Thanks


0
 
r_a_j_e_s_hCommented:
the procedure is very similar to SQL Server, also i am not very much comfortable with MySQL.

I have tried the same in MYSQL but data is storing in same field. just check the option of delimiter ...  etc... in MySQL
0
 
skydaivyAuthor Commented:


Oh, sorry for the confuse ,   my mean is that  I need a procedure could  run in the SQL SERVER ( using T-SQL scripts or storage procedure  ), to load these data into the SQL server DB,  but not MYSQL ~~~    :)



0
 
RejojohnyCommented:
u could use the "bulk insert" statement .. also note that a DTS package too can be called from a stored procedure ... so if you already have a DTS package .. u just have a create a Stored procedure and make  a call to it ...
0
 
RejojohnyCommented:
using DTSrun
0
 
skydaivyAuthor Commented:

I use the  "bulk insert"  statement  such below:

BULK INSERT mac.dbo.ts FROM 'd:\te2.txt'
WITH (
CODEPAGE = 'ANSI' ,
DATAFILETYPE = 'char',

ROWTERMINATOR = '\n',
FIELDTERMINATOR = ',',KEEPNULLS
)


but I didn't got the result  I expect.

would you mind give some construction ? thanks.









0
 
skydaivyAuthor Commented:

I have the way to get the results, using below:

BULK INSERT inspmemo
FROM 'D:\Data\mymemo.txt'
WITH
        (
CODEPAGE ='ROW',  
FIRSTROW  = 1,
KEEPNULLS,
MAXERRORS = 100,
ROWS_PER_BATCH = 200,
DATAFILETYPE ='char',
fieldterminator = '","',
ROWTERMINATOR  = '"\n"'  
        )



It could run well now, Also, thanks much Rejojohny  and r_a_j_e_s_h, all your comments would be appreciated. :)

Have a nice day.

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now