Solved

SQL: How do I bulk insert this data?

Posted on 2011-03-09
2
237 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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now