Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL: How do I bulk insert this data?

Posted on 2011-03-09
2
Medium Priority
?
253 Views
Last Modified: 2012-06-21
I'm new to MS-SQL.  I'm familiar with bulk-inserting from a CSV, however I have a set of data in text files that is not delimited the way a CSV would be and I need to get this into a SQL database.

The data is in a text file, contained in 4-line sets (so each row is four lines), for example:

@EA-GAII-02_0000:7:1:6770:966#0/2
CTAGCTAGCTAGGGTGCTGATCGATCAGCTAGCTGATCGAGTCGATCGATCGATCGATCGA
@EA-GAII-02_0000:7:1:6770:966#0/2
hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhghhhhhhhhhhhhhhhhhhhhhhhhhhhhhggggghhhh

I need to import this data into columns as follows:

Column Name - Data
-------------------------
Cell_No - 7
Tile_No - 1
X_Coord - 6770 [the length of this number could be 1-10 digits]
Y_Coord - 966 [the length of this number could be 1-10 digits]
Pair - 2
Sequence - [all of line 2]

The rest of the information is unimportant.

Essentially, I need to extract bits of data from line 1 and place into multuple columns, place all of line 2 in its own column and ignore lines 3 and 4.  I'm not sure how to write the bulk insert sql to do this.  Can anyone provide an example from a similar import that they have done to get me started?
0
Comment
Question by:I_play_with_DNA
2 Comments
 
LVL 9

Expert Comment

by:AriMc
ID: 35088743
I'd go for a simple C-program to tokenize the text. Do you have access and know how to use a C-compiler?

0
 
LVL 7

Accepted Solution

by:
tlovie earned 2000 total points
ID: 35088919
I think the problem is too specific to provide a simple recipe that would work, but here's what I would do if presented with this problem

Make a table that has 2 columns
create table tblData (LineNo int identity(1,1), Data varchar(200) )
truncate table tblData

bulk insert tblData with a format file this link will help you do that http://www.developerfusion.com/code/5357/bulk-insert-from-flat-file-using-transactsql/

Make your format file such that all your data lands in column 2 of the table, and has line numbers attached to it.

Now, you've got your data into a table, with row numbers, hopefully in the correct order that it came in as.

Then I'd write a join like this:
select *
from tblData a inner join tblData b on a.LineNo=b.LineNo-1
where (a.LineNo%4)=1
-- this will give you every fourth line, joined to the corresponding line 2

Then you'd need to write some substring expressions that use the charindex function:
substring(a.Data, charindex(a.Data, ':', 0))
-- these charindex expressions are going to get ugly in a hurry, because the 3rd argument to it will be the starting position of where you want to search from.  Which from your explanation will depend on charindexes of previous statements.  

If you attach a sample data file here, I will attempt to help you.

Good luck.


0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question