import TXT file into database table

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

import TXT file into database table

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-02-24 : 11:50:19

Hi All.

I have data in .txt file and would like convert that text file to database table. That sample of that file


 02/13/12 09:13AM                                     Header1		                                  PAGE 1
                                               	      Header2

NUMBER  NAME           SPEC   ACTIVE?  LIC #     TYP     ID       NUM
      
263444  AAD, EDD       COMMP  YES      00121      01     12957    AA6272   
752333  AAD, EDD       MED    YES      00121      01     12957    AA6272   
005666  ABA, JAM       COMMP  YES      00136      01     14775                

.....

270504  ACK, PAL       REF    YES      00138      01     13068
 02/13/12 09:13AM                                     Header1		                                  PAGE 2
                                               	      Header2

NUMBER  NAME           SPEC   ACTIVE?  LIC #     TYP     ID       NUM
      
263444  BBD, EMM       COMMP  YES      00121      01     12777    AA6332   
752333  BBD, EMM       MED    YES      00121      01     12777    AA6332   
005666  SBA, LLM       COMMP  YES      00136      01     22775    BB7686            

.....

270504  HCK, PBL       REF    YES      00138      01     42068    CC7156

Each page like in sample has 66 rows. If is it possible how it to do?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-24 : 16:37:00

If the data is indeed like what you have laid out - i.e., with header rows, page numbers, blank lines and so on, it would be hard to reliably weed out the fluff and load the tabular data into a table.

This data looks like it was generated for human consumption, not machine consumption.

I run into this a lot, but every time I just tell the vendor (or whomever is providing the data) that I can't use it, that they need to provide it in a tabular format. (Well, actually *I* don't tell them, I don't have that forceful quality that makes people comply easily. So I get my colleagues in the business side to tell that - "If you want to continue doing business with us, send it in the format she is asking for")

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 11:36:29

As told before its a bit difficult with current format. however, what you can try is to use ETL took like SSIS to parse the rows and then based on some logic (may be derive rownumber and then look for correct in case format is consistent) and parse out detail rows alone to staging table. Then you can apply any transformation business logic you need before dumping it to your final table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-27 : 05:09:30

if possible you can try using BCP Command like Bulk insert or Openrowset()...

BULK INSERT <dbo.tablename> FROM '<text file path>' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

here field termination based on your text data...........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 19:58:43

quote:
Originally posted by Jayam.cnu

if possible you can try using BCP Command like Bulk insert or Openrowset()...

BULK INSERT <dbo.tablename> FROM '<text file path>' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

here field termination based on your text data...........

but question is how to get headers out of whole data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources