Import text file into SQL database

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 text file into SQL database

Author

Topic

PHenry
Starting Member

10 Posts

Posted - 2012-03-06 : 11:18:19

Hello,

Ive been struggling the past couple of weeks because I'm trying to import data from a text file into an SQL database which isnt comma delimited. I cant find any examples on the net and wondered if anyone on here could help?

Please see screenprint of the text file here: [url]http://stackoverflow.com/questions/9443061/import-text-file-into-sql-database[/url]

I want to find a way of getting the data out and inserting it into an SQL database. For example, I want to get the programmed and mean times out of the text file and into the database.

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 11:30:26

well, this image link is broken to me...

Is it fixed width data?

You can bcp or bulk insert it into a single column table and use substring to break it up

Or you can use a format file and bcp or bulk insert it in to a corresponding table

In EITHER case, I would make all of the column(s) datatype varchar until you can perform an audit of the staged table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

PHenry
Starting Member

10 Posts

Posted - 2012-03-06 : 13:07:17

Hello Brett,

Sorry to hear you cannot access the image, ive tried it and seems fine.

The data isnt fixed width however the values have lots of spaces in between each other and are spread across several rows.

Is there an alternative way i could show you the screen print of the text file?

Many Thanks,

quote:
Originally posted by X002548

well, this image link is broken to me...

Is it fixed width data?

You can bcp or bulk insert it into a single column table and use substring to break it up

Or you can use a format file and bcp or bulk insert it in to a corresponding table

In EITHER case, I would make all of the column(s) datatype varchar until you can perform an audit of the staged table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 13:29:01

So the data is space delimited?

if that's the case you can bcp/bulk insert the data to a table that matches the number of columns of data you have

DO you have a layout?

If not you can bcp/bulk import into a single column table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 13:29:34

A variation of this idea is located in this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172151

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

PHenry
Starting Member

10 Posts

Posted - 2012-03-06 : 14:07:04

Each text file is like the below:
I'm finding it difficult since its all across different rows. Looks like the post has removed the formatting i did below

JOB COMPLETION ORDER

ORDER Nr 123456 Q.ty Ordered 1000
CUSTOMER Pet Warehouse Q.ty done 1000
Product 157855544 ITEM 5699 SHIFT Nr. 1
----------------------------------------------------------------------
PROGRAMMED MEAN
300 min Job TIME 377 sec
185 Setting TIME 180
1009 In Manufacture 1153
----------------------------------------------------------------------
JOB START TIME 30-06-09_21:32:37 JOB FINISH TIME 01-07-09_05:35:04
TIME IN MANUAL 0:43:??
AVERAGE CYCLE TIME 434 sec
AVERAGE TURN AROUND TIME 187 sec

quote:
Originally posted by X002548

So the data is space delimited?

if that's the case you can bcp/bulk insert the data to a table that matches the number of columns of data you have

DO you have a layout?

If not you can bcp/bulk import into a single column table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 14:18:40

You need to use [ code] ...posted data [ /code] tags without the space

Hit reply and you will see this



JOB COMPLETION ORDER
     
ORDER   Nr 123456                         Q.ty Ordered  1000
CUSTOMER Pet Warehouse                    Q.ty done     1000
Product 157855544     ITEM 5699    SHIFT Nr. 1
----------------------------------------------------------------------
PROGRAMMED                            MEAN
   300 min        Job TIME           377 sec
   185           Setting TIME        180 
  1009          In Manufacture       1153 
----------------------------------------------------------------------
JOB START TIME 30-06-09_21:32:37    JOB FINISH TIME 01-07-09_05:35:04
          TIME IN MANUAL   0:43:?? 
      AVERAGE CYCLE TIME  434 sec
AVERAGE TURN AROUND TIME  187 sec

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 14:19:38

OK..so import that into a single column table like I provided in the link I posted

My question to you though is, what do you want to do with free form text?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

PHenry
Starting Member

10 Posts

Posted - 2012-03-06 : 14:26:00

Im wanting to extract specific pieces such as the programmed and mean times, Time in manual, Average Cycle Time, etc and then insert them into a database. I just want the values, not the text.

Thanks,

quote:
Originally posted by X002548

You need to use [ code] ...posted data [ /code] tags without the space

Hit reply and you will see this

JOB COMPLETION ORDER

ORDER Nr 123456 Q.ty Ordered 1000
CUSTOMER Pet Warehouse Q.ty done 1000
Product 157855544 ITEM 5699 SHIFT Nr. 1
----------------------------------------------------------------------
PROGRAMMED MEAN
300 min Job TIME 377 sec
185 Setting TIME 180
1009 In Manufacture 1153
----------------------------------------------------------------------
JOB START TIME 30-06-09_21:32:37 JOB FINISH TIME 01-07-09_05:35:04
TIME IN MANUAL 0:43:??
AVERAGE CYCLE TIME 434 sec
AVERAGE TURN AROUND TIME 187 sec

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 14:58:46

Well, let's ASSUME you read my other link and you have the text file into a table..you would then need to grab the rows you want to find, and then ENJOY the task of parsing it all out..

Here is how you can find the rows


SELECT * 
FROM (
SELECT 'JOB COMPLETION ORDER'													AS DATA_ROW UNION ALL
SELECT '     '																	AS DATA_ROW UNION ALL
SELECT 'ORDER   Nr 123456                         Q.ty Ordered  1000'			AS DATA_ROW UNION ALL
SELECT 'CUSTOMER Pet Warehouse                    Q.ty done     1000'			AS DATA_ROW UNION ALL
SELECT 'Product 157855544     ITEM 5699    SHIFT Nr. 1'							AS DATA_ROW UNION ALL
SELECT '----------------------------------------------------------------------'	AS DATA_ROW UNION ALL
SELECT 'PROGRAMMED                            MEAN'								AS DATA_ROW UNION ALL
SELECT '   300 min        Job TIME           377 sec'							AS DATA_ROW UNION ALL
SELECT '   185           Setting TIME        180 '								AS DATA_ROW UNION ALL
SELECT '  1009          In Manufacture       1153 '								AS DATA_ROW UNION ALL
SELECT '----------------------------------------------------------------------'	AS DATA_ROW UNION ALL
SELECT 'JOB START TIME 30-06-09_21:32:37    JOB FINISH TIME 01-07-09_05:35:04'	AS DATA_ROW UNION ALL
SELECT '          TIME IN MANUAL   0:43:?? '									AS DATA_ROW UNION ALL
SELECT '      AVERAGE CYCLE TIME  434 sec'										AS DATA_ROW UNION ALL
SELECT 'AVERAGE TURN AROUND TIME  187 sec'										AS DATA_ROW 
) AS XXX
 WHERE CHARINDEX('Job TIME',		DATA_ROW) <> 0
	OR CHARINDEX('Setting TIME ',	DATA_ROW) <> 0
	OR CHARINDEX('In Manufacture',	DATA_ROW) <> 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources