Solved

SQL: How do I bulk insert this data?

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 76
SQL Help 27 69
TSQL - How to declare table name 26 53
why sql server only update some statistics in the database ? 3 40
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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